CODEWITHSUNDEEP
c#linqlinq-to-sql
Weston Odom
Weston Odom

Reputation: 311

Why does this Linq query return 0 for Count()?

This seems like an inconsistency, but I'm probably just missing something obvious. The base query is:

var events = db.tbl_special_events.Where(x => x.TimeStamp >= startDate);

The apparent inconsistency comes when I run the following code block:

int c1 = 0;
foreach (var e in events)
{
    if (e.TimeStamp.DayOfWeek.ToString() == "Tuesday") c1++;
}

int c2 = events.Where(e => e.TimeStamp.DayOfWeek.ToString() == "Tuesday").Count();

After that runs, c1 is 1832, but c2 is 0. What am I missing?

Upvotes: 2

Views: 2296

Answers (2)

Sinaesthetic
Sinaesthetic

Reputation: 12192

I recreated this test and found that it may be directly related to the DateTime function.

The query that is generated:

exec sp_executesql N'SELECT [t0].[User_ID], [t0].[Email], [t0].[Password], [t0].[BrandID], [t0].[CustomerID], [t0].[Created], [t0].[EndDate], [t0].[AccountStatusID], [t0].[Verified], [t0].[ResetPasswordFailCount], [t0].[ResetPasswordLocked]
FROM [dbo].[User] AS [t0]
WHERE ((CONVERT(NVarChar,CONVERT(Int,(DATEPART(dw, [t0].[Created]) + (@@DATEFIRST) + 6) % 7))) = @p0) AND ([t0].[BrandID] = @p1)',N'@p0 nvarchar(4000),@p1 int',@p0=N'Tuesday',@p1=3

Notice where @p0=N'Tuesday'

Keeping in mind that IQueryable and IEnumerable differ in that where IEnumerable represents an actual .net object, IQueryable converts your expression into an actual SQL statement used to query the database. So any values that you provide in that expression are actually sent to the database.

It is returning 0 results because there is no match. Reason being, the date conversion in SQL returns a 2 instead of 'Tuesday'. You can test this if you replace Tuesday with 2 in your LINQ WHERE clause, it'll actually work. This will work after enumerating it since the results will have been successfully mapped into a usable .net object where the DateTime.DayOfWeek conversion to "Tuesday" will work properly.

Upvotes: 2

AxelEckenberger
AxelEckenberger

Reputation: 16926

Your count is acting on an IQueryable<Event> while e is an enumerated instance. Therefore, some operations might not work as they cannot be translated into SQL [Edit: or will be translated in nonsensical SQL].

To ensure your Where clause works add an AsEnumerable() before it. This converts the IQueryable<Event> into an IEnumerable<Event> and tells the linq provider that it should stop generating SQL at this point.

So, this statement should provide the correct result:

int c2 = events.AsEnumerable()
    .Where(e => e.TimeStamp.DayOfWeek.ToString() == "Tuesday")
    .Count();

The actual code that cannot be converted causes the problem in SQL is e.TimeStamp.DayOfWeek.ToString().

Alternatively you can use the System.Data.Objects.SqlClient.SqlFunctions (doc here) class to hint the linq provider what it should be doing in SQL.

Edit

As @Servy pointed out this is a Linq to SQL question. However, the problem is quite common so I leave the answer and do not delete it.

On looking at the OP again, there could be another variable to the whole game ... lazy loading.

In the foreach loop the TimeStamp is lazy loaded. In the count query the provider tries to construct an SQL query, during this construction it might not be able to work with ToString (which is known to be problematic) and evaluate e.TimeStamp.DayOfWeek.ToString() to something different than "Tuesday".

The AsEnumerable() forces the provider to stop generating SQL, so that e.TimeStamp is lazy loaded again.

The only way kowing exactly what is going on, is to use tracing tool on the DB (e.g. SQL Server Profiler) to actually see the query (or queries) executed on the server.

Edit 2

Building on @Sinaesthetic's answer the reason why 0 is returned is that the Query tries to compare "4" to "Tuesday" which returns false and therefore the correct result is false.

Can be tested by executing 

select ((CONVERT(NVarChar,CONVERT(Int,(DATEPART(dw, getdate()) + (@@DATEFIRST) + 6) % 7))))

against the DB.

However, this also shows that it is up to the provider to decide what SQL it generates. Also it is up to the provider to decide what statements it supports and which statements it doesn't.

It also shows that using AsEnumerable to stop the SQL generation process can have an infulence on the semantic evaluation of a query.

Upvotes: 1

Related Questions