CODEWITHSUNDEEP
c#linq
user1220099
user1220099

Reputation: 127

Many to many query in LINQ

I have 4 tables:

Address - AddressID, City, Address, ZipCode

OBAddress - OBID, AddressID

OB - OBID

States - StateID, StateAbbreviation

How do I join the three tables which are in a many to many relationship? I tried:

var recordsPhysicalAddress = ( from a in db.Addresses
    join oba in db.obAddresses on a.AddressID equals oba.AddressID
    join ob in db.obAddresses on oba.obID equals passedinID
    join s in db.States on a.StateID equals s.StateID
    where oba.obID == ob.obID
    && a.AddressTypeID == '5' //5 is records physical address
    select new
    {
        a.Address1, 
        a.City, 
        a.StateID,
        s.StateAbbreviation,
        a.ZipCode
    }).FirstOrDefault();

Upvotes: 0

Views: 180

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236188

var query = (from a in db.Addresses
             join oba in db.obAddresses on a.AddressID equals oba.AddressID
             // do not join with db.obAddresses again
             join s in db.States on a.StateID equals s.StateID
             where oba.obID == passedinID // filter here
                   && a.AddressTypeID == '5'
             select new {
                   a.Address,
                   a.City,
                   a.StateID,
                   s.StateAbbreviation,
                   a.ZipCode
             }).FirstOrDefault();

I put some remarks in comments to query. But still wonder why you have mentioned forth table in question?

Upvotes: 1

Related Questions