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, Mar 04 '16 at 18:32
@David: I mean awaiting it at a later point in the code it at a later point in the code. — VSO, Mar 04 '16 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, Jul 18 '17 at 20:40

score 4 · Answer 1 · edited May 23 '17 at 12:23

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 '17 at 12:23

Community

1
1

answered Mar 04 '16 at 18:32

Eric J.

147,927
63
340
553

Absolutely, it's not the issue with `CountAsync()` rather with `ToUniversalTime()` without `AsEnumerable()` – Rahul Mar 04 '16 at 18:36
Eric, this still throws an exception. I upvoted your answer, but there is still something else. – VSO Mar 04 '16 at 19:39
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. – Eric J. Mar 04 '16 at 19:40
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. – VSO Mar 04 '16 at 19:45
Updated my answer based on your mention that the underlying DB column is `datetimeoffset`. – Eric J. Mar 04 '16 at 20:57
@VSO you really need to include more details when you post a exception. Click the "[Copy Exception Details to Clipboard](http://blogs.msdn.com/b/saraford/archive/2008/08/07/did-you-know-you-can-copy-the-exception-details-with-one-click-from-the-exception-assistant-276.aspx)" link and post it's entire contents as a update to your question. EntityFramework will almost always have Inner execptions which you are leaving out currently. Also getting to see the message info of the exception also usually has important details. – Scott Chamberlain Mar 04 '16 at 21:34
@ScottChamberlain: I am not getting that exception window here. I know how to get exception details from there when I do. Please see here: http://stackoverflow.com/questions/35805230/comparison-of-datetimeoffset-in-utc-via-ef-linq/35806071#35806071 for the problem I realized I am having after carefully considering it. – VSO Mar 04 '16 at 21:40
If you are not getting the exception window then get the output of `ex.ToString()` from the try-catch block that is catching it. Or enable "break on thrown exceptions", the instructions on how to do that differ if you are using VS2013 or VS2015. – Scott Chamberlain Mar 04 '16 at 21:50

Entity Framework Count Async Throws Exception

1 Answers1