Need help converting a nested SQL statement to LINQ

Question

I have a statement similar to this that I need to run in Linq. I started down the road of using .Contains, and can get the first tier to work, but cannot seem to grasp what I need to do to get additional tiers.

Here's the SQL statement:

select * 
from aTable
where aTableId in
    (select aTableId from bTable
    where bTableId in 
        (select bTableId from cTable
        where cTableId in
            (select cTableId from dTable
            where dField = 'a guid'
            )
        )
    )

Jon Skeet · Accepted Answer · 2009-04-30 21:10:08Z

3

Well, the simplest translation would be:

var query = from a in aTable
            where (from b in bTable
                   where (from c in cTable
                          where (from d in dTable
                                 where dField == "a guid"
                                 select d.cTableId)
                                .Contains(c.cTableId)
                          select c.bTableId)
                         .Contains(b.bTableId)
                   select b.aTableId)
                   .Contains(a.aTableId)
            select a;

However, it's likely that a join would be significantly simpler:

var query = from a in aTable
            join b in bTable on a.aTableId equals b.aTableId
            join c in cTable on b.bTableId equals c.bTableId
            join d in dTable on c.cTableId equals d.cTableId
            where d.dField == "a guid"
            select a;

I haven't checked, but I think they'll do the same thing...

answered Apr 30, 2009 at 21:10

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

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

2 Comments

Levi Rosol Over a year ago

That's what i needed. The only addition was to define the type in the .Contains statements. ie: .Contains((Guid) a.aTableId)

Levi Rosol Over a year ago

and yes, although the SQL is different for both of these options, the Execution Plan is identical. The second option using Joins is a much more readable solution.

Collectives™ on Stack Overflow

Need help converting a nested SQL statement to LINQ

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related