如何在Entity Framework TPT继承中避免多态行为，以便有效查询基本类型 [英] How to avoid polymorphic behaviour in Entity Framework TPT Inheritance in order to query base type efficiently

问题描述

概述

我首先使用带有流畅接口的实体框架4.3代码来设置我的DbContext。我有一个基本的 Item 类，该类具有其他继承该类型的类型，例如 Event ， BlogPost ， ForumThread ， WikiPage 等。

I am using entity framework 4.3 code first with the fluent interface to setup my DbContext. I have a base Item class with other types that inherit this such as an Event, BlogPost, ForumThread, WikiPage and so on.

这些继承的类型与我认为实体框架称为TPT继承的映射。当查询诸如事件或博客文章之类的单一类型时，此方法效果很好，但由于要实现EF提供的多态行为而需要进行联接，因此在尝试对所有类型进行查询时，会构建性能非常糟糕的非常复杂的查询。

These inherited types are mapped with what I think entity framework refers to as TPT inheritance. This works great when querying a single type like 'events' or 'blog posts' but constructs very complicated queries with horrible performance when trying to query across all types due to the joins required in order to achieve the polymorphic behaviour EF provides out of the box.

问题上下文

我想在其中建立全局搜索功能我只需要访问基本的 Item实体，而不需要继承的实例。我希望能够通过名称，标签等在整个基础项目类中进行查询。执行任何种类的LINQ查询，即使请求基本项类型仍然会导致多态行为，从而影响性能。

I am wanting to build a global search feature where I only need access to the base 'Item' entity and not the inherited instances. I'd like to be able to query across the base item class by name, tags and so on. Performing any sort of LINQ query, even when requesting the base item type still results in polymorphic behaviour which kills performance.

代码优先模型

public class Item
{
    public int Id { get; set; }

    public string Name { get; set; }

    public string Body { get; set; }

    public DateTime Created { get; set; }

    public int? CreatedBy { get; set; }

    public int? LastModifiedBy { get; set; }

    public DateTime? LastModified { get; set; }

    public virtual User Author { get; set; }

    public bool IsDeleted { get; set; }

    public string ImageUri { get; set; }

    public virtual ICollection<Tag> Tags { get; set; }
}

public class Event : Item
{
    // Additional properties
}

public class BlogPost : Item
{
    // Additional properties
}

我会怎么做想要做的就是将另一个POCO映射到同一基表，这样当我在其上构造查询时，它就不会涉及继承问题。 EF似乎并不喜欢这样。我目前没有手头的错误，但是我尝试进行简单映射失败。

What I would like to be able to do is map another POCO to the same base table so that when I construct queries on it, it doesn't involve the inheritence issues. EF doesn't seem to like this though. I don't have the error on hand at the moment but my attempts at a simple mapping failed.

替代解决方案？

• 我曾经考虑过要实现一个看起来类似于 Item表的 index表，并在每当有新记录时在其中插入一条记录项目类型已创建。但是，每当事件，博客文章数据发生更改等时，该索引数据也将需要更新。这会由于诸如标签之类的外键而变得更加复杂。每当说事件的标记发生更改时，我都必须确保这些更改也在匹配的索引表上同步。考虑所有不同的项目类型时，坦率地说，这将成为一场噩梦，似乎并不是一个非常优雅的解决方案。

• I had thought about implementing an 'index' table which would look similar to the 'Item' table and inserting a record into that whenever a new item type is created. But this index data would then also need to be updated whenever the event, blog post data changes etc. This is further complicated by foreign keys such as tags. Whenever tags on say an event are changed I would have to make sure these changes are synchronised on the matching index table too. When considering all the different item types, this would become a bit of a nightmare to manage and frankly, doesn't seem like a very elegant solution.

数据库触发器

我的首选解决方案是使用代码而不是数据库触发器/存储的proc。

My preferred solution here would be one that is in code and not database triggers / stored procs.

有没有一种方法来构造一个查询，以强制EF只返回基本类型，而不是导致连接过多和性能糟糕的多态类型？还是有其他解决办法？

Is there a way to construct a query to force EF to only return the base type instead of the polymorphic type which results in too many joins and horrible performance? Or is there some other clever way around this?

更新

更新后通过Nuget（针对.Net 4.0）到EntityFramework 5，我已经能够通过标签来查询项目，并投影到一个新的 SearchItem 中，这导致相当干净的SQL，而无需加入TPT类型。

After updating to EntityFramework 5 via Nuget (targeting .Net 4.0) I have been able to query items by their tags and project into a new SearchItem which results in fairly clean SQL without joins to the TPT types.

        var x = from item in repository.FindAll<Item>()
                where item.Tags.Any(t => t.Name == "test")
                select new SearchItem
                {
                    Id = item.Id,
                    Name = item.Name,
                    Body = item.Body,
                    Created = item.Created,
                    CreatedBy = item.CreatedBy,
                    IsDeleted = item.IsDeleted,
                    ImageUri = item.ImageUri,
                    MembershipEntityId = item.MembershipEntityId,
                    //Tags = (from t in item.Tags
                    //       select new Tag
                    //       {
                    //           Id = t.Id,
                    //           Name = t.Name,
                    //           MembershipEntityId = t.MembershipEntityId
                    //       })
                };

SQL

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
[Extent1].[Body] AS [Body], 
[Extent1].[Created] AS [Created], 
[Extent1].[CreatedBy] AS [CreatedBy], 
[Extent1].[IsDeleted] AS [IsDeleted], 
[Extent1].[ImageUri] AS [ImageUri], 
[Extent1].[MembershipEntityId] AS [MembershipEntityId]
FROM [dbo].[Item] AS [Extent1]
WHERE  EXISTS (SELECT 
1 AS [C1]
FROM  [dbo].[ItemTag] AS [Extent2]
INNER JOIN [dbo].[Tag] AS [Extent3] ON [Extent3].[Id] = [Extent2].[Tag_Id]
WHERE ([Extent1].[Id] = [Extent2].[Item_Id]) AND (N'test' = [Extent3].[Name])
)

这已经解决了我一半的问题，因为现在，我可以按标签搜索基本类型。但是，我希望能够使用新的投影返回标签。将注释掉的代码包括在内会导致EF无法翻译的查询。有解决方法吗？

This has solved half of my problem as I can now search across the base type by tag. I would however like to be able to return the tags with the new projection. Including that commented out bit of code results in a query that EF cannot translate though. Is there a workaround for this?

推荐答案

有没有办法构造强制EF的查询只返回
基本类型而不是多态类型会导致过多的
连接和可怕的性能？

Is there a way to construct a query to force EF to only return the base type instead of the polymorphic type which results in too many joins and horrible performance?

一般不行。您已经映射了继承关系，并且如果要返回 Item 的实例，EF必须始终返回正确的type =>，它需要这些连接。 EF还不允许多次映射同一张表，因此您不能在同一映射中将 Item 再次映射为另一个POCO。

Generally no. You have mapped inheritance and if you want to return instances of Item, EF must always return correct type => it needs those joins. EF also doesn't allow mapping the same table multiple times so you cannot have in the same mapping the Item mapped again as another POCO.

从理论上讲，您应该能够查询项目并将项目投影到您想要的非映射 POCO类，仅从基础属性中获取类。不幸的是，这在.NET 4.0中不起作用- EF仍执行联接。您可以在.NET 4.5和EF 5.0上尝试此操作，其中此问题应该解决。

In theory you should be able to query Items and project to your non mapped POCO class only properties you want from the base class. Unfortunately this didn't work in .NET 4.0 - EF still performed joins. You can try this with .NET 4.5 and EF 5.0 where this issue should be solved.

这篇关于如何在Entity Framework TPT继承中避免多态行为，以便有效查询基本类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！