Entity Framework nested joins

Question

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);
    });
});

why do you have an entity vehicles as a container for a list of vehicles? That does not sound clean. If that's what you BO's look like, you should consider converting them into model objects to save one table and the one-to-one relationship. Also, you may consider either exposing or hiding all FK properties to be consistent. — DevilSuichiro, Mar 23 '19 at 09:26
This is unusual model, so it's not surprising that it's problematic to be mapped. The way `Vehicles` class is defined in the example (w/o additional properties) it makes no sense. In order to try helping, we need to know the meaning of `Vehicles` class - is it an *entity* (and if yes, does it have its own table or has to be stored in the `Customer` table) or just a some sort of a container class? — Ivan Stoev, Mar 23 '19 at 13:44
It is a container class. It does not have its own table. The two tables involved in this are a customers table and a vehicles table. I'm attempting to reuse a large set of entities that exist in this solution. They aren't exactly representative of the database but are very close. I've been able to make the rest of it work with fluent mapping. — Jacob, Mar 23 '19 at 13:52

Ivan Stoev · Accepted Answer · 2019-03-24T10:18:52.073

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

score 0 · Answer 2 · answered Mar 23 '19 at 06:15

0

Use .Join

See this question for some examples:

Entity Framework Join 3 Tables

answered Mar 23 '19 at 06:15

Ian Preglo

421
2
10

Entity Framework nested joins

2 Answers2