CODEWITHSUNDEEP
sqllinq
DIY Believer
DIY Believer

Reputation: 41

INNER JOIN LEFT JOIN in LINQ to SQL

How to convert INNER JOIN and LEFT JOIN in the following SQL query to LINQ to SQL? Thanks!

SELECT transactions.postdate,
       transactions.clientkey    AS TransClientKey,
       transactions.type         AS TransType,
       clients.clientno,
       Isnull(clients.nostmt, 0) AS CliNoStmt,
       Isnull(aging.nostmt, 0)   AS AgeNoStmt,
       pmtchecks.*
FROM   ((pmtchecks
         INNER JOIN transactions
                 ON pmtchecks.transkey = transactions.transkey)
        INNER JOIN clients
                ON transactions.clientkey = clients.clientkey)
       LEFT JOIN aging
              ON ( transactions.clientkey = aging.clientkey )
                 AND ( pmtchecks.debtorkey = aging.debtorkey )
WHERE  ( pmtchecks.debtorkey = 36927 )
       AND ( transactions.status = 0 )
       AND ( transactions.postdate <= '31-May-2012' )
       AND ( ( transactions.postdate >= '01-May-2012' )
              OR ( clients.clientno = 'UNKNOWN' ) )
ORDER  BY pmtchecks.checkdate,
          pmtchecks.checkno

Upvotes: 0

Views: 3935

Answers (2)

user1166147
user1166147

Reputation: 1610

LINQ Query Samples

EDITED

var pmtchecks = from p in urcontext.pmtchecks
                      join t in urcontext.transactions on p.transkey equals t.transkey
                      join a in urcontext.aging on t.clientkey equals a.clientkey into details
                      from d in details.Where( a => ( a.debtorkey == p.debtorkey)).DefaultIfEmpty()
                      where (p.debtorkey == 36927 && t.status == 0 && t.postdate <= '31-May-2012' 
                      && (t.postdate >= '01-May-2012' || c.clientno == 'UNKNOWN' ))
                      orderby p.checkdate, p.checkno 
                      select new
                      {
                           t.postdate,
                           t.clientkey,   
                           // TransClientKey = t.clientkey, //only works if TransClientKey is property 
                           t.type ,       
                           //TransTypet = t.type ,//property 
                           c.clientno,
                           c.nostmt,
                           //CliNoStmt =  c.nostmt ?? 0,//property 
                           a.nostmt//, 
                           //AgeNoStmt = nostmt ?? 0,//property
                           //p. ... //follow above for p columns
                       };

Upvotes: 1

yo chauhan
yo chauhan

Reputation: 12295

Hi this is kind of dummy code i cnt say its exactly right but the idea will be exactly same to get the result 

var anonymousType=  (from pm in pmtchecks
        join tr in transactions
        on pm.transkey equals tr.transkey //INNERJOIN
        join cl in clients
        on tr.clientKey equals cl.clientKey
        join ag in aging 
        on pm.debtorkey equals ag.debtorKey into ljoin //Left Join
        from lj in ljoin.DefaultOrEmpty()
        where pm.debortkey==36927 && tr.status==0 && tr.postdate<="31-May-2012" && tr.postdate>="01-May-2012" //u will have to change this to date format first
        Select new {PostDate=tr.postdate, TransClientKey=tr.clientkey,TransType=tr.type,ClientNo=cl.clientno,CliNoStmt=cl.nomst ?? 0,AgeNoStmt=ag.nomst ??0,Pmtchecks=pm } //Anonymous type from this you can extract the values and fill to your custom type
       ).OrderBy(o=>o.Pmtchecks.checkdate).OrderBy(o=>o.Pmtchecks.checkno).ToList();

Hope this will help.

Upvotes: 1

Related Questions