Linq chaining conditions

Question

Need to chain where condition based on the inputs from an input object. using LINQ in EF core.

eg: select * from employee where name = 'test' and place = 'us' these two conditions always be there. Additionally, I have 3 more optional parameters that may or may not have eg: city, county & zip.

1st condition if city exists check append-only city condition to existing where condition. Eg: select * from employee where name = 'test' and place = 'us' and city = 'ny' and query needs to happen on the database level only.

if the city does not exist check for the county if exist Eg: select * from employee where name = 'test' and place = 'us' and county = 'testcounty' and query needs to happen on the database level only.

if the county does not exist check for zip if exist eg: select * from employee where name = 'test' and place = 'us' and zip = '123' and query needs to happen on the database level only.

if all there does not exist just return eg: select * from employee where name = 'test' and place = 'us' and query needs to happen on the database level only.

I tried a few in LINQ by applying conditions name and place, later on, apply optional required values using where but again putting all these values in memory might a performance impact checking is there any better way to handle this request.

Thanks

Svyatoslav Danyliv Svyatoslav Danyliv · Accepted Answer · 2020-10-13T10:19:55

You have to use Concat for such query

var baseQuery = from e in employee
  where e.name == "test" && e.place == "us" 
  select e;

var byCity = from e in baseQuery
  where e.city == "ny"
  select e;

var byCountry = from e in baseQuery
  where e.country == "testcounty"
  select e;

var result = byCity
  .Concat(byCountry)
  .Concat(baseQuery)
  .FirstOrDefault();

Execution plan of this query should be optimized by SQL Server or other modern databases.

Linq chaining conditions

1 Answers