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 a regular version of EF 6.x I was able to do subqueries pretty easy 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()

When I try to execute the same code in my ASP.NET Core 2.0 EntityFramework powered application my application just freezes and never returns the result.

I tried some variations but all of them hangs once sub-query is executed as part of the initial query:

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

Another sub-query variation (also hangs on execution):

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 }

Yet another subquery variation (also hangs on execution):

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 variation with left outer join (also hangs on 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 works but, obviously, subquery execution is deferred and once we try to access to Action property it kicks off another 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 }

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 execute a subquery and get a list of entities, where each entity assigned list of sub-entities, received using a sub query?

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

Popular Answer

I'm not sure from this.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