LINQ to SQL-多个左联接，分组依据，同一表上的左联接，计数

本文关键字：计数 SQL- to LINQ | 更新日期: 2023-09-27 18:20:29

我有一个SQL查询，它非常简单，但转换成LINQ to SQL（或LINQ to Entity）却是一场噩梦。我一直在读很多问题和文章，但我就是无法做到这一点。以下是SQL查询（Items表1-N到Operations表，Items有ParentItemId列）：

select 
      i.Id
    , i.Code
    , count(oAdd.ItemId) "Added"
    , count(oRem.ItemId) "Removed"
    , count(iChild.Id) "Existing"
from items i
left join operations oAdd on oAdd.ItemId = i.Id and oAdd.OperationTypeId = 10
left join operations oRem on oRem.ItemId = i.Id and oRem.OperationTypeId = 20
left join items iChild on iChild.ParentItemId = i.Id
group by 
      i.Id
    , i.Code

现在，经过所有的重新研究和多次尝试，我得出了以下代码，它进行了编译，但抛出了EntityCommandCompilation异常（"不支持嵌套查询。Operation1='GroupBy'Operation2='MultiStreamNest'"）：

var query =
    from item in dbContext.Items
    join oAdd in dbContext.Operations on item.Id equals oAdd.ItemId into oAddJoin
    join oRem in dbContext.Operations on item.Id equals oRem.ItemId into oRemJoin
    join oChild in dbContext.Items on item.Id equals oChild.ParentItemId into oChildJoin
    from oAddLeftJoin in oAddJoin.Where(o => o.OperationTypeId == (int)OperationTypes.AddTo).DefaultIfEmpty()
    from oRemLeftJoin in oRemJoin.Where(o => o.OperationTypeId == (int)OperationTypes.RemoveFrom).DefaultIfEmpty()
    from oChildLeftJoin in oChildJoin.DefaultIfEmpty()
    group oChildLeftJoin by new
    {
        ItemId = item.Id,
        ItemCode = item.Code,
        Added = oAddJoin.Count(),
        Removed = oRemJoin.Count(),
        Existing = oChildJoin.Count()
    }
    into oChildLeftJoinGrouped
    select new
    {
        oChildLeftJoinGrouped.Key.ItemId,
        oChildLeftJoinGrouped.Key.Added,
        oChildLeftJoinGrouped.Key.Removed,
        oChildLeftJoinGrouped.Key.Existing
    };
var summaryList = query.ToList();

我也尝试过不分组，不"加入"，而是从何处进行选择，不将计数放入分组依据中，而是放入select new，不按added/removed/existing进行分组。什么都不管用，我尝试得越多，就越不明白这是怎么回事。它只工作过一次，但没有"Existing"计数（子项的计数，即同一表上的联接-请参阅上面的SQL查询）。

在我看来，这是一个简单的SQL查询。我应该考虑一下吗？使用LINQ（如果可能的话，没有子查询）是否可以实现这一点？

谢谢你的帮助！

编辑1

下面的代码有效，但如果一个父级有N个子级，它将在结果中出现N次。因为没有分组

var query =
    from item in dbContext.Items
    join oAdd in dbContext.Operations on item.Id equals oAdd.ItemId into oAddJoin
    join oRem in dbContext.Operations on item.Id equals oRem.ItemId into oRemJoin
    from oAddLeftJoin in oAddJoin.Where(o => o.OperationTypeId == (int)OperationTypes.AddTo).DefaultIfEmpty()
    from oRemLeftJoin in oRemJoin.Where(o => o.OperationTypeId == (int)OperationTypes.RemoveFrom).DefaultIfEmpty()
    let existingCount = dbContext.Items.Count(i => i.ParentItemId == item.Id)
    select new
    {
        item,
        Added = oAddJoin.Count(),
        Removed = oAddJoin.Count(),
        existingCount
    };
var summaryList = query.ToList();

编辑2

事实上，下面是一个工作，并返回良好的价值观。甚至上面的SQL查询也是错误的。很抱歉收到垃圾邮件。

var query = 
    from item in dbContext.Items
    let addedCount    = dbContext.Operations.Count(o => o.ItemId == item.Id && o.OperationTypeId == (int)OperationTypes.AddTo)
    let removedCount  = dbContext.Operations.Count(o => o.ItemId == item.Id && o.OperationTypeId == (int)OperationTypes.RemoveFrom)
    let existingCount = dbContext.Items.Count(i => i.ParentItemId == item.Id)
    select new
    {
        item,
        Added = addedCount,
        Removed = removedCount,
        Existing = existingCount
    };
var summaryList = query.Distinct().ToList();

编辑3

工作的超级丑陋的SQL查询：

select distinct
      i.Id
    , i.Code
    , (select count(*) from operations oAdded where oAdded.itemid = i.id and oAdded.operationtypeid = 10) "Added"
    , (select count(*) from operations oAdded where oAdded.itemid = i.id and oAdded.operationtypeid = 20) "Removed"
    , (select count(*) from items ic where ic.ParentItemId = i.id) "Existing"
from items i

LINQ to SQL-多个左联接，分组依据，同一表上的左联接，计数

如果这是实体框架（您说的是LINQ to Entity），那么您可以使操作和项表成为项的导航属性。然后，您可以使用视图模型来计算计数。类似于：

public class ViewModel : Item
{
    public int Added {get;set;}
    public int Removed {get;set;}
    public int Existing {get;set;}
    public ViewModel(Item i) {
        this.id = i.id;
        this.code = i.code;
        this.Added = i.operations.Where(o => o.operationTypeID == 10).Count;
        this.Removed = i.operations.Where(o => o.operationTypeID == 20).Count;
        this.Existing = i.Items.Count;
   }
}

那么你的查询就是：

dbContext.Items.Select(i => new ViewModel(i)).ToList();
相关文章：