Convert nested SQL query to LINQ Query

Question

Is there a way to accurately convert the following SQL query to LINQ

SELECT * FROM T1 
WHERE ColumnA IN (
    SELECT FkColumnA FROM T2 
    WHERE FkColumnB IN (
        SELECT ColumnB FROM T3 
        WHERE FkColumnC IN (
            SELECT ColumnC FROM T4 
            WHERE FkColumnD = 1)) AND FkColumnE is null AND ColumnF = 0)

Also, does anyone know of any documentation wherein any logic or guideline to convert SQL queries to LINQ is laid out?

EDIT 1:

The equivalent for the above using JOINS would be as below:

select * from T1  a
inner join T2 b on a.FKColumnA = b.ColumnA
inner join T3 c on c.ColumnB  = b.FkColumnB
inner join T4 d on d.ColumnC = c.FkColumnC
where a.FkColumnD is null and a.ColumnE = 0
and d.ColumnC = 1

and it's equivalent LINQ query would be

var linq = from q in context.T1
           join r in context.T2
           on q.FKColumnA equals r.ColumnA
           join s in context.T3 
           on r.FkColumnB equals s.ColumnB
           join t in context.T4 
           on s.FkColumnC equals t.ColumnC
           where q.FkColumnD != null && q.ColumnE == false && t.ColumnC == 56816
           select q.FkColumnF;

But using JOINS looked to be a bit more simpler and better in LINQ. Thus the question is for my knowledge purpose only.

No specific reason. I can do it with JOINS as well. This is just for my knowledge as with JOINS I know how to convert SQL to LINQ — Yash Saraiya
– Yash Saraiya, Commented Feb 1, 2017 at 10:02

Chris Pickford · Accepted Answer · 2017-02-01 10:19:00Z

2

Translating your query literally, we get the following LINQ statement:

var results = table1.Where(t1 => table2.Where(
        t2 =>
            table3.Where(
                    t3 =>
                        table4.Where(t4 => t4.FkColumnD == 1)
                            .Select(t4 => t4.ColumnC)
                            .Contains(t3.FkColumnC))
                .Select(t3 => t3.ColumnB)
                .Contains(t2.FkColumnB) && !t2.FkColumnE.HasValue && t2.ColumnF == 0)
    .Select(t2 => t2.FkColumnA)
    .Contains(t1.ColumnA));

This results in an IEnumerable<T1> which you can use as required.

As far as I know there is no "documentation" on converting syntax, this, as a developer, is your job. However, I personally find LINQPad very useful when constructing LINQ statements.

answered Feb 1, 2017 at 10:19

Chris Pickford

9,0606 gold badges51 silver badges76 bronze badges

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

Collectives™ on Stack Overflow

Convert nested SQL query to LINQ Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related