使用LINQ左联接多个条件和子查询

本文关键字：条件 查询 LINQ 使用 | 更新日期: 2023-09-27 18:27:04

我想左联接两个表并求和一个字段，所以我进行了以下查询：

IQueryable<Reference.Inventory.SearchDetailRequester> _qRequester =
    from a in dbErp.EPROC_TR_ER_DETAIL
    join b in dbErp.EPROC_TR_INVENTORY on
    new Reference.Inventory.SearchDetailRequester { ID_REQUEST = a.ID_REQUEST , ID_KATALOG = a.ID_KATALOG}
    equals
    new Reference.Inventory.SearchDetailRequester { ID_REQUEST = b.ID_REQUEST, ID_KATALOG = b.ID_KATALOG }
    into inv_join 
    from c in inv_join.DefaultIfEmpty()
    where a.ID_REQUEST == ID_REQUEST && a.APROVE_BY_DS == 1 && a.APROVE_BY_GS == 1
    select new Reference.Inventory.SearchDetailRequester
    {
        ID_KATALOG = a.ID_KATALOG,
        TYPE_OF_GGS = a.TYPE_OF_GGS,
        TRANSACTION_TYPE = "OUT",
        DATE = c.DATE ?? "",
        QTY = -1 * c.QTY ?? a.QTY,
        ID_INVENTORY = c.ID_INVENTORY,
        QTY_AVAILABLE = ((from d in dbErp.EPROC_TR_INVENTORY
                          where d.ID_KATALOG == a.ID_KATALOG
                          group d by new { d.ID_KATALOG } into e
                          select new { qty_ava = (System.Int32)e.Sum(p => p.QTY ?? 0) }).FirstOrDefault().qty_ava)
    };

但当我调试时，我得到了这样的消息：

类型"Reference.Inventure.SearchDetailRequester"出现在单个LINQ to Entities查询中的两个结构不兼容的初始化中。类型可以在同一查询中的两个位置初始化，但前提是在两个位置都设置了相同的属性，并且这些属性的设置顺序相同。

有人能帮忙吗？

使用LINQ左联接多个条件和子查询

您需要使用匿名类型的联接，我认为您不能像以前那样选择类：

new { ID_REQUEST = a.ID_REQUEST , ID_KATALOG = a.ID_KATALOG}
equals
new { ID_REQUEST = b.ID_REQUEST, ID_KATALOG = b.ID_KATALOG }

例如，如果b.ID_KATALOG在数据库中可以为null，则可以这样解决：

new { ID_REQUEST = a.ID_REQUEST , ID_KATALOG = a.ID_KATALOG}
equals
new { ID_REQUEST = b.ID_REQUEST, ID_KATALOG = (int)b.ID_KATALOG }

这当然是假设ID_KATALOG是int。或者你可以用另一种方式来做——太正常了：

new { ID_REQUEST = a.ID_REQUEST , ID_KATALOG = (int?)a.ID_KATALOG}
equals
new { ID_REQUEST = b.ID_REQUEST, ID_KATALOG = b.ID_KATALOG }
相关文章：