entity-framework-core

How to handle one to many foreign keys in Entity Framework Core 8.0

This project works with trucks and trailers - you know, over-the-road trucks and trailers. It is a MAUI .NET 8.0 project that runs to an Android device. The solution has an ASP.NET Core 8.0.3 Web API web service for posting to the database.

SUMMARY: my app needs to submit a truck to the database, then submit that truck's trailer. The problem is that I cannot successfully submit the truck's matching trailer. It seems that EF Core 8.0 handles foreign keys differently than older versions.

To create the model classes in the models project (gaModels), I run a scaffolding command (so, reverse engineering) from the Package Manager Console window in Visual Studio. The questions I have found here on Stackoverflow use code-first.

Here is the scaffolding command:

Scaffold-DbContext "my Db connection string" 
    Microsoft.EntityFrameworkCore.SqlServer 
    -OutputDir wjbeitlerDb 
    -Tables "MobileUser", "LoggedInfo", "guardsTruck", "guardsTrailer"  
    -Force -Project "gaModels"

Tables

Here is a screenshot of the truck table in SSMS:

guardsTruck table

Here is a screenshot of the trailer table table in SSMS:

guardsTrailer table

I created a foreign key in the trailers table:

ALTER TABLE [dbo].[guardsTrailer] WITH NOCHECK 
    ADD CONSTRAINT [FK_guardsTrailer_guardsTruck] 
        FOREIGN KEY([TruckId])
        REFERENCES [dbo].[guardsTruck] ([GuardsTruckId])
GO

ALTER TABLE [dbo].[guardsTrailer] NOCHECK CONSTRAINT [FK_guardsTrailer_guardsTruck]
GO

My plan was to:

  1. submit a truck
  2. grab that truck's ID from the web service response
  3. submit a trailer, putting the truck's ID into Trailer.TruckId

However, EF Core 8.0's scaffolding command added an entire truck object to the trailer class (see the bottom of this code block):

public partial class GuardsTrailer
{
    public int GuardsTrailerId { get; set; }
    public int TruckId { get; set; }
    public int Shipper { get; set; }
    public string? BolNumber { get; set; }
    public string? SealNumber { get; set; }
    public int? RefferTemp { get; set; }
    public string? TrailerNumber { get; set; }
    public string? State { get; set; }
    public string? LicensePlate { get; set; }
    public string? ActualSealNumber { get; set; }
    public int? SealCondition { get; set; }
    public DateTime? TimeOut { get; set; }
    public string? Comments { get; set; }
    public string? PhoneNumber { get; set; }
    public virtual GuardsTruck Truck { get; set; } = null!;
}

No matter what I put in that Truck property, the submit to the database does not work.

I have tried:

  1. submit the Trailer w the Truck obj that was created BEFORE submitting to the Db
    1. it has TruckId = 0
    2. that creates an additional Truck in the database with a new ID AND successfully creates a Trailer with the NEW TruckID
    3. AND throws Internal Server Error.
  2. submit the new Trailer with null for the Truck object, setting the TruckId to the value returned when I POSTed the truck.
    1. nothing gets posted to the Trailer table
    2. the code does not hit the ws method. I guess EF Core stops it from trying.
  3. submit the new Trailer with the RETURNED Truck as the Trailer.Truck object
    1. ws method throws something like:

    Cannot send a Truck that already has a TruckId value

Here is the trailer entity that the scaffolding command creates in the DbContext:

modelBuilder.Entity<GuardsTrailer>(entity =>
{
    entity.ToTable("guardsTrailer");

    entity.Property(e => e.GuardsTrailerId).HasColumnName("guardsTrailerId");
    entity.Property(e => e.ActualSealNumber)
        .HasMaxLength(20)
        .IsFixedLength();
    entity.Property(e => e.BolNumber)
        .HasMaxLength(20)
        .IsFixedLength();
    entity.Property(e => e.Comments)
        .HasMaxLength(50)
        .IsFixedLength();
    entity.Property(e => e.LicensePlate)
        .HasMaxLength(20)
        .IsFixedLength();
    entity.Property(e => e.PhoneNumber)
        .HasMaxLength(15)
        .IsFixedLength();
    entity.Property(e => e.SealNumber)
        .HasMaxLength(20)
        .IsFixedLength();
    entity.Property(e => e.State)
        .HasMaxLength(2)
        .IsFixedLength();
    entity.Property(e => e.TimeOut).HasColumnType("datetime");
    entity.Property(e => e.TrailerNumber)
        .HasMaxLength(20)
        .IsFixedLength();

    entity.HasOne(d => d.Truck).WithMany(p => p.GuardsTrailers)
        .HasForeignKey(d => d.TruckId)
        .OnDelete(DeleteBehavior.ClientSetNull)
        .HasConstraintName("FK_guardsTrailer_guardsTruck");
});

A weird thing ^ there is that there is no HasKey there like maybe:

entity.HasKey(e => e.GuardsTrailerId).HasName("PK__Name");

Likewise, the truck entity in the DbContext does not have a HasKey, AND the primary key, GuardsTruckId, is not even in the entity:

modelBuilder.Entity<GuardsTruck>(entity =>
{
    entity.ToTable("guardsTruck");

    entity.Property(e => e.DriversFirstName)
        .HasMaxLength(15)
        .IsFixedLength();
    entity.Property(e => e.DriversLastName)
        .HasMaxLength(15)
        .IsFixedLength();
    entity.Property(e => e.LicensePlate)
        .HasMaxLength(15)
        .IsFixedLength();
    entity.Property(e => e.LicensePlateState)
        .HasMaxLength(2)
        .IsFixedLength();
    entity.Property(e => e.Shipper)
        .HasMaxLength(15)
        .IsFixedLength();
    entity.Property(e => e.TimeOfEntry).HasColumnType("datetime");
    entity.Property(e => e.TimeOfExit).HasColumnType("datetime");
});

How can I submit the truck then submit the matching trailer?

Solution

  • You will run into issues passing entities around in client-server environments like MAUI or MVC/Web API because with EF, references are everything in terms of importance. When you pass entities around you aren't passing tracked references, behind the scenes MVC/Web API will be instantiating new instances of entity classes, which when given to a DbContext, will be treated as new rows. This leads to exceptions or duplicate data inserts.

    Inserting data that has no associations is typically no problem. Such as when you insert a new Truck. Inserting data that does have an association needs a bit more care and attention, such as inserting a Trailer since a Trailer is associated to a Truck.

    Say we have a method that accepts a trailer we want to insert:

    public Task<ActionResult> CreateTrailer(Trailer trailer)

    ... and that Trailer includes a Truck navigation property. When the Trailer (and associated Truck) were populated from a web request we cannot simply go:

    _context.Trailers.Add(trailer);

    The reason is that this will see a new reference to a Truck and try to insert that as well. Problem being, that truck already exists in the DB. First we have to handle the association. We could call _context.Attach(trailer.Truck); first, and that would work some/most of the time, but depending on code that might have attached or loaded Trucks etc. that code could fail. The safest way to handle it would be:

    public Task<ActionResult> CreateTrailer(Trailer trailer)
{
    ArgumentNullException.ThrowIfNull(trailer, nameof(trailer));

    var existingTruck = _context.Trucks.Local.FirstOrDefault(x => x.TruckId == trailer.Truck.TruckId);
    if (existingTruck != null)
        trailer.Truck = existingTruck;
    else
        _context.Attach(truck);

    _context.Trailers.Add(trailer);
    await _context.SaveChangesAsync();

    return View(trailer);
}

    Here we check the tracking cache (Trucks.Local). This doesn't hit the database, but if we find the context is tracking that truck we replace the trailer.Truck reference to the tracked Truck. If we aren't tracking the truck then we can Attach it. Once that's done we can insert the trailer and it should go through without an error since either way it will treat the Truck as "this is an existing row".

    When updating data we need to do the same checks, and I strongly recommend avoiding the DbContext Update() method. If we are updating a Trailer and some of its data can change, including getting associated to a new truck, do not simply try to use the Update() method on the trailer. Again, associations will mess things up, and Update will overwrite everything about the entity, opening the door for tampering. Instead:

    public Task<ActionResult> UpdateTrailer(Trailer trailer)
{
    ArgumentNullException.ThrowIfNull(trailer, nameof(trailer));
    
    var existingTrailer = await _context.Trailers
        .Include(x => x.Truck)    
        .SingleAsync(x => x.TrailerId == trailer.TrailerId);

    // Copy fields over, only what you expect/allow to be changed.
    existingTrailer.TrailerNumber = trailer.TrailerNumber; 
    // ...

    if (trailer.Truck.TruckId != existingTrailer.Truck.TruckId)
    {
        var existingTruck = await _context.Trucks
             .SingleAsync(x => x.TruckId == trailer.Truck.TruckId);
        existingTrailer.Truck = existingTruck;
    }
    await _context.SaveChangesAsync();
    return View(existingTrailer);
}

    The above code first fetches the existing trailer and truck from the database. If a record does not exist, it will throw. (Cannot update what is not there) We copy across any values we allow to be changed, and this will result in an UPDATE SQL statement only for values that actually changed, and only if any actually changed. We don't need to check for changes, EF's change tracking does that automatically. We then check to see if the Truck reference changed, if so we fetch the new truck from the database (again asserts that the truck actually exists) and update the tracked trailer reference to the new truck before saving the changes.

    Being explicit and working with loaded data, rather than trying to use the Update() method handles associations. For raw speed we can do away with assertions and fetching the associated entities, but we have to remove association navigation properties first.

    For instance if we want to insert a trailer for a truck and the trailer has a TruckId FK as well as a Truck navigation property, we have to remove any Truck navigation property before we attach or Update the entity:

    public Task<ActionResult> InsertTrailer(Trailer trailer)
{
    ArgumentNullException.ThrowIfNull(trailer, nameof(trailer));
    
    var truck = trailer.Truck;
    trailer.Truck = null;  // we will rely on trailer.TruckId
    _context.Add(trailer);
    await _context.SaveChanges();
    trailer.Truck = truck;
    return View(trailer); 
}

public Task<ActionResult> UpdateTrailer(Trailer trailer)
{
    ArgumentNullException.ThrowIfNull(trailer, nameof(trailer));
    
    var truck = trailer.Truck;
    trailer.Truck = null;  // we will rely on trailer.TruckId
    _context.Update(trailer); // Still not recommended...
    await _context.SaveChanges();
    trailer.Truck = truck;
    return View(trailer); 
}

    When we remove the Truck navigation property reference before adding/updating then EF will just rely on the FK. In this example if we want to go back to a View of the trailer entity we copy back the Truck reference before returning. Alternatively it's often better to have the view reload the data.

    That should hopefully get you on the right path for things to check when working with entities. My overall advice though with web applications is to use POCO view models for the data going back and forth between server and views rather than passing entities. It helps avoid confusion/errors, and boosts performance and security by passing less data back and forth.