CODEWITHSUNDEEP
linqjoinleft-join
James
James

Reputation: 2610

multiple tables join by Linq

I have 3 tables which named Player, PlayerDetails and Team. I want to write a linq statement such as 

from p in Player join d in PlayerDetails on p.ID equals d.PID

and then right join team table, Player has a column named TID which face to Team table's ID.

I have tried to write a statement like

from p in Player join d in PlayerDetails on p.ID equals d.PID into PlayerGroup
from t in team join g in PlayerGroup on t.ID equals g.p.ID

It certainly can't work. I'm not sure how to write such type of query statement, table left join table2 then right join table3.

Who can help?

Upvotes: 1

Views: 1380

Answers (1)

Nils Magne Lunde
Nils Magne Lunde

Reputation: 1824

I believe you could do something like this:

var LeftJoin =
    from p in Player
    join d in PlayerDetails on p.ID equals d.PID into pd
    from d in pd.DefaultIfEmpty()
    select new
    {
        pID = p.ID,
        pTID = p.TID,
        dID = d.ID
    };

var RightJoin =
    from t in Team
    join l in LeftJoin on t.ID equals l.pTID into tl
    from l in tl.DefaultIfEmpty()
    select new
    {
        tID = t.ID,
        pID = l.pID,
        pTID = l.PTID,
        dID = l.dID
    };

To do everything in one query, I think you could do (not tested) something like this:

var RightJoin =
    from t in Team
    join l in
        (from p in Player
         join d in PlayerDetails on p.ID equals d.PID into pd
         from d in pd.DefaultIfEmpty()
         select new
         {
             pID = p.ID,
             pTID = p.TID,
             dID = d.ID
         })
    on t.ID equals l.pTID into tl
    from l in tl.DefaultIfEmpty()
    select new
    {
        tID = t.ID,
        pID = l.pID,
        pTID = l.PTID,
        dID = l.dID
    };

Upvotes: 1

Related Questions