CODEWITHSUNDEEP
c#sqllinq
user7336033
user7336033

Reputation: 297

How to create LINQ from complex SQL?

I've this SQL query:

 SELECT D.ID
 FROM Documents AS D
 INNER JOIN DocClasses AS DC WITH (NOLOCK) 
 ON D.DocClass = DC.ID
 INNER JOIN DocSubClasses AS DSC WITH (NOLOCK) 
 ON D.DocSubClass = DSC.ID AND DSC.DocClassID = DC.ID
 INNER JOIN DocPathFolders AS F WITH (NOLOCK) 
 ON D.DocPathFolderID = F.ID
 WHERE 
    DC.ShortName = 'PAY' AND DSC.Name = 'xxxxx' 
    AND UPPER(F.Description) = 'READY TO SEND'

I'm trying to convert this query into LINQ. Here is what I've done so far:

 from D in ctx.Documents
 join DC in ctx.DocClasses on D.DocClass equals DC.ID
 join DSC in ctx.DocSubClasses 
     on new { D.DocSubClass, DSC.DocClassID } equals new { DSC.ID, DC.ID }
 join F in ctx.DocPathFolders 
     on D.DocPathFolderID equals F.ID
 where 
     DC.ShortName == "PAY" 
     && DSC.Name == "xxxxx" 
     && (F.Description).ToUpper() == "READY TO SEND"
 select D.ID;

I'm getting error in this line:

join DSC in ctx.DocSubClasses on new { D.DocSubClass, DSC.DocClassID } equals new { DSC.ID, DC.ID }

Here, I'm getting following error:

The name 'DSC' is not in scope on the left side of 'equals'

The name 'DC' is not in scope on the right side of 'equals'

I'm new in LINQ and that's why I can't solve these error. I would apperciate any suggestion on the above. Thanks.

Upvotes: 3

Views: 98

Answers (5)

SelvaS
SelvaS

Reputation: 2125

You can try with Lambda Join instead of using LINQ.

Your JOIN statements look like below in Lambda.

C# Fiddle

var result = ctx.Documents.Join(ctx.DocClasses , d => new { Id = d.DocClass }, dc => new { Id = dc.ID }, (d, dc) => new { doc = d, docClass = dc }) //INNER JOIN DocClasses  
                          .Join(ctx.DocSubClasses , d => new { sc = d.doc.DocSubClass, Id = d.docClass.ID }, dsc => new { sc = dsc.ID, Id = dsc.DocClassID }, (d, dsc) => new { doc = d, dsc = dsc } ) //INNER JOIN DocSubClasses
                          .Join(ctx.DocPathFolders, d => new { fId = d.doc.doc.DocPathFolderID }, f => new { fId = f.ID }, (d, f) => new { doc = d, f = f }) //INNER JOIN DocPathFolders
                          .Where(x => x.doc.doc.docClass.ShortName == "PAY" && x.doc.dsc.Name == "xxxxx" && x.f.Description.ToUpper() == "READY TO SEND")//Apply where clause
                          .Select(y => y.doc.doc.doc.ID);//Select whatever you want

Upvotes: 1

Cetin Basoz
Cetin Basoz

Reputation: 23797

Hint: Provided you have set your relations appropriately in your database you almost never need to use "join". It is easier to write in LINQ and relations are exposed as "navigational properties". You simply use "dot notation". ie:

var result = from d in ctx.Documents
           where d.DocClasses.Any( dc => dc.ShortName == "PAY" 
              && dc.DocSubClasses.Any( dsc => dsc.Name == "xxxxx" )
              && d.DocPathFolders.Any( dpf => dpf.Description.ToUpper() == "READY TO SEND" )
            select d.ID;

Note: We don't have any idea on your model. I assumed the relations as 1-To-Many. If it were Many-To-1 then you would simply use notation like:

where d.DocClass.ShortName == "PAY" 
                  && d.DocClass.DocSubClass.Name == "xxxxx" 
                  && d.DocPathFolder.Description.ToUpper() == "READY TO SEND"

HINT2: Download and start using Linqpad from LinqPad.net. It is a great tool where you can not only test your LINQ queries but use as a .Net scratch pad.

Upvotes: 0

Gaurav Jalan
Gaurav Jalan

Reputation: 497

Just switch DSC into right side of equals and DC to the left, and it should work fine. 

join DSC in ctx.DocSubClasses on new { D.DocSubClass, DC.ID } equals new { DSC.ID, DSC.DocClassID}

Upvotes: 0

Gilad Green
Gilad Green

Reputation: 37299

You should rearrange the properties in the anonymous objects like this:

join DSC in ctx.DocSubClasses 
on new { D.DocSubClass, DC.ID } equals new { DSC.DocClassID, DSC.ID }

Those on the right should be of the table you are joining (DSC) and those on the left can be of previous tables

Upvotes: 1

user7336033
user7336033

Reputation: 297

Here is how I've solved my issue:

from D in ctx.Documents
join DC in ctx.DocClasses on D.DocClass equals DC.ID
join DSC in ctx.DocSubClasses on new { D.DocSubClass, DC.ID } equals new { DocSubClass = DSC.ID, ID = DSC.DocClassID }
join F in ctx.DocPathFolders on D.DocPathFolderID equals F.ID
where DC.ShortName == "PAY" && DSC.Name == "xxxx" && (F.Description).ToUpper() == "READY TO SEND"
select D.ID;

Upvotes: 0

Related Questions