在多对多实体框架关系中保留外键列表 [英] Keep list of foreign keys in many-to-many Entity Framework relationship

问题描述

在我的代码优先的Entity Framework模型中，我与多对多关系。假设我们有两个表，公司和文章，两者之间有这种关系。我的简化代码模型如下所示：

I have a many-to-many relationship in my code-first Entity Framework model. Imagine we have two tables, "Company" and "Article", that have such relationship in between. My simplified code model looks like the following:

public class Article
{
    public int Id { get; set; }
    public string Text { get; set; }
    public virtual ICollection<Company> Companies { get; set; }
}

public class Company
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Article> Articles { get; set; }
}

使用流畅的映射，我创建了多对多关系：

Using fluent mapping, I create many-to-many relationship:

modelBuilder.Entity<Company>().HasMany(c => c.Articles).WithMany(a => a.Companies);

这很好，EF创建了中间表。但是，按照我的逻辑，与每个实体一起拥有外键集合将非常好。这意味着我想将以下属性添加到相应的模型类中：

This works fine, EF creates intermediate table. But, according to my logic, it would be very nice to have a collection of foreign keys together with each entity. It means I would like to add the following property to corresponding model classes:

public virtual ICollection<int> ArticlesId { get; set; } // to Company
public virtual ICollection<int> CompaniesId { get; set; } // to Article

我知道一种解决方法是在EF中手动创建中间表的模型在每个呼叫中​​选择适当的ID，但也许EF映射可以提供更便捷的方式来进行此类操作？预先感谢您提供任何提示。

I know that one workaround solution is to create intermediate table's model in EF and manually select appropriate IDs in every call, but maybe EF mapping can provide more convenient way to do such operation? Thank you in advance for any tips.

推荐答案

很遗憾，似乎没有办法以我想要的方式映射ID。但是，这里有三种解决方法，用于实现对所需实体密钥的检索。

It seems that unfortunately there is no way to map IDs in the way I want. However, here are three workarounds of how to implement retrieval of required entity keys.

First 解决方案，由Ben Reich建议。

实现仅获取属性，该属性将仅返回链接实体的ID。

First solution, suggested by Ben Reich.
Implement get-only property, that will return only IDs of linked entities.

public class Company
{
    public virtual ICollection<Article> Articles { get; set; }

    public IEnumerable<int> ArticlesIds
    {
        get { return Articles.Select(a => a.Id); }
    }
}

使用起来似乎很方便，它有一个缺点-整个实体将从数据库中读取，以便接收唯一的ID。这是从SQL事件探查器发出的此类调用的日志：

It seems to be convient for using, however, it has a disadvantage - whole entity will be read from database in order to receive the only ID. Here is log of such call from SQL Profiler:

exec sp_executesql N'SELECT 
[Extent2].[Id] AS [Id], 
[Extent2].[Header] AS [Header], 
[Extent2].[Description] AS [Description], 
[Extent2].[Text] AS [Text], 
[Extent2].[CreationDate] AS [CreationDate], 
[Extent2].[AccountId] AS [AccountId], 
[Extent2].[ImageSetId] AS [ImageSetId]
FROM  [dbo].[CompanyArticles] AS [Extent1]
INNER JOIN [dbo].[Articles] AS [Extent2] ON [Extent1].[Article_Id] = [Extent2].[Id]
WHERE [Extent1].[Company_Id] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

第二解决方案。

使用相同的模型，在读取实体后分别读取ID。

Second solution.
Using the same model, read IDs separately after reading of the entity.

var ids = db.Set<Article>().Where(a => a.Companies.Select(c => c.Id).Contains(f.Id)).ToList();

此方法与前一种方法完全相同，将获取整个实体集。

This approach works quite the same as the previous one, whole entity set will be fetched.

exec sp_executesql N'SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Header] AS [Header], 
[Extent1].[Description] AS [Description], 
[Extent1].[Text] AS [Text], 
[Extent1].[CreationDate] AS [CreationDate], 
[Extent1].[AccountId] AS [AccountId], 
[Extent1].[ImageSetId] AS [ImageSetId]
FROM [dbo].[Articles] AS [Extent1]
WHERE  EXISTS (SELECT 
    1 AS [C1]
    FROM [dbo].[ArticleCompanies] AS [Extent2]
    WHERE ([Extent1].[Id] = [Extent2].[Article_Id]) AND ([Extent2].[Company_Id] = @p__linq__0)
)',N'@p__linq__0 int',@p__linq__0=1

第三解决方案。从我的角度来看，最合适。

为中间表创建实体类。

Third solution. The most appropriate, from my point of view.
Create entity class for your intermediate table.

public class ArticleCompany
{
    public int CompanyId { get; set; }
    public int ArticleId { get; set; }

    public virtual Company Company { get; set; }
    public virtual Article Article { get; set; }
}

将两个实体与此实体映射为1-to-m关系。不要忘记映射新实体本身。

Map both entities with this entity as 1-to-m relationship. Don't forget to map the new entity itself.

modelBuilder.Entity<Article>().HasMany(a => a.ArticlesCompanies).WithRequired(ac => ac.Article).HasForeignKey(ac => ac.ArticleId);
modelBuilder.Entity<Company>().HasMany(c => c.ArticlesCompanies).WithRequired(ac => ac.Company).HasForeignKey(ac => ac.CompanyId);

modelBuilder.Entity<ArticleCompany>().ToTable("ArticlesCompanies");
modelBuilder.Entity<ArticleCompany>().HasKey(ac => new { ac.ArticleId, ac.CompanyId });

然后，在获取实体后，使用中间表来获取相关的ID：

Then, after fetching the entity, use intermediate table in order to fetch related IDs:

var ids = db.Set<ArticleCompany>().Where(ca => ca.CompanyId == companyEntity.Id).Select(ca => ca.ArticleId);

相应的SQL日志（仅从数据库中获取ID）：

Corresponding SQL log (only IDs are fetched from database):

exec sp_executesql N'SELECT 
[Extent1].[ArticleId] AS [ArticleId]
FROM [dbo].[ArticlesCompanies] AS [Extent1]
WHERE [Extent1].[CompanyId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=1

这篇关于在多对多实体框架关系中保留外键列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！