CODEWITHSUNDEEP
sql-serverlinqentity-framework
Sayak Banerjee
Sayak Banerjee

Reputation: 1964

EF6 - Generating unneeded nested queries

I have the following tables:

MAIN_TBL:
Col1 | Col2 | Col3
------------------
A    | B    | C
D    | E    | F

And:

REF_TBL:
Ref1 | Ref2 | Ref3
------------------
A    | G1   | Foo
D    | G1   | Bar
Q    | G2   | Xyz

I wish to write the following SQL query:

SELECT    M.Col1
FROM      MAIN_TBL M
LEFT JOIN REF_TBL R
ON        R.Ref1 = M.Col1 
AND       R.Ref2 = 'G1'
WHERE     M.Col3 = 'C'

I wrote the following LINQ query:

from main in dbContext.MAIN_TBL
join refr in dbContext.REF_TBL
on "G1" equals refr.Ref2
into refrLookup
from refr in refrLookup.DefaultIfEmpty()
where main.Col1 == refr.Col1
select main.Col1

And the generated SQL was:

SELECT 
    [MAIN_TBL].[Col1]
    FROM  (SELECT 
    [MAIN_TBL].[Col1] AS [Col1],
    [MAIN_TBL].[Col2] AS [Col2],
    [MAIN_TBL].[Col3] AS [Col3]
    FROM [MAIN_TBL]) AS [Extent1]
    INNER JOIN (SELECT 
    [REF_TBL].[Ref1] AS [Ref1],
    [REF_TBL].[Ref2] AS [Ref2],
    [REF_TBL].[Ref3] AS [Ref3]
    FROM [REF_TBL]) AS [Extent2] ON [Extent1].[Col1] = [Extent2].[Ref1]
    WHERE ('G1' = [Extent2].[DESCRIPTION]) AND ([Extent2].[Ref1] IS NOT NULL) AND CAST( [Extent1].[Col3] AS VARCHAR) = 'C') ...

Looks like it is nesting a query within another query, while I just want it to pull from the table. What am I doing wrong?

Upvotes: 0

Views: 191

Answers (1)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

I may be wrong, but it looks like you don't do the same in linq query and sql query, especially on your left joining clause.

I would go for this, if you want something similar to your sql query.

from main in dbContext.MAIN_TBL.Where(x => x.Col3 == "C")
join refr in dbContext.REF_TBL
on new{n = "G1", c = main.Col1} equals new{n = refr.Ref2, c = refr.Col1}
   into refrLookup
from r2 in refrLookup.DefaultIfEmpty()
select main.Col1

By the way, it doesn't make much sense to left join on a table which is not present in the select clause : you will just get multiple identical Col1 if there's more than one related item in the left joined table...

Upvotes: 2

Related Questions