CODEWITHSUNDEEP
.net-coreentity-framework-core
SkinnyPete63
SkinnyPete63

Reputation: 653

Entity Framework Core - optional foreign key

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.

Upvotes: 1

Views: 1063

Answers (2)

Christian Bungi
Christian Bungi

Reputation: 31

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.

Upvotes: 3

SkinnyPete63
SkinnyPete63

Reputation: 653

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...

Upvotes: 0

Related Questions