Keep list of foreign keys in many-to-many Entity F

问题:

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);

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

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.

回答1:

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 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); }
    }
}

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

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; }
}

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 });

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);

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

回答2:

Would some simple getters suffice?

public IEnumerable<int> ArticlesId { get { return this.Articles.Select(a => a.Id); } }
public IEnumerable<int> CompaniesId { get { return this.Companies.Select(c => c.Id); } }

This would cover many use cases. Of course since this isn't a virtual association property, you lose some of the flexibility of using these properties in actual Linq-to-Entities queries.