How to convert following SQL Query into a LINQ to SQL query

Question

I have following SQL Query and would like to convert to LINQ to SQL which I will use in entity framework 5.0

var internationalDesksList =
            from internationalDesks in _context.InternationalDesks
            from subsection in
                _context.Subsections.Where(
                    s =>
                    internationalDesks.EBALocationId == s.LocationId ||
                    internationalDesks.FELocationId == s.LocationId).DefaultIfEmpty()
            where subsection.PublicationId == 1

            select new {internationalDesks.Id, subsection.LocationId};

I have referred the following posts and answers. Though no luck.

When I tried this query in LINQPad I got the following answer which is correct.

-- Region Parameters
DECLARE @p0 Int = 1
-- EndRegion
SELECT [t0].[Id], [t1].[Id] AS [Id1]
FROM [InternationalDesks] AS [t0]
LEFT OUTER JOIN [Subsection] AS [t1] ON (([t0].[FELocationId]) = [t1].[LocationId]) OR (([t0].[EBALocationId]) = [t1].[LocationId])
WHERE [t1].[PublicationId] = @p0

However in entity framework 5 ( DBContext ) it is not providing me with the correct query. When I checked in SQL profiler all columns in subsection table is selected. That's it.

Following is the result:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Description] AS [Description], 
[Extent1].[PracticeAreaId] AS [PracticeAreaId], 
[Extent1].[LocationId] AS [LocationId], 
...
FROM [dbo].[Subsection] AS [Extent1]

Don't know what could be problem. Please help me.

Please provide LINQ query which you are using – Sergey Berezovskiy Jun 21 '13 at 15:01 — Sergey Berezovskiy, Jun 21 '13 at 15:01
@lazyberezovsky thanks, I have updated with the LINQ Query. – manu Jun 21 '13 at 15:05 — manu, Jun 21 '13 at 15:05

Sergey Berezovskiy · Answer 1 · 2013-06-21T15:54:18.873

With LINQ you can't do LEFT OUTER JOIN on some boolean expression, only equijoins are supported. So, you can generate CROSS JOIN this way:

var internationalDesksList =
            from internationalDesks in _context.InternationalDesks
            from subsection in _context.Subsections
            where subsection.PublicationId == 1 &&
                  (internationalDesks.EBALocationId == subsection.LocationId ||
                   internationalDesks.FELocationId == subsection.LocationId)
            select new {
                internationalDesks.Id, 
                subsection.LocationId
            };

EF 5 will generate following SQL:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent2].[LocationId] AS [LocationId]
FROM  [dbo].[InternationalDesks] AS [Extent1]
CROSS JOIN [dbo].[Subsections] AS [Extent2]
WHERE (1 = [Extent2].[PublicationId]) AND 
      ([Extent1].[EBALocationId] = [Extent2].[LocationId] OR 
       [Extent1].[FELocationId] = [Extent2].[LocationId])

As you can see, only required columns are selected. I also checked this query in LINQ to SQL - following query is generated:

DECLARE @p0 Int = 1

SELECT [t0].[Id], [t1].[LocationId]
FROM [InternationalDesks] AS [t0], [Subsections] AS [t1]
WHERE ([t1].[PublicationId] = @p0) AND 
      (([t0].[EBALocationId] = [t1].[LocationId]) OR 
       ([t0].[FELocationId] = [t1].[LocationId]))

lazyberezovsky, it adds the condition in where clause where I need them part of left join. -- Region Parameters DECLARE @p0 Int = 223 -- EndRegion SELECT [t0].[Id], [t1].[LocationId] FROM [InternationalDesks] AS [t0], [Subsection] AS [t1] WHERE ([t1].[PublicationId] = @p0) AND ((([t0].[EBALocationId]) = [t1].[LocationId]) OR (([t0].[FELocationId]) = [t1].[LocationId])) — manu, Jun 21 '13 at 15:20
@Elangesh you can't have conditions as part of left join. It's impossible to do with LINQ — Sergey Berezovskiy, Jun 21 '13 at 15:59

How to convert following SQL Query into a LINQ to SQL query

1 Answers1