Entity Framework Core Database First - Multiple Foreign keys to one table

asp.net-core-2.0 c# ef-database-first entity-framework-core

Question

Background Information

I am currently working with EF Core using a database first implementation.

Current tables

Fizz
{
    [Id] INT
    [Category] varchar
    [Value] varchar
}

Buzz
{
    [Id] UniqueIdentifier
    [TypeId1] INT
    [TypeId2] INT
    CONSTRAINT [FK_Buzz_Fizz_1] FOREIGN KEY ([TypeId1] REFERENCES [Fizz][Id]) 
    CONSTRAINT [FK_Buzz_Fizz_2] FOREIGN KEY ([TypeId2] REFERENCES [Fizz][Id])
}

Fizz currently acts a lookup table. Doing this allows for a single data repository to be used to find different values by category.

Buzz is a table that has two different type values to be stored e.g. TypeId1 could be brand which would exist in Fizz as (id, Brands, Nestle) and TypeId2 could be a flavor which would exist in Fizz as (id, Flavors, Grape).

The Issue

I scaffold the db to create the Data Models. When running the application the following occurrs:

InvalidOperationException: Unable to determine the relationship represented by navigation property 'Buzz.TypeId1' of type 'Fizz'. Either manually configure the relationship, or ignore this property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.

One solution that has occurred to me is to break this lookup table (Fizz) into multiple tables that way the references could be resolved by not having duplicate types used for Foreign Keys.

This would require re-work of the logic for the current data repository to either access multiple tables or be split into multiple data repos.

Another solution would be to modify the DBContext that is generated and use DataAnnotations on the DataModel. I would like to avoid doing this as the Context and Models will be regenerated in the future and these changes will be overwritten.

Is there a way to have a datamodel generated from a table that has multiple Foreign Keys to a single table without having to modify the generated code?

1
1
1/14/2019 2:02:31 AM

Accepted Answer

For posterity:

With the database approach a scaffold of the db is done to create the context and data models.

The data models generated (using the example tables above) look something like this -

public partial class Buzz
{
    public Buzz()
    { }

    public Guid Id { get; set; }
    public int TypeId1 { get; set; }
    public int TypeId2 { get; set; }

    public Fizz TypeId1Fizz { get; set; }
    public Fizz TypeId2Fizz { get; set; }
}


public partial class Fizz
{
    public Fizz()
    { }

    public int Id { get; set; }
    public string Category { get; set; }
    public string Value { get; set; }

    public ICollection<Buzz> TypeId1Fizz { get; set; }
    public ICollection<Buzz> TypeId2Fizz { get; set; }
}

The issue is that the relationship in Buzz could not be resolved.

The solution

When using scaffold on the database all models are generated as partials to a specified folder. I created a partial for the Buzz class in another directory that lives inside of the directory created by the scaffold (be sure that the namespaces match VS likes to add the directory name to the namespace and the partials won't be matched).

public partial class Buzz
{
    [NotMapped]
    public Fizz TypeId1Fizz { get; set; }
    [NotMapped]
    public Fizz TypeId2Fizz { get; set; }
}

but Leustherin then you lose the ability to utilize .Include for Fizz! EntityFramework won't create an SQL join statement for you so you will have to make an extra trip to the DB to obtain your look up value!

To get around this, override the Get or GetAll function of your data repository and create your own join statement.

Why I chose this solution

Maintainability.

Anytime the DataModels are regenerated instead of getting a runtime error there is now a compile error reminding the dev to delete the extra properties from the generated data model.

There is no other modification of automatically generated files.

There are no major schema changes done to accommodate the change.

I will do my best to keep this updated.

2
4/18/2018 9:16:47 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow