LINQ&左联接枚举 [英] LINQ & Enum with Left Join

问题描述

我有一个枚举

public enum Status { New, InProgress, Processed, InComplete};

我有以下查询要查询，以根据状态为我提供列表计数.但是现在，我只知道它是否存在.因此，如果处理的计数为零，我将不会获得任何值.在SQL中，我会进行左联接，但不太确定如何在LINQ中进行联接.也不确定如何使用枚举类型进行LEFT连接.

I have the following query to query to give me list counts based upon the status. But right now I'm only getting if it exists. So if Processed count is zero I won't get any values. In SQL I would do a left join but not quite sure how to do that in LINQ. Also not sure how to do a LEFT join with an enumeration type.

这是我的查询

var results = DbContext.Orders
                       .Where(i => i.Id== Id)
                       .GroupBy(row => new { row.Status})
                       .Select(g => new Stats()
                       {
                           Status = g.Key.Status,
                           Count = g.Count()
                       }).ToList();

所以在sql中，我会做这样的事情.假设我有一个状态表.

So in sql I would do something like this. Assuming if I had a status table.

SELECT status_id, s.name, count(status_id) count
FROM order o LEFT JOIN status s ON (o.status_id = s.status_id)
WHERE o.id = 1
GROUP BY status_id, s.name

我假设我会得到这样的结果 新11 进行中5 已处理0 未完成0

I'm assuming I would get results like this New 11 InProgress 5 Processed 0 InComplete 0

在我的LINQ查询中，我不会得到新处理的"或未完成的".

In my LINQ query I would not get New Processed or InComplete.

推荐答案

一种方法是在数据库中运行select以获取当前结果，然后通过加入内存中的枚举来填充空白，如下所示: /p>

One approach is to run the select in the database to get your current result, and then fill in the blanks by joining to enum in memory, like this:

// This selection is performed in the database
var raw = DbContext.Orders
    .Where(i => i.Id== Id)
    .GroupBy(row => new { row.Status })
    .Select(g => new Stats {
        Status = g.Key.Status,
        Count = g.Count()
    }).ToList();
// This join is performed in memory
var results =
    from e in Enum.GetValues(typeof(Status)).Cast<Status>()
    join r in raw on e equals r.Status into rs
    from r in rs.DefaultIfEmpty()
    select new { Status = e, Count = r?.Count ?? 0};

由于从RDBMS传输到内存的数据量没有变化，因此在内存中进行联接不会对该操作的总成本造成重大影响.

Since the amount of data transferred to memory from the RDBMS does not change, joining in memory does not bring a significant change in the overall cost of this operation.

这篇关于LINQ&左联接枚举的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！