Entity Framework .NET Core 2.0 Query hangs on execution when subquery is executed

c# entity-framework entity-framework-6 entity-framework-core sql

Question

With an ordinary version of EF 6.x, I found it rather simple to use subqueries with the following query:

var q = from u in db.User
        let actions = from ua in db.UserActions
                      where ua.UserId == u.Id && ua.Approved
                      select ua
        where u.Active
        select new { User = u, Actions = actions.ToList() }

var result = await q.ToListAsync()

My ASP.NET Core 2.0 EntityFramework-powered application merely stalls and never gives the result when I attempt to run the same code in it.

I tried a few other approaches, but when the first query's sub-query is performed, they all hang.

  1. sub-query (same as above with some variations)
  2. outer left join

A other sub-query that stalls during execution is:

var q = from u in db.User
        let actions = (from ua in db.UserActions
                      where ua.UserId == u.Id && ua.Approved
                      select ua).ToList()
        where u.Active
        select new { User = u, Actions = actions }

Another subquery variant that stalls during execution is:

var q = from u in db.User
        let actions = db.UserActions.Where(ua => ua.UserId == u.Id && ua.Approved)
        where u.Active
        select new { User = u, Actions = actions.ToList() }

Another left outer join variant that stalls during execution

var forUserId = "test";
var q = from u in db.User
        join ua in db.UserActions on u.Id equals ua.UserId into actions
        from ua in action.DefaultIfEmpty()
        where u.Active
        select new { User = u, Actions = ua}

The following code runs, however subquery execution is noticeably delayed, and when we attempt to get toAction It causes it to spark another inquiry:

var q = from u in db.User
        let actions = from ua in db.UserActions
                      where ua.UserId == u.Id && ua.Approved
                      select ua
        where u.Active
        select new { User = u, Actions = actions }

var result = await q.ToListAsync()
foreach (var r in result)
{
     foreach(var a in r.Actions)
     {
          // extra database requests is executed for each result row
          // .. process action.
     }
}

How can I run a subquery and get a list of entities with a list of sub-entities allocated to each entity?

1
6
8/21/2017 8:51:04 PM

Popular Answer

This leaves me uncertain. But,you can try '.ToList()'.

 var q = (from u in db.User
            join ua in db.UserActions on u.Id equals ua.UserId into actions
            from ua in action.DefaultIfEmpty()
            where u.Active
            select new {u,ua}).ToList();
0
8/22/2017 7:02:20 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow