[c#]使用 LINQ 左的联接多个条件和子查询

标签: Linq SQLServer C#
发布时间: 2017/2/26 14:30:12
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我想去左的联接两个表和总和一个字段,所以我做了这个查询︰

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.Inventory.SearchDetailRequester' 类型出现在单个 LINQ to 实体查询内的两个结构不兼容初始化操作。在两个地方在同一查询中,但只有如果在两个地方设置相同的属性和中以相同的顺序,设置这些属性,可以初始化类型。

有没有任何人能帮吗?

解决方法 1:

你需要加入与匿名类型,我不认为你可以选择一类像你这样做︰

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 }
官方微信
官方QQ群
31647020