Using LINQ left join with multiple conditions and subquery

Question

I want to left join two tables and sum one field so I made this query:

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

but when I debug I got this message :

The type 'Reference.Inventory.SearchDetailRequester' appears in two structurally incompatible initializations within a single LINQ to Entities query. A type can be initialized in two places in the same query, but only if the same properties are set in both places and those properties are set in the same order.

Is there anyone can help?

Answer 1

You need to to the join with anonymous type, I don't think you can choose a class like you did:

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

If for example b.ID_KATALOG is nullable in the database, you can solve it like this:

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

That is assuming ID_KATALOG is an int of course. Or you can do it the other way around too normally:

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