CODEWITHSUNDEEP
asp.net-mvcentity-frameworklinqlambda
tomas
tomas

Reputation: 153

LINQ JOIN with WHERE condition

I have a problem with the creation of LINQ query with lambda expression. I need join two tables and make some conditions. I have two tables MSR and BOMDetail.

MSR had theese columns -> MSRID, PN, Buyer,Plant EditDate. BomDetail had theese columns -> BOMID, PN, AltQty, Plant, EditDate.

And i need to write this query into LINQ.

SELECT MSR.PN, Buyer, MSR.EditDate, MSR.Plant FROM MSR 
JOIN BomDetail bd ON MSR.PN = bd.PN AND MSR.Plant = bd.Plant
WHERE LEN(ISNULL(bd.AltQty,''))>0

I need to make 2 conditions PN must equals between tables and Plant's too. I have for result ViewModel in asp.net MVC.

public class MSRViewModel
{
    public string PN { get; set; }
    public string Buyer { get; set; }
    public string Plant { get; set; }
    public DateTime EditDate { get; set; }

}

And here is my sample, it works fine, but i don't know where i must write the second condition for bd.Plant = MSR.Plant.

var data = DbContext.BomDetails.Where(x => !string.IsNullOrEmpty(x.AltQty))
                        .Join(DbContext.MSRs
                        , bd => bd.PN,
                        msr => msr.PN,
                        (bd, msr) => new MSRViewModel
                        {
                            PN = msr.PN,
                            Buyer = msr.Buyer,
                            Plant = msr.Plant,
                            EditDate = msr.EditDate
                        }).ToList().AsEnumerable();

Thanks.

Upvotes: 0

Views: 793

Answers (1)

Tom Wuyts
Tom Wuyts

Reputation: 854

You can do this as follows:

var data = DbContext.BomDetails.Where(x => !string.IsNullOrEmpty(x.AltQty))
                    .Join(DbContext.MSRs
                    , bd => new { bd.PN, bd.Plant },
                    msr => new { msr.PN, msr.Plant },
                    (bd, msr) => new MSRViewModel
                    {
                        PN = msr.PN,
                        Buyer = msr.Buyer,
                        Plant = msr.Plant,
                        EditDate = msr.EditDate
                    }).ToList().AsEnumerable();

Upvotes: 2

Related Questions