Entity Framework Search functionality with Dynamic SQL WHERE clause

Question

hi guys i was using Dynamic SQL for search queries where i used to attach WHERE & AND clause piece by piece and form a statement, i recently came to below alternate for this, and life was amazing

cool alternates of Dynamic WHERE-Clause

Select * From tblEmployees 
where EmployeeName = Coalesce(@EmployeeName, EmployeeName) AND
Department = Coalesce(@Department, Department ) AND
Designation = Coalesce(@Designation, Designation) AND
JoiningDate >= Coalesce(@StartDate, JoiningDate) AND 
JoiningDate <= Coalesce(@EndDate, JoiningDate) AND
Salary >= Coalesce(@Salary, Salary)

now the issue is since i implemented entity framework i need to achieve same with Linq queries. i have nullable Byte type and nullable boolean which i am currently unable to handle

just like Coalesce my stupid attempt was && (s.Floors == deal.Floors.HasValue ? null : s.Floors)

below code not matching any results

[HttpPost]
        public ActionResult Results(Deal deal, bool exactMatch)
    {
        List<Deal> deals;

        if (exactMatch)
        {
            deals = dataBase.Deals.Where(s =>
               (s.OwnerName.Contains(deal.OwnerName) || s.OwnerName == null)
            && (s.Rooms == deal.Rooms || s.Rooms == null)
            && (s.BathRooms == deal.BathRooms || s.BathRooms == null)
            && (s.Floors == deal.Floors || s.Floors == null)
            && (s.Builtin == deal.Builtin || s.Builtin == null)
            && (s.Kitchens == deal.Kitchens || s.Kitchens == null)
            && (s.DoubleUnit == deal.DoubleUnit || s.DoubleUnit == null)
            && (s.Corner == deal.Corner || s.Corner == null)
            && (s.Remarks.Contains(deal.Remarks) || s.Remarks == null)
            ).ToList();
        }
        else
        {
            deals = dataBase.Deals.Where(s =>
               (s.OwnerName.Contains(deal.OwnerName) || s.OwnerName == null)
            || (s.Rooms == deal.Rooms || s.Rooms == null)
            || (s.BathRooms == deal.BathRooms || s.BathRooms == null)
            || (s.Floors == deal.Floors || s.Floors == null)
            || (s.Builtin == deal.Builtin || s.Builtin == null)
            || (s.Kitchens == deal.Kitchens || s.Kitchens == null)
            || (s.DoubleUnit == deal.DoubleUnit || s.DoubleUnit == null)
            || (s.Corner == deal.Corner || s.Corner == null)
            || (s.Remarks.Contains(deal.Remarks) || s.Remarks == null)
            ).ToList();
        }

        return View(deals);
    }

table has values like

id Bathroom Floors
1   1        2
2   1        4
3   2        6
4   3        1

i need results which has id 1 & 2 for instance in front end user want to only fill bathroom field with "1" and leave floor field empty

Not really the same is it. In your query your `coalesce` is on the parameter then taking the record value as the default if it is null. In your c# lambda you are checking if the parameter is the same as the table value and then checking if the table value is `null` but that omits the possibility of having a null value in the parameter. — Igor, Jan 20 '17 at 18:13
@Igor my conversion of coalesce into c# code is a lame attempt, i just need coalesce like functionality in C# — Abubakar Riaz, Jan 20 '17 at 18:19
You should remove these null checks from the expression body. Build the expression dynamically: http://stackoverflow.com/a/14622200/861716 — Gert Arnold, Jan 20 '17 at 18:47
Esp. for the `||` alternative you have to do it as I mentioned above. Note that it always returns all rows if even only one of the `= null` conditions is true. — Gert Arnold, Jan 20 '17 at 19:21
@GertArnold i think predicate builder is more proper approach but for now @Igor showed me how to use `??` which translates to SQL CASE statement. will try to learn predicate builder also as shown in your shared link. — Abubakar Riaz, Jan 20 '17 at 19:46

MikeS · Answer 1 · 2017-01-20T18:21:32.530

You are testing whether the field in the table equals the 'deal' property or the field is null rather than doing this:

s.Remarks.Contains(deal.Remarks) || deal.Remarks == null

If you do this, it should be the equivalent query.

You can do this cumulatively too. For example with the exact match case you can do:

deals = dataBase.Deals;
if (deal.OwnerName != null)
   deals = deals.Where(s => s.OwnerName.Contains(deal.OwnerName));

if (deal.Rooms != null)
  deals = deals.Where(s => s.Rooms == deal.Rooms)

That can make the resulting query more efficient. There's a similar way to do this with the non exact match through using unions. I don't know the syntax off hand.

Yes, an important aspect of the EF query, if you choose `s => ` then s will always refer to the table — Antoine Pelletier, Jan 20 '17 at 18:16

Igor · Accepted Answer · 2017-01-20T19:34:16.863

1

Not really the same. In your query your coalesce is on the parameter then taking the record value as the default if it is null. In your c# lambda you are checking if the parameter is the same as the table value and then checking if the table value is null but that omits the possibility of having a null value in the parameter.

Example

Sql

Department = Coalesce(@Department, Department )

would be

(s.Department == deal.Department || deal.Department == null)

not this which is what you have now

(s.Department == deal.Department || s.Department == null)

Edit

If you wanted to duplicate the COALESCE expression you have now you could write it this way although I am not sure if it would decrease efficiency / performance.

(s.Department == (deal.Department ?? s.Department))

edited Jan 20 '17 at 19:34

answered Jan 20 '17 at 18:18

Igor

60,821
10
100
175

let me test this both ?? statement and (s.Department == deal.Department || deal.Department == null) – Abubakar Riaz Jan 20 '17 at 18:44
@AbubakarRiaz - ok. – Igor Jan 20 '17 at 19:20
`??` surely works i have checked `(s.Department == (deal.Department ?? s.Department))` is converted in SQL case statements and suites my need in this search function. `([Extent1].[Department] = CASE WHEN (@p__linq__28 IS NULL) THEN [Extent1].[Department] ELSE @p__linq__28 END )` – Abubakar Riaz Jan 20 '17 at 19:32
Thanks alot for so quick responses. – Abubakar Riaz Jan 20 '17 at 19:39

Entity Framework Search functionality with Dynamic SQL WHERE clause

2 Answers2

Edit