Monday, 30 September 2013

Why is the generated query from LinQ different if I build the where-clause dynamically for a COUNT()?

Why is the generated query from LinQ different if I build the where-clause
dynamically for a COUNT()?

I want to count rows for a certain query for which I build the
WHERE-clause dynamically.
What excited me was the difference I saw when building this in two
different ways.
Try 1
int resultsCount = context.MyView.Where(x => x.Id > 100000).Count();
in the profiler I see, that the following query is being executed:
SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM (SELECT
[MyView].[Id] AS [Id],
[MyView].[EventTypeId] AS [EventTypeId],
[MyView].[EventSourceId] AS [EventSourceId],
[MyView].[TraceLevelId] AS [TraceLevelId],
[MyView].[TimeCreated] AS [TimeCreated],
[MyView].[Data1MaxStr] AS [Data1MaxStr]
FROM [dbo].[MyView] AS [MyView]) AS [Extent1]
WHERE [Extent1].[Id] > 100000
) AS [GroupBy1]
Try 2
But I have to check for multiple conditions and not all of them need to be
met for every execution. So I tried building the WHERE-clause for my COUNT
dynamically (referring to this post):
var parameter = Expression.Parameter(typeof(MyView), "x");
Expression<Func<MyView, bool>> check1 = x => x.Id > 100000;
Expression expression = Expression.Constant(true);
expression = Expression.AndAlso(expression, Expression.Invoke(check1,
parameter));
var lambda = Expression.Lambda<Func<MyView, bool>>(expression, parameter);
int resultsCount = context.MyView.Where(lambda.Compile()).Count();
I would expect to see the same or at least a similar query being executed,
but instead I see the following in the profiler:
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[EventTypeId] AS [EventTypeId],
[Extent1].[EventSourceId] AS [EventSourceId],
[Extent1].[TraceLevelId] AS [TraceLevelId],
[Extent1].[TimeCreated] AS [TimeCreated],
[Extent1].[Data1MaxStr] AS [Data1MaxStr]
FROM (SELECT
[MyView].[Id] AS [Id],
[MyView].[EventTypeId] AS [EventTypeId],
[MyView].[EventSourceId] AS [EventSourceId],
[MyView].[TraceLevelId] AS [TraceLevelId],
[MyView].[TimeCreated] AS [TimeCreated],
[MyView].[Data1MaxStr] AS [Data1MaxStr]
FROM [dbo].[MyView] AS [MyView]) AS [Extent1]
The value of resultsCount however is the same, but why are those queries
so different and where does LinQ get the COUNT for the second query,
because I do not see any COUNT being selected?
Can someone tell me how I can force a query similar to the first but still
build my WHERE-clause dynamically?
Any hints would be much appreciated.

No comments:

Post a Comment