Entity Framework Core - optional foreign key

Question

I am creating a web api that needs to return details about vehicles. The first part works fine, just returning data from my vehicles table. Now I have another table which may or may not contain additional data about vehicles in the first table. So when I get vehicle data, I want all of the vehicle data, and any additional data from the second table if it exists, like a left join in SQL.

Here are my classes (very much abridged for readability):

public class Vehicle
{
    [Key]
    [Required]
    public string registrationNo { get; set; }
    public string capacity{ get; set; }
    public string maxGross{ get; set; }
}

public class VehicleDvlaDetail
{
    [ForeignKey("Vehicle")]
    public string? registrationNumber { get; set; }
    public int? co2Emissions { get; set; }
}

And in my context class OnModelCreating I have (again, very abridged):

modelBuilder.Entity<Vehicle>(entity =>
        {
            entity.HasOne(dvlaRec => dvlaRec.dvlaDetail).WithMany().HasForeignKey(dvla => dvla.registrationNo);
        });

This works fine when there is an associated record in the DVLA table, but that isn't always the case. I need to keep them as separate entities as my API will be required to return details from the DVLA table separately as well. Is there any way to create an optional foreign key, as clearly, what I am doing is wrong.

score 3 · Answer 1 · answered Sep 23 '22 at 08:33

Friendly advice: Primary key as a string is not a good practice because of performance issues when data table has lots of data in it.

It would be better if you create your model like this:

public class Vehicle
{
    public long Id { get; set; }
    public string RegistrationNo { get; set; }
    public string Capacity { get; set; }
    public string MaxGross { get; set; }

    public List<VehicleDvlaDetail> VehicleDvlaDetails { get; set; }
}

public class VehicleDvlaDetail
{
    public long? VehicleId { get; set; }
    public int? Co2Emissions { get; set; }

    public Vehicle Vehicle { get; set; }
}

Vehicle and VehicleDvlaDetail are now connected without additional code in OnModelCreating method and it is possible to fetch vehicles with details like this (this is assuming you have named properties in dbcontext Vehicles and VehicleDvlaDetails):

_dbContext.Vehicles.Include(x => x.VehicleDvlaDetails).ToList();

Also as foreign key VehicleId is nullable - this allows for vehicles not to have any dvla details.

Thanks for the detailed response Christian, it's much appreciated. I am stuck with the string key as it's an existing (badly designed) database running an application that should have been retired about 20 years ago, hence why I am building this API!!! — SkinnyPete63, Sep 23 '22 at 10:33

score 0 · Answer 2 · answered Sep 22 '22 at 15:40

0

Wow. I spent about 3 hours looking for the answer, just posted the question and came across this: Create an optional foreign key using the fluid-API for Entity Framework 7

So simple...

answered Sep 22 '22 at 15:40

SkinnyPete63

597
1
5
20

It's even simpler. If `Vehicle` had a `Details` property, and `Detail` a nullable `Vehicle` (ie whose type is Vehicle?), the foreign keys would be inferred. If the class and property names follow naming conventions (these ones don't) the tables and their relations would be inferred automatically – Panagiotis Kanavos Sep 23 '22 at 06:58
Check the [Relations](https://learn.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key) page in the docs. It shows how all types on relations can be defined, in most cases simply by following conventions. Even bridge tables in many-to-many relations can be created automatically – Panagiotis Kanavos Sep 23 '22 at 07:02

Entity Framework Core - optional foreign key

2 Answers2