EF Core (5) navigation property without DB relation

Question

I have an old database that I can't change now. In the database there are no relation amongst tables. Please see the below DB image. Here three tables which are defines many to many relation but no physically relation (foreign key). With EFCore DB first approach, Now I need to map EFCore models with navigator. I have created the model classes as below,

public class Company {
    [Key]
    [MaxLength(36)]
    [Column("COMPANY_ID")]
    public string CompanyId { get; set; }
    [Column("FULL_NAME")]
    public string FullName { get; set; }
    /*Others properties*/

    public virtual ICollection<TagCompany> TagCompanies { set; get; }
}

public class Tag {
    [Column("TAG_GROUP_ID")]
    public string GroupId { get; set; }

    [Column("TAG_ITEM_ID")]
    public string Id { get; set; }

    [Column("TAG_ITEM_NAME")]
    public string Name { get; set; }

    [Column("TAG_ITEM_DESCRIPTION")]
    public string Description { get; set; }

    public virtual ICollection<TagCompany> TagCompanies { set; get; }
}

public class TagCompany {
    [Column("COMPANY_ID")]
    public string CompanyId { get; set; }

    [Column("TAG_ITEM_ID")]
    public string TagId { get; set; }

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

And the OnModelCreating methods as below,

            protected override void OnModelCreating(ModelBuilder modelBuilder) {

            modelBuilder.Entity<Company>().HasKey(o => o.CompanyId);
            modelBuilder.Entity<Tag>().HasKey(o => new { o.GroupId, o.Id });


            modelBuilder.Entity<TagCompany>().HasKey(o => new { o.CompanyId, o.TagId });
            modelBuilder.Entity<TagCompany>().HasOne(s => s.Tag).WithMany(s => s.TagCompanies).HasPrincipalKey(o => new { o.GroupId, o.Id });
            modelBuilder.Entity<TagCompany>().HasOne(s => s.Company).WithMany(s => s.TagCompanies).HasForeignKey(s => s.CompanyId);
            }

With above code I am facing below error,

Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'TagGroupId'. Invalid column name 'TagId1'. at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at

Any help is appreciate. Thanks in advance.

Answer 1

The problem is that if Tag.Id is not unique, you have a hidden one-to-many relationship between TagCompany and Tag, which requires collection navigation property and generally cannot be mapped to the single TagCompany.TagId FK.

You can fool EF that Tag.Id is unique by mapping it as alternate key, which then would allow you to map TagCompany.TagId as many-to-one FK by replacing

.HasPrincipalKey(o => new { o.GroupId, o.Id }

with

.HasPrincipalKey(o => o.Id)

But now some queries will return incorrect results in the Tag data contains duplicate Ids. For instance, here

var companies = db.Set<Company>()
    .Include(e => e.TagCompanies).ThenInclude(e => e.Tag)
    .ToList();

var includedTags = companies
    .SelectMany(e => e.TagCompanies).Select(e => e.Tag)
    .ToList();

var actualTags = db.Set<Company>()
    .SelectMany(e => e.TagCompanies).Select(e => e.Tag)
    .ToList();

actualTags is correct, and includedTags is not (contains less items).

So a better hack which seems to work with EFC 5 would be to configure the many-to-many relationship with the so called skip navigations. Here is the modified model (the essential are the two collection navigation properties):

public class Company
{
    [Key]
    [MaxLength(36)]
    [Column("COMPANY_ID")]
    public string Id { get; set; }
    [Column("FULL_NAME")]
    public string FullName { get; set; }
    /*Others properties*/

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

public class Tag
{
    [Column("TAG_GROUP_ID")]
    public string GroupId { get; set; }

    [Column("TAG_ITEM_ID")]
    public string Id { get; set; }

    [Column("TAG_ITEM_NAME")]
    public string Name { get; set; }

    [Column("TAG_ITEM_DESCRIPTION")]
    public string Description { get; set; }

    public virtual ICollection<Company> Companies { get; set; } // <--
}

public class TagCompany
{
    [Column("COMPANY_ID")]
    public string CompanyId { get; set; }

    [Column("TAG_ITEM_ID")]
    public string TagId { get; set; }

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

and the fluent configuration:

// composite PK
modelBuilder.Entity<Tag>().HasKey(e => new { e.GroupId, e.Id });

// M2M relationship and join entity configuration
modelBuilder.Entity<Company>()
    .HasMany(e => e.Tags)
    .WithMany(e => e.Companies)
    .UsingEntity<TagCompany>(
        j => j.HasOne(e => e.Tag).WithMany().HasForeignKey(e => e.TagId)
            .HasPrincipalKey(e => e.Id), // fake alternate key
        j => j.HasOne(e => e.Company).WithMany().HasForeignKey(e => e.CompanyId),
        j => j.HasKey(e => new { e.CompanyId, e.TagId }) // composite PK
    );

Now the same test as before

var companies = db.Set<Company>()
    .Include(e => e.Tags)
    .ToList();

var includedTags = companies
    .SelectMany(e => e.Tags)
    .ToList();

var actualTags = db.Set<Company>()
    .SelectMany(e => e.Tags)
    .ToList();

yield one and the same results.

---

Now, the last hack seems to work in EFC 5 (not tested with projections and other non entity returning LINQ queries), but might break in future EFC versions, so use it on your own risk. From the other side, there is no other way to map such db model, so...