LINQ & Enum Left-Handed

I have an enumeration

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

I have the following query for a query to count the number of lists based on status. But now I only get if it exists. Therefore, if the processed number is zero, I will not get any values. In SQL, I would make a left join, but not quite sure how to do this in LINQ. Also not sure how to combine LEFT with an enumeration type.

Here is my request

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

So in sql I would do something like this. Assuming I have 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

I assume that I get such results New 11 InProgress 5 Processed 0 InComplete 0

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

+4
source share
1

- , , , , :

// 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};

, , , .

+4

Source: https://habr.com/ru/post/1684485/


All Articles