Linq Count() for IQueryable vs Linq Count() for IEnumerable

Question

I am confused to understand the difference between Linq's Count() for IQueryable and Linq's Count() for IEnumerable.

I have the following method which has to return the same result of the counts, but enumerableCount equals 2 while queryableCount is 0:

public List<int> GetMembersCount() 
{
    var queryableQuery = MemberRepository.GetAll().Include(p => p.Message);
    var enumerableQuery = MemberRepository.GetAll().Include(p => p.Message).ToList();

    var queryableCount = queryableQuery.Count(m => m.Message.Deadline.Date == DateTime.Today);
    var enumerableCount = enumerableQuery.Count(m => m.Message.Deadline.Date == DateTime.Today);

    return new List<int>
        {
            enumerableCount,
            queryableCount
        };
}

Can anybody explain what is going on? I am using PostgreSQL and Entity Framework

The `IQueryable` version will build an SQL query to attempt to perform the count using the date comparison, where the 'IEnumerable` flavour builds an SQL query to load all data, and the Count does a comparison via C#. As Tim has mentioned this is most likely coming up different if the SQL-based date/time comparison isn't resulting in the datetime comparisons you are expecting. You will need to use EntityFunctions (EF Core) or DbFunctions (EF6) to perform date-only comparisons. — Steve Py, Aug 18 '22 at 07:13
It's important to know the exact EF version. Also, check the generated SQL query. — Gert Arnold, Aug 18 '22 at 07:21

score 3 · Answer 1 · answered Aug 18 '22 at 07:12

3

Try

var querableCount = querableQuery
    .Count(m => DbFunctions.TruncateTime(m.Message.Deadline) == DbFunctions.TruncateTime(DateTime.Now));

because DateTime.Date doesn't seem to be supported in EF if used in lambda expressions: https://stackoverflow.com/a/21825268/284240

answered Aug 18 '22 at 07:12

Tim Schmelter

450,073
74
686
939

IMO, *doesn't seem* is too weak for an answer. In EF-core `DateTime.Date` is supported. If it wasn't the query wouldn't even translate. It's just a guess that `DbFunctions.TruncateTime` might help. – Gert Arnold Aug 18 '22 at 07:22
@GertArnold: even a guess often helps. Its easy to delete an answer but its hard for OP to find help if no one answers. A comment has limitations. Sometimes part of an answer gives you the hint to the real fix. – Tim Schmelter Aug 18 '22 at 10:18

score 2 · Answer 2 · answered Aug 23 '22 at 22:06

I don't use dates in filtering due to problems I've had in the past.

var dt = DateTime.Today;
var dt_ole = Convert.ToInt32(dt.ToOADate());
var queryableCount = queryableQuery.Count(m => SqlFunctions.DateDiff("DD", "1899-12-30", m.Message.Deadline) == dt_ole);

Linq Count() for IQueryable vs Linq Count() for IEnumerable

2 Answers2