How to create LINQ from complex SQL?

Question

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.

Gilad Green · Accepted Answer · 2018-07-04 14:27:18Z

1

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

edited Jul 4, 2018 at 14:27

answered Jul 4, 2018 at 14:22

Gilad Green

37.3k7 gold badges67 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

NotFound Over a year ago

Compares the wrong values as ID and DocSubClass are not compared to the same property names. You would have to change the property names in either of the anonymous class (switch them around)

SelvaS · Accepted Answer · 2018-07-04 14:54:44Z

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

user7336033 · Accepted Answer · 2018-07-04 14:22:50Z

0

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;

answered Jul 4, 2018 at 14:22

user7336033

2973 silver badges22 bronze badges

Comments

Gaurav Jalan · Accepted Answer · 2018-07-04 14:27:26Z

0

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}

answered Jul 4, 2018 at 14:27

Gaurav Jalan

4972 silver badges15 bronze badges

Comments

Cetin Basoz · Accepted Answer · 2018-07-04 14:30:37Z

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.

Collectives™ on Stack Overflow

How to create LINQ from complex SQL?

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related