Linq到实体 - 如何定义左连接进行分组？ [英] Linq to entities - How to define left join for grouping?

问题描述

我们有两个表 - 任务和TasksUsers（分配给任务的用户）。任务具有EntityCollection称为TaskUsers。

此查询返回每个用户名的任务数量：

  model.TaskCountByAssignee = 
（
 from t in TaskRepository.List（）
 from tu in t.TaskUsers 
 group tu by tu into tug 
 select新的{Count = tug.Count（），UserName = tug.Key.Username}）。ToList（）
  

此查询返回：

管理员11

LukLed 5

我想它返回：

管理员11

LukLed 5

null 10

某些任务没有任何作业，但我仍然希望在我的结果集中。通常，在SQL中，通过将 join 更改为 left join 来实现。在Linq，EF外，我可以使用DefaultIfEmpty（）。如何在linq中实现？

解决方案

我的第一个尝试是：

$


tu.UserName into tug
select new {Count = tug.Count（），UserName = tug.Key}）
.Union（from t in TaskRepository.List（）
where！t。 TaskUsers.Any（）
group t by 1 into tug
select new {Count = tug.Count（），UserName = null}）ToList（）;

或者沿着这些行的东西，或者只是使用两个查询，不知道这是否是最好的方法，正如我在评论中指出的在EF 4中，这更容易。

We have two tables - Tasks and TasksUsers (users assigned to task). Task has EntityCollection called TaskUsers.

This query returns number of tasks per username:

model.TaskCountByAssignee =
            (
            from t in TaskRepository.List()
            from tu in t.TaskUsers
            group tu by tu into tug
                select new {Count = tug.Count(), UserName = tug.Key.Username}).ToList()

This query returns:

Administrator 11
LukLed 5

I want it to return:

Administrator 11
LukLed 5
null 10

Some of tasks don't have any assignment, but I still want them in my result set. Normally, in SQL, it is achieved by changing join to left join. In Linq, outside EF, I could use DefaultIfEmpty(). How can it be done in linq to entities?

解决方案

My first try would be:

model.TaskCountByAssignee = (
        (from t in TaskRepository.List()
         from tu in t.TaskUsers
         group tu by tu.UserName into tug
         select new {Count = tug.Count(), UserName = tug.Key})
        .Union(from t in TaskRepository.List()
               where !t.TaskUsers.Any()
               group t by 1 into tug
               select new {Count = tug.Count(), UserName = null}).ToList();

Or something along those lines. Or just use two queries. I don't know if this is the best way, though. As I noted in comments, this is far easier in EF 4.

这篇关于Linq到实体 - 如何定义左连接进行分组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！