Styjun

I am new to entity framework and am having a hard time trying to figure out how to query with a join when my models look like this (drastically simplified)

class Customer

 public int Id get; set;

 public Vehicles Vehicles get; set; 


class Vehicles

 public List<Vehicle> Items get; set;


class Vehicle

 public int Id get; set;

 public int CustomerId get; set;

If I put the List<Vehicle> on the customer class directly. I am able to do fluent mapping like this

builder.Entity<Customer>()
 .HasMany(x => x.Items)
 .WithOne()
 .HasForeignKey(x => x.CustomerId);

Which then I can do this and I get back a customer object with vehicles

db.Customers.Include(x => x.Items).FirstOrDefault(x => x.Id == 1);

What I am not understanding is how to do this with my original set of models. I would like to keep them the way they are if possible. I have tried doing various versions of this in my onModelCreating method with no luck.

builder.Entity<Customer>(t =>

 t.OwnsOne(x => x.Vehicles, v =>
 
 v.HasMany(x => x.Items).WithOne().HasForeignKey(x => x.CustomerId);
 );
);

It's possible to map the original classes, but in quite counterintuitive way.

Since the Vehicles class is just a container, mapping it as owned entity as you have tried seems the most natural way. However currently EF Core does not allow owned entity to be at the principal side of the relationship, and in your case this is needed.

So instead you need to map the Vehicles class as regular "entity" sharing the same table with the Customer - the so called table splitting. You have to do explcitly all that EF Core does implicitly for owned entities - define a shadow property and map is a both PK and FK for the one-to-one relationship with the Customer. You'd need also the explicitly map the Vehicle.CustomerId as a FK because from EF point of view the Vehicle is related to Vehicles rather than to Custome, hence the conventional FK property / column name assumed will be VehiclesId. Note that with this model you'll never be able to define an inverse navigation property Customer of the Vehicle.

With that being said, here is the fluent configuration needed:

modelBuilder.Entity<Vehicles>(builder =>

 // Table
 builder.ToTable(modelBuilder.Entity<Customer>().Metadata.Relational().TableName);
 // PK
 builder.Property<int>("Id");
 builder.HasKey("Id");
 // One-to-one relationship with Customer
 builder.HasOne<Customer>()
 .WithOne(e => e.Vehicles)
 .HasForeignKey<Vehicles>("Id");
 // One-to-many relationship with Vehicle
 builder.HasMany(e => e.Items)
 .WithOne()
 .HasForeignKey(e => e.CustomerId);
);

and usage:

db.Customers
 .Include(x => x.Vehicles.Items) // <--
 // ...

Use .Join

See this question for some examples:

Entity Framework Join 3 Tables