none
EF4 adding to collection causes SQL performance issue RRS feed

  • Question

  • Hi,

    I have an issue with what looks like inefficient SQL being produced by EF.  To explain have a simplified example with the following 3 entities:

    Role - has fields ID (PK) and Description and navigation collection property OrganisationRoles

    Organisation - has fields ID (PK) and Code and navigation collection property OrganisationRoles

    OrganisationRole - has fields ID (PK), OrganisationID (FK), RoleID (FK) and StartDate and Navigation properties Role and Organisation

    All the IDs are self incrementing identity fields on the database and are the primary key for each table.

    We have the situation whereby when adding a new organisation we also need to assign it a default role.  Hence the code looks like this:

                ObjectContext context = new ObjectContext(connectionString); 
                context.ContextOptions.LazyLoadingEnabled = true;  //Off by default so turn on 
     
                Organisation org = new Organisation() 
                { 
                     Code="XYZ" 
                }; 
     
                ObjectSet<Role> roles = context.CreateObjectSet<Role>(); 
                Role role = roles.Single(r => r.ID == 1); 
                OrganisationRole orgRole = new OrganisationRole() 
                { 
                     Role = role, 
                     RoleID = role.ID, 
                     StartDate = DateTime.Today 
                }; 
     
                org.OrganisationRoles.Add(orgRole); 
                ObjectSet<Organisation> orgs = context.CreateObjectSet<Organisation>(); 
                orgs.AddObject(org);//This line here is where issue occurs 
                context.SaveChanges();

    When running a SQL profiler on this code at the point it hits the line orgs.AddObject(org) I get the following SQL issued:

    exec sp_executesql N'SELECT  
    [Extent1].[ID] AS [ID],  
    [Extent1].[OrganisationID] AS [OrganisationID],  
    [Extent1].[RoleID] AS [RoleID],  
    [Extent1].[StartDate] AS [StartDate] 
    FROM [dbo].[OrganisationRole] AS [Extent1] 
    WHERE [Extent1].[RoleID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

    This causes us an issue in the real system as a given RoleID may have tens of thousands of associated OrganiationRole records.  Does anyone know why EF is issuing this "loose" SQL and how to stop it? 

    Interestingly enough if I set the LazyLoadingEnabled flag to false it doesn't occur but in the real system this isn't an option as we're actually going through a Repository and UnitOfWork pattern and the rest of the applicarion already has an assumption that lazy loading will be enabled when the Unit of work creates the ObjectContext for the repositories.

    Any help appreciated.

    Also posted on ASP.NET EF forum

    Tuesday, November 22, 2011 10:26 PM

Answers

  • Hi,

    I created a test database/project and using the code you provided I didn't see the same query being performed. The only things showing up in the profiler were 1. Retrieve the Role entity, 2. Insert Organization, 3. Insert OrganizationRole.

    From the looks of the extraneous query it's definitely loading the role.OrganizationRoles navigation property, the question is why it's doing it at that point. It would appear that some sort of change tracking is being performed in which it is executing role.OrganizationRoles.Add(orgRole).

    Is there a specific reason that you need to retrieve the Role from the database which you've excluded from the sample? Avoiding instantiating the object would likely keep it from performing the query. Given the example you could easily exclude instantiating the role object entirely. If the Role is needed later then you could try retrieve it after saving.

    Regards,

    Tyler

    Wednesday, November 23, 2011 4:36 PM
  • Hi,

    I have never seen behaviour like that earlier, so I had to do as Tyler_A did, create a test project.

    And my experience is the same as his, it doesn't give any query in the profiler for the orgs.AddObject(org) call with the exact same code as you are presenting her.

    But, I have a couple of question to you:

    1. What version of .NET and Entity Framework are you using?

    2. Why are you using the general ObjectContext class and not autogenterated code for your entities?

     


    --Rune

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful" if the post helped you to a solution of your problem.
    Wednesday, November 23, 2011 5:28 PM

All replies

  • Hi,

    I created a test database/project and using the code you provided I didn't see the same query being performed. The only things showing up in the profiler were 1. Retrieve the Role entity, 2. Insert Organization, 3. Insert OrganizationRole.

    From the looks of the extraneous query it's definitely loading the role.OrganizationRoles navigation property, the question is why it's doing it at that point. It would appear that some sort of change tracking is being performed in which it is executing role.OrganizationRoles.Add(orgRole).

    Is there a specific reason that you need to retrieve the Role from the database which you've excluded from the sample? Avoiding instantiating the object would likely keep it from performing the query. Given the example you could easily exclude instantiating the role object entirely. If the Role is needed later then you could try retrieve it after saving.

    Regards,

    Tyler

    Wednesday, November 23, 2011 4:36 PM
  • Hi,

    I have never seen behaviour like that earlier, so I had to do as Tyler_A did, create a test project.

    And my experience is the same as his, it doesn't give any query in the profiler for the orgs.AddObject(org) call with the exact same code as you are presenting her.

    But, I have a couple of question to you:

    1. What version of .NET and Entity Framework are you using?

    2. Why are you using the general ObjectContext class and not autogenterated code for your entities?

     


    --Rune

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful" if the post helped you to a solution of your problem.
    Wednesday, November 23, 2011 5:28 PM
  • Hi there, Idle Dog posted this problem on my behalf.  Sorry for the lateish reply as both Mr Dog and Me have been away from the office for a few days but just to say to both of you, Tyler and Rune, that appreciate your taking the time to try and replicate this issue very much.

    Taking Tyler's suggestion changed the example code to not instantiate the role object but just to directly set the RoleId property only of the organisation role i.e.

    rather than

    ObjectSet<Role> roles = context.CreateObjectSet<Role>();
    Role role = roles.Single(r => r.ID == 1);
                
    OrganisationRole orgRole = new OrganisationRole()
    {
        Role = role,
        RoleID = role.ID,
        StartDate = DateTime.Today,
        Organisation = org
    };
    
    

    Have changed to :

    OrganisationRole orgRole = new OrganisationRole()
    {
        RoleID = 1,
        StartDate = DateTime.Today,
        Organisation = org
    };
    

    and lo and behold don't get the inefficient SQL being issued, in fact we don't get any SQL until we actually call SaveChanges.  Will need to look again at the real application to check to see if there is any reason why we couldn't take the same approach there, but at the moment it seems to work so thank you Tyler for this suggestion.

    As noted in the original post with the original code if we turned off lazy loading we also didn't get the inefficient SQL issued so again I think Tyler is right in that the issue is related to change tracking (having lazy load enabled caused it to load all the related navigation collectionsso it can check what's been changed, maybe ??)

    Rune, in answer to your query we're using POCO classes in the application so have the EF code genaration strategy set to None.  We use ObjectContext to instantiate repositories for those POCOs within a Unit of Work pattern.  We are using V 4.0.30319 SP1 Rel of the .NET Framework and EF V4.  This is the first project we've used EF in anger on so I'm afraid I don't totally understand your point 2, although as I've mentioned we're using POCO classes which contain certain simple extension properties over and above the standard EF mapped properties.

    Once again thanks for both of yours help, it is appreciated.

    regards

    Tuesday, November 29, 2011 2:13 PM
  • Hi again,

    Well, I see what you are doing then and why you are doing it.

    However, wouldn't it be better to just use the POCO generation template to generate your POCO objects? This will generate strong typed classes all over for you, so you don't have to do calls like ObjectSet<Role> roles = context.CreateObjectSet<Role>(); ? If you are using the POCO Entity generator you will get a context class defined to your model with objectset properties?

    It would make the code easier to read and maintain.


    --Rune

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful" if the post helped you to a solution of your problem.

    Tuesday, November 29, 2011 6:01 PM