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:
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?
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();