且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

在多对多实体框架关系中保留外键列表

更新时间:2023-02-13 15:13:53

很遗憾,似乎没有办法以我想要的方式映射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