linq left join with filtering in joined

c# entity-framework-6 linq

Question

I am getting duplication meals here

IEnumerable<DTOHotMealsPrice> lst = (from m in this.dbEntity.HOT_MEALS
    join ml in this.dbEntity.HOT_MEALS_PRICE   on m.MEALSID equals ml.MEALSID into mls
    from mls1 in mls.DefaultIfEmpty()
    where mls1.HOTID==hotelId
    select new DTOHotMealsPrice
    {
        MEALSID = m.MEALSID,
        MEALSNAME = m.MEALSNAME,
        CHPRICE = mls1.CHPRICE,
        PRICE = mls1.PRICE,
        HOTID = mls1.HOTID
    }).Distinct().ToList();

I want to list all HOT_MEALS and also join with HOT_MEALS_PRICE when a mealsid reference on it

When mls1.HOTID==hotelId, this will getting innerjoin results How could it will a proper result

1
0
5/26/2017 8:29:48 AM

Accepted Answer

Two Way solve Solution

Remove Where Case

Add where Case In Join table

Show for Where Case Blow Linq Query

first Query

IEnumerable<DTOHotMealsPrice> lst = (from m in this.dbEntity.HOT_MEALS
join ml in this.dbEntity.HOT_MEALS_PRICE.where(c=>c.HOTID==hotelId)   on m.MEALSID equals ml.MEALSID into mls
from mls1 in mls.DefaultIfEmpty()   
select new DTOHotMealsPrice
{
    MEALSID = m.MEALSID,
    MEALSNAME = m.MEALSNAME,
    CHPRICE = mls1.CHPRICE,
    PRICE = mls1.PRICE,
    HOTID = mls1.HOTID
}).Distinct().ToList();

Second is:

IEnumerable<DTOHotMealsPrice> lst = (from m in this.dbEntity.HOT_MEALS
join ml in this.dbEntity.HOT_MEALS_PRICE   on m.MEALSID equals ml.MEALSID into mls
from mls1 in mls.where(c=>c.HOTID==hotelId).DefaultIfEmpty()
select new DTOHotMealsPrice
{
    MEALSID = m.MEALSID,
    MEALSNAME = m.MEALSNAME,
    CHPRICE = mls1.CHPRICE,
    PRICE = mls1.PRICE,
    HOTID = mls1.HOTID
}).Distinct().ToList();
0
5/26/2017 12:02:57 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow