Entity Framework Count Async Throws Exception

Question

We had a synchrounous call to get a count of disposals that meet certain criteria. The working query looks like this.

            itemsCount = _db.Disposals
                .Include(d => d.ItemIds)
                .AsEnumerable()
                .Where(d => d.OrganizationId == SelectedOrganizationID &&
                            d.CreateDateTime.UtcDateTime > greaterThen.ToUniversalTime() &&
                            d.CreateDateTime.UtcDateTime < lessThen.ToUniversalTime())
                .SelectMany(d => d.ItemIds)
                .Count();

I needed to make the query async. It seems that I need an IQueryable to run CountAsync on it. I don't understand why the code above had .AsEnumerable() in the first place, and why the code below throws an exception:

A first chance exception of type 'System.NotSupportedException' occurred in mscorlib.dll

            itemsCount = await _db.Disposals
                .Include(d => d.ItemIds)
                .Where(d => d.OrganizationId == SelectedOrganizationID &&
                            d.CreateDateTime.UtcDateTime > greaterThen.ToUniversalTime() &&
                            d.CreateDateTime.UtcDateTime < lessThen.ToUniversalTime())
                .SelectMany(d => d.ItemIds)
                .CountAsync();

I am really just looking for why the async count doesn't work (the project does build, but then the exception is thrown). However, it would be a bonus to understand why AsEnumerable() is used.

P.S. I am aware that it's not running asynchrounously when I put await in front of it, that's just there for testing. _db is the EF database context.

edit: It still throws an exception when written as:

            var greaterThanUtc = greaterThen.ToUniversalTime();
            var lessThanUtc = greaterThen.ToUniversalTime();

            itemsCount = await _db.Disposals
                .Include(d => d.ItemIds)
                .Where(d => d.OrganizationId == SelectedOrganizationID &&
                            d.CreateDateTime.UtcDateTime > greaterThanUtc &&
                            d.CreateDateTime.UtcDateTime < lessThanUtc)
                .SelectMany(d => d.ItemIds)
                .CountAsync();

A first chance exception of type 'System.NotSupportedException' occurred in mscorlib.dll

EDIT 2:

I think the problem is trying convert a Date field in SQL (datetimeoffset) to d.CreateDateTime.UtcDateTime, I am guessing EF doesn't support this at all.

"I am aware that it's not running asynchrounously when I put await in front of it, that's just there for testing." - I'm morbidly curious what you even mean by that. If the itemsCount should be the result of the operation, removing the await is going to break that. — David
– David, Commented Mar 4, 2016 at 18:32
@David: I mean awaiting it at a later point in the code it at a later point in the code. — VSO
– VSO, Commented Mar 4, 2016 at 19:15
@tcrite I don't remember - judging by the timing of my update, I am guessing the issue was with the date field. So for your use case, look at the answer below. Hopefully it helps. I am guessing I worked around it somehow by refactoring, since I usually accept answers that solve my prob. — VSO
– VSO, Commented Jul 18, 2017 at 20:40

Community · Accepted Answer · 2017-05-23 12:23:44Z

4

The function call ToUniversalTime() cannot be translated to a store expression, so it cannot run on the database side.

.AsEnumerable() causes everything that comes after it to run on the client, so you can use that .NET method.

Note it's generally a bad idea to run more client-side than necessary. The calls to ToUniversalTime() happen on what appear to be local variables. You could so something like

var greaterThanUtc = greaterThen.ToUniversalTime()

and then use greaterThanUtc in your query, with out .ToUniversalTime();

Same for lessThen.

UPDATE

If your database field is datetimeoffset you have to use DateTimeOffset in C#. See Entity Framework Mapping DateTimeOffset to SQL Server DateTime

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered Mar 4, 2016 at 18:32

Eric J.

151k65 gold badges353 silver badges563 bronze badges

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

8 Comments

Rahul Over a year ago

Absolutely, it's not the issue with CountAsync() rather with ToUniversalTime() without AsEnumerable()

VSO Over a year ago

Eric, this still throws an exception. I upvoted your answer, but there is still something else.

Eric J. Over a year ago

What is the exception along with stack trace, and inner exception(s) if there is one? That's a lot of info for a comment, best to append to your question.

VSO Over a year ago

It's the same exception, I added it to the answer. Since it's first chance, it's not giving me inner exception details or stopping like it normally would. Not sure what the proper verbiage is, but if I divide by zero or something, it pauses the code. Here it just throws the exception and doesn't execute further.

Eric J. Over a year ago

Updated my answer based on your mention that the underlying DB column is datetimeoffset.

|

Collectives™ on Stack Overflow

Entity Framework Count Async Throws Exception

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related