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
– Igor, Commented Jan 20, 2017 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
– Abubakar Riaz, Commented Jan 20, 2017 at 18:19
You should remove these null checks from the expression body. Build the expression dynamically: stackoverflow.com/a/14622200/861716 — Gert Arnold
– Gert Arnold, Commented Jan 20, 2017 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
– Gert Arnold, Commented Jan 20, 2017 at 19:21

Igor · Accepted Answer · 2017-01-20 19:34:16Z

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, 2017 at 19:34

answered Jan 20, 2017 at 18:18

Igor

62.5k10 gold badges111 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Abubakar Riaz Over a year ago

let me test this both ?? statement and (s.Department == deal.Department || deal.Department == null)

Igor Over a year ago

@AbubakarRiaz - ok.

Abubakar Riaz Over a year ago

?? 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 Over a year ago

Thanks alot for so quick responses.

MikeS · Accepted Answer · 2017-01-20 18:21:32Z

1

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.

edited Jan 20, 2017 at 18:21

answered Jan 20, 2017 at 18:14

MikeS

1,7641 gold badge9 silver badges13 bronze badges

1 Comment

Antoine Pelletier Over a year ago

Yes, an important aspect of the EF query, if you choose s => then s will always refer to the table

Collectives™ on Stack Overflow

Entity Framework Search functionality with Dynamic SQL WHERE clause

2 Answers 2

Edit

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Edit

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related