Using LINQ left join with multiple conditions and subquery

Question

I want the left to join two tables and summarize 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 the message, I got this message:

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

Can anyone help?

Answer 1

You need to join an anonymous type, I don’t think you can choose a class like you:

 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 NULL in the database, you can solve it as follows:

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

This means that ID_KATALOG is an int , of course. Or you can do it the other way around too normal:

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