none
EF 4 - Conditional mapping makes memory consumption to reach huge values RRS feed

  • Question

  • Hello,

    The problem that I'm experiencing is occuring in a more complex scenario, but I'm able to reproduce it with a very simple case.

    I have a model with two entities with a relation one to many

    in the Address I have soft delete implemented through the column Deleted.

    In my code I want to retrieve all the people that live in a set of addresses

    using (var ctx = new DemoEFEntities())
    {
    var l = ctx.Person.Include("Address").Where(x =>
    (x.Address.Name == "317 Oak Blvd." && x.Address.Number == 926) ||
    (x.Address.Name == "891 White Milton Drive" && x.Address.Number == 497));
    
    textBox1.Text = l.ToList().Count.ToString();
    }

    in the code block I only present two restrictions, but they are 100, just like those two.

    When this is executed the memory of the process reaches ~680MB.

    Using SQL Server Management Studio I can see that the server is sending ~7KB of information!!

    The query that reaches SQL Server is full of LEFT OUTER JOINS

    SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[FullName] AS [FullName], 
    [Extent1].[AddressId] AS [AddressId], 
    [Extent202].[Id] AS [Id1], 
    [Extent202].[Name] AS [Name], 
    [Extent202].[Number] AS [Number]
    FROM                                                                                                                                                                                                        [dbo].[Person] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Address] AS [Extent2] ON ([Extent2].[Deleted] = 0) AND ([Extent1].[AddressId] = [Extent2].[Id])
    LEFT OUTER JOIN [dbo].[Address] AS [Extent3] ON ([Extent3].[Deleted] = 0) AND ([Extent1].[AddressId] = [Extent3].[Id])
    LEFT OUTER JOIN [dbo].[Address] AS [Extent4] ON ([Extent4].[Deleted] = 0) AND ([Extent1].[AddressId] = [Extent4].[Id])
    LEFT OUTER JOIN [dbo].[Address] AS [Extent5] ON ([Extent5].[Deleted] = 0) AND ([Extent1].[AddressId] = [Extent5].[Id])
    LEFT OUTER JOIN [dbo].[Address] AS [Extent6] ON ([Extent6].[Deleted] = 0) AND ([Extent1].[AddressId] = [Extent6].[Id])
    ...
    WHERE ((N'317 Oak Blvd.' = [Extent2].[Name]) AND (926 = [Extent3].[Number])) 
    ...

    If I remove the conditional mapping all works well.

    Why does EF needs to generate so many information? This is a EF known bug?

    Thanks in advanced.

    Marco

    Wednesday, June 20, 2012 3:44 PM

Answers

  • I’ve submitted the problem to Microsoft Support and the answer …:

    “The current design of EF when using conditional mapping leads to huge memory usage, with no configuration options which can change the behavior.

    A workaround for the issue is to remove the conditional mapping and explicitly map the Deleted column to a property. This can then be included in the LINQ query to filter out deleted entities. Often this can be moved into a Repository or similar data layer abstraction. It also makes it possible to mark entities as deleted without needing to use a stored procedure or other end-run around the conditional mapping.”

    Unfortunately is refactoring time for me.

    • Marked as answer by Marco Carreira Wednesday, July 11, 2012 10:01 PM
    Wednesday, July 11, 2012 10:01 PM

All replies

  • Hi Marco carreira,

    Welcome to MSDN Forum

    EF team are doing very hard on improving T-SQL generated, and SQL generation will be much better in .NET4.5. Please refer links below.

    Microsoft Boosts Performance in Entity Framework 5.0

    Entity Framework Developers Clamor for Better SQL Generation

    Improved SQL Generation

    Any update about this issue? If you need further help, please feel free to let me know, I will be more than happy to be of assistance. :)

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, June 21, 2012 7:28 AM
    Moderator
  • Hi Allen,

    For the time we won't be able to upgrade EF. So I need to address this issue.

    But I did a test with EF4.3 and the same problem occurred.

    I'm still without a solution.

    Best regards,

    Marco

    Monday, June 25, 2012 10:30 AM
  • Hi Marco Carreira,

    Could you please clarify why you delete the 'Deleted' column? Commonly, we do that when we want to achieve a TPH mapping. I don't find any mapping information in your models. If you only want to use it to check whether the address is valid, just let it as a normal property is OK.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, June 25, 2012 10:44 AM
    Moderator
  • Hi Allen,

    I remove it because I'm using the conditional mapping to retrieve only the records that are not deleted.

    When I add the condition I have to remove the mapping between the database column and the property and remove the property to the compilation to be successful.

    I think this is the best approach, isn't it?

    Best regards

    Monday, June 25, 2012 4:49 PM
  • Hi Marco Carreira,

    I think you can only let it as a flag column, define the type as bool. When you want to retrieve records which are not deleted, just add a where statement to filter.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, June 26, 2012 3:28 AM
    Moderator
  • Hi Allen,

    First let me thank you for your support.

    I think we are getting off the problem. I don't see any error with the current approach but for queries with many restrictions the memory raises to values that break the application.

    In reference to your last suggestion, we implemented a conditional mapping to ensure that no one and nowhere the deleted filter is forgotten.

    Best regards.


    Tuesday, June 26, 2012 9:03 AM
  • I’ve submitted the problem to Microsoft Support and the answer …:

    “The current design of EF when using conditional mapping leads to huge memory usage, with no configuration options which can change the behavior.

    A workaround for the issue is to remove the conditional mapping and explicitly map the Deleted column to a property. This can then be included in the LINQ query to filter out deleted entities. Often this can be moved into a Repository or similar data layer abstraction. It also makes it possible to mark entities as deleted without needing to use a stored procedure or other end-run around the conditional mapping.”

    Unfortunately is refactoring time for me.

    • Marked as answer by Marco Carreira Wednesday, July 11, 2012 10:01 PM
    Wednesday, July 11, 2012 10:01 PM