CODEWITHSUNDEEP
entity-framework-coreef-core-3.1
James S.
James S.

Reputation: 150

Non-Standard Composite Relationship in EF Core

I have a 3rd party database I'm trying to integrate with EF Core and it uses strange composite columns instead of foreign keys. A relationship is defined by a "Parent_Table_Name" and "Parent_Id". They're not keys, just an nchar(20) that contains the table name and an int with the ID in that table respectively. Is there any way I can establish navigation properties between tables if they don't have foreign keys?

The provider is Microsoft SQL Server. Here are CREATE statements that describe the situation.

CREATE TABLE [ParentA] (Table_Name nchar(20), Id int)
CREATE TABLE [ParentB] (Table_Name nchar(20), Id int)
CREATE TABLE [ChildC] (Table_Name nchar(20), Id int, Parent_Table_Name nchar(20), Parent_Table_Id int)

Here is an image of some sample data given the above tables.

sample data of ParentA, ParentB, and ChildC

Upvotes: 0

Views: 99

Answers (1)

bricelam
bricelam

Reputation: 30425

These are known as a polymorphic associations (feature request #757). There is currently no first-class support for it, but you can work around it:

class ParentA
{
    public string Table_Name { get; set; }
    public int Id { get; set; }

    public ICollection<ChildC> ChildCs { get; set; }
}

class ParentB
{
    public string Table_Name { get; set; }
    public int Id { get; set; }

    public ICollection<ChildC> ChildCs { get; set; }
}

class ChildC
{
    public string Table_Name { get; set; }
    public int Id { get; set; }
    
    public int? ParentA_Id { get; set; }
    public ParentA ParentA { get; set; }
    
    public int? ParentB_Id { get; set; }
    public ParentB ParentB { get; set; }
}

var childrenWithParents = dbContext.ChildCs
    // TODO: You could also create a view in the database
    //       or use ToSqlQuery on modelBuilder
    .FromSql(@"
        SELECT Table_Name,
            Id,
            CASE
                WHEN Parent_Table_Name = 'ParentA'
                    THEN Parent_Table_Id
            END AS ParentA_Id,
            CASE
                WHEN Parent_Table_Name = 'ParentB'
                    THEN Parent_Table_Id
            END AS ParentB_Id
        FROM ChildC")
    .Include(c => c.ParentA)
    .Include(c => c.ParentB)
    .ToList();

Upvotes: 2

Related Questions