0

I am trying to put a Where Clause in reading a SQL server database table where some conditions are depending on other variables, I have 7 fields to do the filtering in the database and are entered by the user , anyone can have a value or can be null . it should look like this if all search variables Var1, etc are not null 

Select * from Table1 where Field1 = var1 and Field2 = Var2 and Field3 = Var3

However if Var2 for example is null it should be ignored in the Select statement and it will look like

Select * from Table1 where Field1 = var1 and Field3 = Var3

It's much like SQL string, however when using the select statement with Lambda expression as in Entity Framework, I could not find any thing like simple sql string .

I am using VS2017 with c# coding language for an ASP.NET Core application. Database server is SQL2016 .

This looks pretty much standard, however, I could not find a solution. Does anyone have a good solution?

ekad
  • 14,436
  • 26
  • 44
  • 46
Dean
  • 1
  • 1
  • _it should be ignored in the Select statement_ - specify what it means. Should be `select field1, field3` instead of `select *`? – Alexander Petrov Jul 07 '19 at 13:36
  • Answered here: https://stackoverflow.com/questions/632434/linq-to-sql-where-clause-optional-criteria but for Linq-to-Sql. Same solutions work for EF: build a where clause or just test for null or the field match in each case. – Ian Mercer Jul 07 '19 at 13:55

1 Answers1

2

If I understand correctly what you want, the standard way is this:

using (var context = new SomeContext())
{
    IQueryable<SomeEntity> query = context.SomeEntities;

    if (var1 != null)
        query = query.Where(x => x.Field1 == var1);

    if (var2 != null)
        query = query.Where(x => x.Field2 == var2);

    // and so on

    // use the query somehow
}
Alexander Petrov
  • 13,457
  • 2
  • 20
  • 49
  • Yes , but the queries will be cumulative at the end as query1 + query2 + etc to form single query to be presented to the database – Dean Jul 07 '19 at 15:19
  • @Dean look closely, it is cumulative: by setting `query` to `query.Where...` it builds a query with all the terms on it. – Ian Mercer Jul 07 '19 at 15:27
  • Sorry , I am not familiar with IQueryable , the aim is to get at the end query1 & query2 & query3 etc . to me it looks like we will end up with query1 OR query2 OR query3 ! can you please put the last expected statement . It must look like --------------- --------------------------------------------------------------- var Z = await context.Case_TBL.FromSql(queryAll).ToListAsync(); – Dean Jul 07 '19 at 16:45
  • @Dean Chaining `.Where()` calls will result in `AND` combinations, not `OR`. So this code can result in a query like `query.Where(x => x.Field1 == var1).Where(x => x.Field2 == var2)`, which results in `WHERE Field1 = var1 AND Field2 = Var2`. – Progman Jul 14 '19 at 06:59