Efcore: Projection with nested projection hangs/does not complete in 2.0 Preview 2

[bridgesquared]

When we updated our code base to 2.0 Preview-2 we had significant problems with a number of our queries hanging. We initially flagged this as AutoMapper/AutoMapper/#2184. But during discussions it has become apparent that this is a change at the EF Core 2 level.

The following style of query with an inner projection now fails to complete / hangs:

var result = await context.Documents.Select(d => new DocumentDTO
    {
        Reference = d.Reference,
        Items = d.Items.Select(i => new DocumentItemDTO
        {
            Reference = i.Reference
        }).ToList()
    }).FirstAsync();

It used to work in 2.0 Preview 1 (and 1.x.x) but now hangs in 2.0 Preview 2.

I appreciate that it is a problematic query, in the sense that the inner projection uses ToList() whilst the outer projection is FirstAsync() and I have some ideas for workarounds

But overall I feel that this should continue to work, it feels natural, it is not unreasonable to request nested data and a projection in one step (we are trying to reduce the data retrieved from the db server) and I can't see how you could rewrite the inner projection as async.

It seems not unreasonable to ask EF Core to resolve this type of query correctly and return the data requested asynchronously regardless of how inefficient / ugly it is.

I have created the following gists to repro this.

• Test EF Core 2.0 Preview 1 Projection with nested projection
• Test EF Core 2.0 Preview 2 Projection with nested projection

Discussions on the AutoMapper repo have suggested that this is related to #8208 and possibly #9029.

Meanwhile, we've gone back to 2.0 Preview 1 and are trying to work out how we are going to address this in 2.0 Final. So any thoughts/fixes etc. would be much appreciated. Many thanks.

[BenjiZombie]

+1

I am also facing the same issue in -preview2 with many queries involving nested projections. FYI using npgsql.

[ajcvickers]

@bridgesquared Thanks for reporting this--we will investigate, but we will not be able to get a fix in for 2.0. Some workarounds to try:

• Use First instead of FirstAsync. (Since the inner query is not async there will be some blocking anyway.)
• Remove the ToList call and then enumerate Items explicitly. (This may change the way the DTO gets consumed, so look into your app code as to whether this is acceptable.)
• Split it explicitly into two queries and then combine the results into the DTO afterwards.

[tomerpeled]

I think that this should be on high priority.
The async operation is crucial sometimes in performance manner...

[stgeorge]

In my personal opinion, this is quite a major issue and 2.0 shouldn't be released until async queries with nested projections can be safely written without deadlocking. It worked in preview1, it should not result in a deadlock in an RTM release...

[divega]

We should consider the specific scenario form https://github.com/aspnet/EntityFramework/issues/8208 when we work on this.

[shlomiw]

Hi,

This is a major concern for us, all our framework is based on async/await, and queries are blocked.
We actually consider moving to EF 6, unfortunately it is a show-stopper.

Do you have a time frame?

Thanks in advance

[luisms89]

@shlomiw do not move to EF 6. I have the same issue using EF 6.1.3

[AndriySvyryd]

@anpete Do we need a new issue for patch approval?

[anpete]

Risk: Low: Adds a small amount of new code to the query compiler that detects the offending query patterns and performs a transformation from sync to async operators.
Justification: Fixes a deadlock that can occur in reasonably common customer scenarios. After the fix, such queries become fully async, which is the ideal behavior.

[dbbaleva]

Hi, tried the suggested patch via:

services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"));
    // temporary fix for ef #9128
    options.ReplaceService<ITaskBlockingExpressionVisitor, TaskBlockingExpressionVisitor2>();
    options.ReplaceService<IProjectionExpressionVisitorFactory, ProjectionExpressionVisitorFactory2>();
});

Called this query:

var report = await Context.Reports
    .Include(x => x.ReportFiles)
    .FirstOrDefaultAsync(q => q.Id == id, CancellationToken);

Throws the following error:

Unable to cast object of type 
'System.Func`1[System.Linq.IOrderedQueryable`1[TestDb.Models.ReportFile]]' to type 
'System.Func`1[System.Collections.Generic.IAsyncEnumerable`1[System.Object]]'

[clement911]

I'm facing a similar issue.
Did ef core 2.0 get a RTM greenlight despite this regression? Hard to believe.

await _ctx.AllSchedules
               .Where(s => s.Id == cmd.ScheduleId)
               .Select(s => new
                                            {
                                                Schedule = s,
                                                Shop = s.Shop,
                                                LastRun = s.Runs.OrderByDescending(r => r.StartTimestamp).FirstOrDefault(r => r.IsSuccess)
                                          })
               .SingleAsync();

[stgeorge]

@clement911 I posted on 7/16 that they shouldn't go RTM with this obvious and very common issue that causes hard deadlocks, the product is useless with this massive gotcha in there, especially if you started building something that worked fine in earlier previews. It's not an incorrect pattern. Doesn't seem like whoever is making decisions cares much about quality, only deadlines. Makes me question about using or recommending .NET Core at all if this is the attitude they have.

[spdjudd]

Out of interest, I was having this issue and saw some further strangeness: when I add an 'Include' for the property of the inner projection, the deadlock goes away. I get a warning logged saying the Include is not necessary because of the projection, and will be ignored, but it certainly changes the behaviour.

It's not a good workaround for this issue though, because it looks like EF core then does separate subqueries for each item.

var blogs = await _context.Blogs
    .Include(blog => blog.Posts)  // adding this prevents deadlock, but causes multiple subqueries
    .Select(blog => new
    {
        Id = blog.BlogId,
        Url = blog.Url,
        Posts = blog.Posts.Select(p => p.Title).ToArray()
    })
    .ToListAsync();

[Wijithapaw]

I'm getting the same issue with the EF Core 2.0 (final). Wondering whether any positive workaround is out there yet? Our solution was working well with EF Core 1.1.2.
Its really annoying when the final versions have fundamental bugs like this. Really don't get the point of releasing a product having obvious known issues that almost make it useless for most of the users. This results only waste of time!

[stgeorge]

@Wijithapaw I believe the only workaround right now is to not use async when having nested projections.

[lburkovsky]

we face the same problem. MS give us please a delivery date.

[Wijithapaw]

@stgeorge, For that I have to change a good chuck of back end methods. And would be even annoying when reverting them back once fixing the issue. So I decided not to do the .Net 2.0 upgrade until this issue is fixed.

Even after 2 months of reporting this show-stopper there is no a proper fix. That means there is no a simple fix!. I won't be surprised if we have to do some significant changes in our code (AGAIN) to comply with architectural changes in next .Net Core release.

[ajcvickers]

This is tentatively scheduled to release near the beginning of October.

[Eilon]

This patch bug is approved for the 2.0.x patch. Please send a PR to the feature/2.0.1 branch and get it reviewed and merged. When we have the rel/2.0.1 branches ready please port the commit to that branch.

[john-mills-nz]

I can confirm that the workaround @spdjudd mentioned that uses .Include() does generate a warning that says it is ignored but does in fact, fix the hang issue.

[danobri]

How can we install the 2.0.3 release to take this fix? It appears to be complete: https://github.com/aspnet/EntityFrameworkCore/milestone/35

However, it's not in the list of official releases, and I don't see it in the myget builds either...? When will the first official 2.0.x release drop? An earlier post suggested it would be early October.

[Eilon]

@danobri we're working on setting up a MyGet feed with a preview of the patch for people to try out. I'll update this issue when we have that ready.

[danobri]

@Eilon Thanks! Any idea when the official release will happen?

[Eilon]

@danobri should be November, but with the patch feed people should be able to at least verify the fixes and use the bits as a temporary solution.

[Eilon]

Hi, we have a public test feed that you can use to try out the ASP.NET/EF Core 2.0.3 patch!

To try out the pre-release patch, please refer to the following guide:

• https://github.com/aspnet/Announcements/issues/274 (info)
• https://github.com/aspnet/Home/issues/2254 (discussions and questions)

We are looking for feedback on this patch. We'd like to know if you have any issues with this patch by updating your apps and libraries to the latest packages and seeing if it fixes the issues you've had, or if it introduces any new issues. If you have any issues or questions, please reply on this issue to let us know as soon as possible.

Thanks,
Eilon

[mderriey]

@Eilon I was experiencing that specific bug with EF Core 2.0.

After updating to the preview bits, it works as expected and I haven't noticed any regression.

Thanks for pushing that preview, looking forward to the stable patch release.

[Eilon]

@mderriey thanks for trying it out and reporting back!

[gusleo]

@Eilon I'm still face deadlock ToListAsync issue on this 2.03-* version. Example cuery

[Eilon]

@kodelapan did this issue also affect the 2.0.0 RTM release? Or is it a new issue with the 2.0.3 patch?

[Eilon]

@ajcvickers / @anpete - can someone take a look at this issue?

[divega]

@kodelapan would it be possible for you to provide a project that reproduces the error?

[gusleo]

@Eilon : yes, i'm already using 2.0.3
@divega : thanks for waiting, i have create the repo https://github.com/kodelapan/coffeemaker

[ajcvickers]

@smitpatel @maumar Could one of you investigate this--the repro code is linked above. (We'll likely need a new issue to track it.)

[smitpatel]

I have isolated the issue and filed #10188

@kodelapan - Use following query as work-around
```C#
var result = (from a in DnaCoreContext.Article
join c in DnaCoreContext.ArticleCategory on a.CategoryId equals c.Id
where status.Contains(a.Status) && c.IsVisible == true
orderby a.CreatedDate descending
select new Article
{
Id = a.Id,
ShortDescription = a.ShortDescription,
Slug = a.Slug,
Title = a.Title,
CategoryId = a.CategoryId,
CreatedById = a.CreatedById,
Category = new ArticleCategory
{
Id = c.Id,
Name = c.Name
},
ImageMaps = (from map in a.ImageMaps
join i in DnaCoreContext.Image on map.ImageId equals i.Id
where i.IsPrimary == true
select new ArticleImage
{
Id = map.Id,
Image = new Image
{
Id = i.Id,
FileName = i.FileName,
FileExtension = i.FileExtension,
ImageUrl = i.ImageUrl
}
}).ToList()
});

        if(staffId > 0 )
        {
            result = result.Where(x => x.CreatedById == staffId);
        }
        return new PaginationEntity<Article>
        {
            Page = pageIndex,
            PageSize = pageSize,
            TotalCount = await result.CountAsync(),
            Items = await result.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync()
        };

`` (it worked for me). The difference is, instead of usingDnaCoreContext.ImageMapsfollowing by wheremap.ArticleId == a.Idit uses thea.ImageMaps` navigation property which will give same result. You can do similar for any other queries you have.

[gusleo]

@smitpatel : thanks for your information. It's work now