Johannes Johannes - 3 months ago 20
C# Question

dynamic linq multiple vs single .where query

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()))

Answer

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");
Comments