locked
Unnecesary SQL statements RRS feed

  • Question

  • Think in this example for the tipical tables of users and roles, with a intermediary table where one user can have serveral roles. If I ask to EF one user it gives me also his roles with all his properties, and the users of each role.

    When it should be a very easy SQL sentence for getting the properties of an user it has been converted to several unnecesary statements.

    Is there any way to avoid this behaviour?

    Thanks a lot!

    Monday, March 11, 2013 10:51 AM

Answers

  • Knowing the LINQ query you are using and the SQL statement which is generated would help...

    Ah when reading again I believe it could be just the "classic" lazy loading issue ? LINQ sends a query when your try to enumerate entities so if you are fetching only one user and using the debugger to inspect its roles it will trigger a query to get the roles for this user and if you inspect the users for one of these role, it will also trigger a query to get all corresponding users etc... so a beginner may think that EF always loads all those data...

    It happens just because those lists are enumerated. It doesn't actually happen when you don't inspect those collections using the debugger (or if you don't enumerate them from your code)...

    My understanding is finallly not that you are actually seeing a single complicated SQL statement but that you are confused as if you inspect one user you always find its related data while you don't have run any explicit query (if you do a server side SQL trace, you'll see your debugger actions are turned into SQL statements to fill client side collections as you are asking for their content).


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".





    Monday, March 11, 2013 1:09 PM

All replies

  • Hi,

    You may want to give some more details. Keep in mind that you'll always do a better job than EF (as EF has to be general while you can always optimize as needed for each case you are dealing with). To start with do you see a significant performance slowdown caused by this statement. If yes your best is likely to report the issue at http://entityframework.codeplex.com/workitem/list/basic


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Monday, March 11, 2013 10:57 AM
  • Hi Patrice, perhaps it is due to I'm new with EF. What kind of details do you need? Thanks a lot!
    Monday, March 11, 2013 12:17 PM
  • Knowing the LINQ query you are using and the SQL statement which is generated would help...

    Ah when reading again I believe it could be just the "classic" lazy loading issue ? LINQ sends a query when your try to enumerate entities so if you are fetching only one user and using the debugger to inspect its roles it will trigger a query to get the roles for this user and if you inspect the users for one of these role, it will also trigger a query to get all corresponding users etc... so a beginner may think that EF always loads all those data...

    It happens just because those lists are enumerated. It doesn't actually happen when you don't inspect those collections using the debugger (or if you don't enumerate them from your code)...

    My understanding is finallly not that you are actually seeing a single complicated SQL statement but that you are confused as if you inspect one user you always find its related data while you don't have run any explicit query (if you do a server side SQL trace, you'll see your debugger actions are turned into SQL statements to fill client side collections as you are asking for their content).


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".





    Monday, March 11, 2013 1:09 PM
  • You are completly right, I have just see it with SQL Profiler, and the roles were consulted because I was debugging it. Thanks a lot!
    Monday, March 11, 2013 1:27 PM