dynamic linq multiple vs single .where query

Question

Why does the single where query give a different result than multiple where queries ?

query.Where("666 = ID");
query.Where("ActiveFrom < @0 && ActiveTo > @1 && ValidFrom < DateTime.Now && ValidTo > DateTime.Now", toDate, fromDate);

query.ToString(); results in :

SELECT  *
    FROM [Country] AS [Extent1]
    WHERE 666 = [Extent1].[ID]

and the same query with multiple where calls

query = query.Where("ActiveFrom < @0", toDate);
query = query.Where("ActiveTo > @0", fromDate);
query = query.Where("ValidFrom < DateTime.Now");
query = query.Where("ValidTo > DateTime.Now");

results in :

SELECT  *
FROM [Country] AS [Extent1]
WHERE (666 = [Extent1].[ID]) AND 
([Extent1].[ActiveFrom] < convert(datetime2, '2016-10-23 11:40:35.9538054', 121)) AND 
([Extent1].[ActiveTo] > convert(datetime2, '2016-06-23 11:40:35.9518052', 121)) AND
([Extent1].[ValidFrom] < (SysDateTime())) AND 
([Extent1].[ValidTo] > (SysDateTime()))

Not sure if it is a stupid question but by the code you posted... in the first example - did you set query = "first where" and then again for the second where? — Gilad Green
– Gilad Green, Commented Aug 23, 2016 at 9:56
query.Where("666 = ID"); doesn't do anything because you're not assigning this query to a variable whereas your second example does it. — Tim Schmelter
– Tim Schmelter, Commented Aug 23, 2016 at 9:56

Graham · Accepted Answer · 2017-09-29 18:10:37Z

2

In order for the different Wheres to be relevant you need to assign them back to query:

//instead of:
query.Where("666 = ID");
query.Where("ActiveFrom < @0 && ActiveTo > @1 && ValidFrom < DateTime.Now && ValidTo > DateTime.Now", toDate, fromDate);

//do:
query = query.Where("666 = ID");
query = query.Where("ActiveFrom < @0 && ActiveTo > @1 && ValidFrom < DateTime.Now && ValidTo > DateTime.Now", toDate, fromDate);

Also the Where calls can be chained. Most linq extension methods return IEnumerable<TSource> and therefore can be chained.

Applying the chaining on your second query it will look like this:

query = query.Where("ActiveFrom < @0", toDate)
             .Where("ActiveTo > @0", fromDate)
             .Where("ValidFrom < DateTime.Now")
             .Where("ValidTo > DateTime.Now");

edited Sep 29, 2017 at 18:10

Graham

7,86020 gold badges67 silver badges92 bronze badges

answered Aug 23, 2016 at 10:02

Gilad Green

37.3k7 gold badges67 silver badges99 bronze badges

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

Comments

ViRuSTriNiTy · Accepted Answer · 2016-08-23 09:54:40Z

1

Because the calls dont modify the query, they return a new instance representing the modified query instead. Thus the second example results in the big query whereas the first results in a simple where condition only.

answered Aug 23, 2016 at 9:54

ViRuSTriNiTy

5,2052 gold badges35 silver badges63 bronze badges

Collectives™ on Stack Overflow

dynamic linq multiple vs single .where query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related