locked
Entity Framework produces unexpected SQL when using .Any() RRS feed

  • Question

  • I was running a trace on SQL server to see what queries are produced by my application that uses EF, and found that the following C# code

    (
     from x in db.PostedItems
     where x.UserID == 1
     select x
    ).Any()

    produces the following SQL:

    SELECT
    CASE WHEN ( EXISTS (SELECT
     1 AS [C1]
     FROM [dbo].[PostedItem] AS [Extent1]
     WHERE 1 = [Extent1].[UserID]
    )) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT
     1 AS [C1]
     FROM [dbo].[PostedItem] AS [Extent2]
     WHERE 1 = [Extent2].[UserID]
    )) THEN cast(0 as bit) END AS [C1]
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]

    Quite surprised, I connected LINQPad to the database and executed the same C# expression - the SQL produced by LINQPad was:

    -- Region Parameters
    DECLARE @p0 Int = 1
    -- EndRegion
    SELECT
        (CASE
            WHEN EXISTS(
                SELECT NULL AS [EMPTY]
                FROM [PostedItem] AS [t0]
                WHERE [t0].[UserID] = @p0
                ) THEN 1
            ELSE 0
         END) AS [value]

    That's what I was expecting from EF!

    Just to make sure, I connected LINQPad to my exe file and its app.config, and executed the original expression again - the result is the same as what I got from the SQL trace of the application (as expected).

    Now questions:

    1. Is it possible to make LINQPad to produce the same SQL as EF will produce, otherwise I cannot really trust LINQPad.

    2. Why does EF produce such an unoptimised SQL? Logically it's correct, but it queries for the same information twice.

    3. Statement produced by EF has a concurrency issue. If the table PostedItem did not have any records with UserID = 1 during the first select, but did have such records during the second select, the whole statement will return NULL, while I presume EF always expects either 1 or 0. I presume it'll result in an error?

    Thanks,
    Andrew

    • Moved by Mike Feng Monday, February 11, 2013 9:52 AM
    Saturday, February 9, 2013 10:01 AM

Answers

  • Hi Andrew;

    To your statement in your last post, "That's the exact article I used to connect LinqPad to my own assembly", but in your previous post you stated, "how can I make LinqPad use EF (rather than Linq to SQL) ", so I provided the link.

    So we are back to the original question you posted, "Is it possible to make LINQPad to produce the same SQL as EF will produce", Yes, but only if you configure LinqPad to use your executable which contains the EF model otherwise NO you CAN NOT. Using Linq to SQL configuration in LinqPad will NOT produce the same SQL as a Linq to EF. This is how LinqPad was developed and supports Linq.

    To your question, "Why can't I point LinqPad directly to the same database (not my assembly!) and have it generate somewhere behind the scenes exactly the same data context and entities using EF,", because the two technologies do not produce the exact same results. It is like a VB.Net programmer saying I will use the C# compiler to compile my VB code and expect the results from the C# compiler to be the same, if you try this you will have multiple syntax errors.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Andrew-72 Wednesday, February 13, 2013 4:34 AM
    Wednesday, February 13, 2013 4:08 AM

All replies

  • Andrew,

    Did you know that there is an active forum where your question is done?

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/threads


    Success
    Cor

    Saturday, February 9, 2013 1:15 PM
  • Thanks Cor, can you point me to a particular question?

    Saturday, February 9, 2013 1:22 PM
  • Hi  Andrew,

    Welcome to the MSDN Forum.

    >>Is it possible to make LINQPad to produce the same SQL as EF will produce, otherwise I cannot really trust LINQPad.

    As far as I know, LINQPad is a third party tool: http://www.linqpad.net/  Here I cannot tell you LinqPad is reliable or not. I Just want to tell another simple thing, you know 1+1 = 2 since we are pupils, and also 1+1 = 0.5+0.5+0.5+0.5 = 2. So you cannot say the way I got the answer by 0.5+0.5+0.5+0.5 is wrong.

    >>2. Why does EF produce such an unoptimised SQL? Logically it's correct, but it queries for the same information twice.

    I am afraid this is by design. You can report this to Microsoft: http://connect.microsoft.com/VisualStudio 

    >>Statement produced by EF has a concurrency issue.

    Generally, a concurrency issue should be handled by developers, and here is a similar thread: http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/5219035b-aaa9-4917-b2f2-852b5d8449ea/  

    I hope this will be helpful.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, February 11, 2013 9:52 AM
  • Hi ;

    To your question, "Is it possible to make LINQPad to produce the same SQL as EF will produce, otherwise I cannot really trust LINQPad.", Did you configure LinqPad to reference the entity model and did NOT create a Linq to SQL connection, this would cause the issue you are seeing.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, February 11, 2013 2:36 PM
  • Hi Fernando,

    > Did you configure LinqPad to reference the entity model and did
    > NOT create a Linq to SQL connection

    If I configure LinqPad to use data context from my own assembly (which uses EF 5.0) then it produces the same SQL as my assembly does. If I configure LinqPad to build data context automatically using Linq to SQL, I get different SQL.

    The question is how I can get LinqPad to produce the same SQL as my assembly will be producing, while I'm still developing the assembly and it does not compile yet.

    Thank you,
    Andrew

    Monday, February 11, 2013 3:01 PM
  • Hi Andrew;

    To your question, "The question is how I can get LinqPad to produce the same SQL as my assembly will be producing, while I'm still developing the assembly and it does not compile yet.", In order to use LinqPad with Entity Framework you will need to have a database in the server and an executable for linqPad to work against. Without those two pieces you cant get what you are looking for, LinqPad is a tool made to develop and test queries and is NOT intended to create a database and model where one does not exist.

    To your statement, "If I configure LinqPad to use data context from my own assembly (whichuses EF 5.0) then it produces the same SQL as my assembly does.", And so LinqPad does produce the same query as seen in SQL server.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Monday, February 11, 2013 3:28 PM
  • Hi Fernando,

    So there's no surprise that LinqPad produces the same SQL as my assembly does, when I point it to the data context in my assembly. What I do not understand is why SQL is different when I get LinqPad to produce SQL by pointing it to the existing database and letting it build data context automatically. In my example

    (
      from x in db.PostedItems
      where x.UserID == 1
      select x
    ).Any()

    PostedItems is the entity representing the PostedItem table in the database. This entity was created by EF from the database (it's a database-first project), so why would it behave differently from the entity auto-generated by LinqPad when I point it to the same database?

    Thanks,
    Andrew


    • Edited by Andrew-72 Tuesday, February 12, 2013 2:25 AM
    Tuesday, February 12, 2013 12:29 AM
  • To your question, "What I do not understand is why SQL is different when I get LinqPad to produce SQL by pointing it to the existing database and letting it build data context automatically.", Linq to SQL was the first of the two to be released and was worked on by one product team and implemented it one way. Entity Framework was then released and was worked on by a different team and had different challenges to over come. Therefore we have two different technologies implemented differently. In order for LinqPad to give the same results as writing the query in Visual Studio you need to use the same technology otherwise you will not get the same results.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Tuesday, February 12, 2013 4:55 AM
  • Thanks Fernando, but this brings us back to my original question - how can I make LinqPad use EF (rather than Linq to SQL) to produce SQL? If I can point it to my my assembly which contains the entities, and to my app.config which contains the DB connection string, why can't I instead point it to EntityFramework.dll and to the DB?

    Regards,
    Andrew

    Tuesday, February 12, 2013 8:09 AM
  •   

    I am sorry Andrew but I was under the understanding that you were able to configure LinqPad to connect to your Entity Framework. Please see this link, Using LINQPad with Entity Framework, to see how to connect Entity Framework in LinqPad.

      

     

    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Tuesday, February 12, 2013 2:54 PM
  • Hi Fernando,

    That's the exact article I used to connect LinqPad to my own assembly. What I'm trying to ask is this:

    • I can point my own assembly which references EF to the database, and Visual Studio will generate edmx file for me. Behind the scenes it will create data context and etities. Now I can query entities from my assembly using LINQ, with EF producing SQL queries for me.
    • Why can't I point LinqPad directly to the same database (not my assembly!) and have it generate somewhere behind the scenes exactly the same data context and entities using EF, just like Visual Studio did for my assembly. Then LinqPad would allow me to query these entities, and it would use EntityFramework.dll to produce SQL. Then I can use LinPad to see what queries my assembly will be sending to the database even before my assembly even exists, as long as I have the database ready.

    Hope this makes sense.

    Thanks,
    Andrew

    Tuesday, February 12, 2013 11:15 PM
  • Hi Andrew;

    To your statement in your last post, "That's the exact article I used to connect LinqPad to my own assembly", but in your previous post you stated, "how can I make LinqPad use EF (rather than Linq to SQL) ", so I provided the link.

    So we are back to the original question you posted, "Is it possible to make LINQPad to produce the same SQL as EF will produce", Yes, but only if you configure LinqPad to use your executable which contains the EF model otherwise NO you CAN NOT. Using Linq to SQL configuration in LinqPad will NOT produce the same SQL as a Linq to EF. This is how LinqPad was developed and supports Linq.

    To your question, "Why can't I point LinqPad directly to the same database (not my assembly!) and have it generate somewhere behind the scenes exactly the same data context and entities using EF,", because the two technologies do not produce the exact same results. It is like a VB.Net programmer saying I will use the C# compiler to compile my VB code and expect the results from the C# compiler to be the same, if you try this you will have multiple syntax errors.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Andrew-72 Wednesday, February 13, 2013 4:34 AM
    Wednesday, February 13, 2013 4:08 AM