0

This SQL was generated when you pass a parameter from a variable

SELECT 
    ..
    FROM ( SELECT TOP (1) 
        ..
        FROM   (SELECT ..
            FROM  [dbo].[PurchaseOrders] AS [Extent1]
            INNER JOIN [dbo].[Parameters] AS [Extent2] ON [Extent1].[StatusId] = [Extent2].[Id]
            WHERE [Extent1].[Deleted] <> 1 ) AS [Filter1]
        INNER JOIN [dbo].[Suppliers] AS [Extent3] ON [Filter1].[SupplierId] = [Extent3].[Id]
        WHERE ([Filter1].[Id1] = @p__linq__0) AND ([Filter1].[LocationId] = @p__linq__1)
    )  AS [Limit1]

While if you pass a constant the generated SQL is much simpler

SELECT 
    ..
    FROM ( SELECT TOP (1) 
        ..
        FROM   [dbo].[PurchaseOrders] AS [Extent1]
        INNER JOIN [dbo].[Parameters] AS [Extent2] ON [Extent1].[StatusId] = [Extent2].[Id]
        INNER JOIN [dbo].[Suppliers] AS [Extent3] ON [Extent1].[SupplierId] = [Extent3].[Id]
        WHERE ([Extent1].[Deleted] <> 1) AND (2 = [Extent1].[LocationId]) AND ([Extent1].[Id] = @p__linq__0)
    )  AS [Limit1]

so, how can we tell EF to generate 2nd SQL without using constant?

Dale K
  • 25,246
  • 15
  • 42
  • 71
h3n
  • 5,142
  • 9
  • 46
  • 76
  • 4
    You want a parameter, not a constant! If you use a constant you get a different query plan for every different constant value. When you use a parameter you get to reuse the same query plan multiple times. I highly recommend you take the time to understand how all this works under the hood before you try and make changes just for the sake of making it look pretty. These things (and your previous question) happen for a good reason. You don't want to mess with it without having a very advanced understanding of the mechanisms involved. – Dale K May 08 '22 at 06:28
  • In deployment time it easier to read the query, in our project we use predicate builder and can decided to use constant or parameter, so we add appSeting that tell to builder how to create the query, in prod we use params in deploy time const. – s-s May 08 '22 at 16:52
  • @DaleK, the subqueries concerned me when I saw the generated SQL. I thought that SQL Server executes the subqueries first without filtering it since the filter is outside. – h3n May 09 '22 at 06:27

0 Answers0