How to convert Linq query to SQL query

Question

I am not be able to understand that All function functionality in below code and i just want to write SQL stored procedure for this below query..

  IQueryable<Situation> situations = db.Situations
            .Include("SituationHandicap")
            .Include("SituationAttributes")
            .Where(x =>
                x.Deleted == false &&
                x.Published == true &&
                x.SituationCategory.Id == C
                && (A.All(a => x.SituationAttributes.Any(sa => a == sa.AttributesId)) ||
                A.Any(a => x.SituationAttributes.Any(sa => a == sa.AttributesId)))
            && (H.All(a => x.SituationHandicap.Any(sa => a == sa.HandicapId)) ||
            H.Any(a => x.SituationHandicap.Any(sa => a == sa.HandicapId)))
            );

I just confuse what to use in place of .All() method in SQL...please answer

I have just found the Entity Framework Generated SQL query of the Linq query which is too long as below

SELECT 
    [UnionAll1].[Id] AS [C1], 
    [UnionAll1].[Id1] AS [C2], 
    [UnionAll1].[Name] AS [C3], 
    [UnionAll1].[YdsToPin] AS [C4], 
    [UnionAll1].[WindSpeed] AS [C5], 
    [UnionAll1].[Elevation] AS [C6], 
    [UnionAll1].[WindDirection] AS [C7], 
    [UnionAll1].[startCoordinate] AS [C8], 
    [UnionAll1].[pinCoordinate] AS [C9], 
    [UnionAll1].[targetCoordinate] AS [C10], 
    [UnionAll1].[lineType] AS [C11], 
    [UnionAll1].[Published] AS [C12], 
    [UnionAll1].[Unpublished] AS [C13], 
    [UnionAll1].[Deleted] AS [C14], 
    [UnionAll1].[Book_Id] AS [C15], 
    [UnionAll1].[ImageFile_Id] AS [C16], 
    [UnionAll1].[SituationCategory_Id] AS [C17], 
    [UnionAll1].[C1] AS [C18], 
    [UnionAll1].[Id2] AS [C19], 
    [UnionAll1].[SituationId] AS [C20], 
    [UnionAll1].[HandicapId] AS [C21], 
    [UnionAll1].[C2] AS [C22], 
    [UnionAll1].[C3] AS [C23], 
    [UnionAll1].[C4] AS [C24]
    FROM  (SELECT 
        CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Id] AS [Id1], 
        [Extent1].[Name] AS [Name], 
        [Extent1].[YdsToPin] AS [YdsToPin], 
        [Extent1].[WindSpeed] AS [WindSpeed], 
        [Extent1].[Elevation] AS [Elevation], 
        [Extent1].[WindDirection] AS [WindDirection], 
        [Extent1].[startCoordinate] AS [startCoordinate], 
        [Extent1].[pinCoordinate] AS [pinCoordinate], 
        [Extent1].[targetCoordinate] AS [targetCoordinate], 
        [Extent1].[lineType] AS [lineType], 
        [Extent1].[Published] AS [Published], 
        [Extent1].[Unpublished] AS [Unpublished], 
        [Extent1].[Deleted] AS [Deleted], 
        [Extent1].[Book_Id] AS [Book_Id], 
        [Extent1].[ImageFile_Id] AS [ImageFile_Id], 
        [Extent1].[SituationCategory_Id] AS [SituationCategory_Id], 
        [Extent2].[Id] AS [Id2], 
        [Extent2].[SituationId] AS [SituationId], 
        [Extent2].[HandicapId] AS [HandicapId], 
        CAST(NULL AS int) AS [C2], 
        CAST(NULL AS int) AS [C3], 
        CAST(NULL AS int) AS [C4]
        FROM  [dbo].[Situations] AS [Extent1]
        LEFT OUTER JOIN [dbo].[SituationHandicaps] AS [Extent2] ON [Extent1].[Id] = [Extent2].[SituationId]
        WHERE (0 = [Extent1].[Deleted]) AND (1 = [Extent1].[Published]) AND ([Extent1].[SituationCategory_Id] = 1) AND (( NOT EXISTS (SELECT 
            1 AS [C1]
            FROM ( SELECT 
                36 AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
            )  AS [Project1]
            WHERE ( NOT EXISTS (SELECT 
                1 AS [C1]
                FROM [dbo].[SituationAttributes] AS [Extent3]
                WHERE ([Extent1].[Id] = [Extent3].[SituationId]) AND ([Project1].[C1] = [Extent3].[AttributesId])
            )) OR (CASE WHEN ( EXISTS (SELECT 
                1 AS [C1]
                FROM [dbo].[SituationAttributes] AS [Extent4]
                WHERE ([Extent1].[Id] = [Extent4].[SituationId]) AND ([Project1].[C1] = [Extent4].[AttributesId])
            )) THEN cast(1 as bit) ELSE cast(0 as bit) END IS NULL)
        )) OR ( EXISTS (SELECT 
            1 AS [C1]
            FROM ( SELECT 
                36 AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]
            )  AS [Project5]
            WHERE  EXISTS (SELECT 
                1 AS [C1]
                FROM [dbo].[SituationAttributes] AS [Extent5]
                WHERE ([Extent1].[Id] = [Extent5].[SituationId]) AND ([Project5].[C1] = [Extent5].[AttributesId])
            )
        ))) AND (( NOT EXISTS (SELECT 
            1 AS [C1]
            FROM ( SELECT 
                1 AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]
            )  AS [Project8]
            WHERE ( NOT EXISTS (SELECT 
                1 AS [C1]
                FROM [dbo].[SituationHandicaps] AS [Extent6]
                WHERE ([Extent1].[Id] = [Extent6].[SituationId]) AND ([Project8].[C1] = [Extent6].[HandicapId])
            )) OR (CASE WHEN ( EXISTS (SELECT 
                1 AS [C1]
                FROM [dbo].[SituationHandicaps] AS [Extent7]
                WHERE ([Extent1].[Id] = [Extent7].[SituationId]) AND ([Project8].[C1] = [Extent7].[HandicapId])
            )) THEN cast(1 as bit) ELSE cast(0 as bit) END IS NULL)
        )) OR ( EXISTS (SELECT 
            1 AS [C1]
            FROM ( SELECT 
                1 AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable4]
            )  AS [Project12]
            WHERE  EXISTS (SELECT 
                1 AS [C1]
                FROM [dbo].[SituationHandicaps] AS [Extent8]
                WHERE ([Extent1].[Id] = [Extent8].[SituationId]) AND ([Project12].[C1] = [Extent8].[HandicapId])
            )
        )))
    UNION ALL
        SELECT 
        2 AS [C1], 
        [Extent9].[Id] AS [Id], 
        [Extent9].[Id] AS [Id1], 
        [Extent9].[Name] AS [Name], 
        [Extent9].[YdsToPin] AS [YdsToPin], 
        [Extent9].[WindSpeed] AS [WindSpeed], 
        [Extent9].[Elevation] AS [Elevation], 
        [Extent9].[WindDirection] AS [WindDirection], 
        [Extent9].[startCoordinate] AS [startCoordinate], 
        [Extent9].[pinCoordinate] AS [pinCoordinate], 
        [Extent9].[targetCoordinate] AS [targetCoordinate], 
        [Extent9].[lineType] AS [lineType], 
        [Extent9].[Published] AS [Published], 
        [Extent9].[Unpublished] AS [Unpublished], 
        [Extent9].[Deleted] AS [Deleted], 
        [Extent9].[Book_Id] AS [Book_Id], 
        [Extent9].[ImageFile_Id] AS [ImageFile_Id], 
        [Extent9].[SituationCategory_Id] AS [SituationCategory_Id], 
        CAST(NULL AS int) AS [C2], 
        CAST(NULL AS int) AS [C3], 
        CAST(NULL AS int) AS [C4], 
        [Extent10].[Id] AS [Id2], 
        [Extent10].[SituationId] AS [SituationId], 
        [Extent10].[AttributesId] AS [AttributesId]
        FROM  [dbo].[Situations] AS [Extent9]
        INNER JOIN [dbo].[SituationAttributes] AS [Extent10] ON [Extent9].[Id] = [Extent10].[SituationId]
        WHERE (0 = [Extent9].[Deleted]) AND (1 = [Extent9].[Published]) AND ([Extent9].[SituationCategory_Id] = 1) AND (( NOT EXISTS (SELECT 
            1 AS [C1]
            FROM ( SELECT 
                36 AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable5]
            )  AS [Project16]
            WHERE ( NOT EXISTS (SELECT 
                1 AS [C1]
                FROM [dbo].[SituationAttributes] AS [Extent11]
                WHERE ([Extent9].[Id] = [Extent11].[SituationId]) AND ([Project16].[C1] = [Extent11].[AttributesId])
            )) OR (CASE WHEN ( EXISTS (SELECT 
                1 AS [C1]
                FROM [dbo].[SituationAttributes] AS [Extent12]
                WHERE ([Extent9].[Id] = [Extent12].[SituationId]) AND ([Project16].[C1] = [Extent12].[AttributesId])
            )) THEN cast(1 as bit) ELSE cast(0 as bit) END IS NULL)
        )) OR ( EXISTS (SELECT 
            1 AS [C1]
            FROM ( SELECT 
                36 AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable6]
            )  AS [Project20]
            WHERE  EXISTS (SELECT 
                1 AS [C1]
                FROM [dbo].[SituationAttributes] AS [Extent13]
                WHERE ([Extent9].[Id] = [Extent13].[SituationId]) AND ([Project20].[C1] = [Extent13].[AttributesId])
            )
        ))) AND (( NOT EXISTS (SELECT 
            1 AS [C1]
            FROM ( SELECT 
                1 AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable7]
            )  AS [Project23]
            WHERE ( NOT EXISTS (SELECT 
                1 AS [C1]
                FROM [dbo].[SituationHandicaps] AS [Extent14]
                WHERE ([Extent9].[Id] = [Extent14].[SituationId]) AND ([Project23].[C1] = [Extent14].[HandicapId])
            )) OR (CASE WHEN ( EXISTS (SELECT 
                1 AS [C1]
                FROM [dbo].[SituationHandicaps] AS [Extent15]
                WHERE ([Extent9].[Id] = [Extent15].[SituationId]) AND ([Project23].[C1] = [Extent15].[HandicapId])
            )) THEN cast(1 as bit) ELSE cast(0 as bit) END IS NULL)
        )) OR ( EXISTS (SELECT 
            1 AS [C1]
            FROM ( SELECT 
                1 AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable8]
            )  AS [Project27]
            WHERE  EXISTS (SELECT 
                1 AS [C1]
                FROM [dbo].[SituationHandicaps] AS [Extent16]
                WHERE ([Extent9].[Id] = [Extent16].[SituationId]) AND ([Project27].[C1] = [Extent16].[HandicapId])
            )
        )))) AS [UnionAll1]
    ORDER BY [UnionAll1].[Id1] ASC, [UnionAll1].[C1] ASC

It's working but it's not a good way to do this please suggest

what have you tried? Where is your attempt at writing the stored proc yourself? — pquest, Aug 14 '17 at 14:05
It is unclear what your issue is. Avoid asking us to do your work for you. Attempt something first. Please see the help https://stackoverflow.com/help/how-to-ask — Doug E Fresh, Aug 14 '17 at 14:08
This is my query for stored procedure `select * from [dbo].[Situations] as s left join [dbo].[SituationAttributes] as sa on s.Id=sa.SituationId left join [dbo].[SituationHandicaps] as sh on s.Id=sh.SituationId where published=1 and Deleted=0 and SituationCategory_Id=1` — Sumit Singh, Aug 14 '17 at 14:11

score 0 · Answer 1 · answered Aug 14 '17 at 15:25

0

For .All() you can use a NOT EXISTS subquery. EG this query returns Orders having All OrderLines with UnitPrice >= 2.

select * 
from Sales.Orders o
where not exists 
  (
    select *
    from Sales.OrderLines
    where OrderId = o.OrderId 
      and UnitPrice < 2.00
  )

answered Aug 14 '17 at 15:25

David Browne - Microsoft

80,331
6
39
67

can you rewrite the above for SQL – Sumit Singh Aug 14 '17 at 16:28
Probably, but why not just run it and capture the translated SQL as a starting point? – David Browne - Microsoft Aug 14 '17 at 16:31
I have editted the question and pus SQL of the Linq which is giving the right result but the query is too long – Sumit Singh Aug 14 '17 at 16:43
Why do you think it's "too long"? – David Browne - Microsoft Aug 14 '17 at 16:56
It's EntityFramwork generated SQL and difficult to maintanable – Sumit Singh Aug 14 '17 at 17:04
This is my SQL query where i am trying but don't know where to use not exist in place of All() function `select * from [dbo].[Situations] as s left outer join [dbo].[SituationHandicaps] as sh on s.Id=sh.SituationId left outer join [dbo].[SituationAttributes] as sa on s.Id=sa.SituationId where published=1 and Deleted=0 and SituationCategory_Id=1 ` – Sumit Singh Aug 14 '17 at 17:06

score 0 · Answer 2 · answered Aug 14 '17 at 22:19

The All() is completely redundant with the Any(). Remove the All and translate:

select *
from Situations x
join SituationAttributes sa on x.Id = sa.SituationId
join SituationHandicap sh on x.Id = sh.SituationId
where NOT Deleted AND Published AND SituationCategory_Id = C AND
EXISTS (SELECT 1 FROM SituationAttributes sa2 WHERE sa2.AttributesId in A) AND
EXISTS (SELECT 1 FROM SituationHandicap sh2 WHERE sh2.HandicapId in H)

How to convert Linq query to SQL query

2 Answers2