C#•3mo ago

Define a Many to Many navigation in EF Core without any Join Table

Restrictions 1. The model is Db First 2. I cannot modify the Db Schema (Im not in charge of it) Challenge Currently speaking I have a pseudo "Many to Many" relationship between 2 tables via a non unique column they both share and I LEFT JOIN on it. It looks something like so:

[PrimaryKey(nameof(A_Id), nameof(A_Version))]
public class DataModelA
{
    public int A_Id { get; set; }

    public int A_Version { get; set; }

    public ICollection<DataModelB> Bs { get; set; } = [];
}

[PrimaryKey(nameof(B_Id), nameof(B_Version))]
public class DataModelB
{
    public int B_Id { get; set; }

    public int B_Version { get; set; }

    public int A_Id { get; set; }
    public ICollection<DataModelA> As { get; set; } = [];
}

[PrimaryKey(nameof(A_Id), nameof(A_Version))]
public class DataModelA
{
    public int A_Id { get; set; }

    public int A_Version { get; set; }

    public ICollection<DataModelB> Bs { get; set; } = [];
}

[PrimaryKey(nameof(B_Id), nameof(B_Version))]
public class DataModelB
{
    public int B_Id { get; set; }

    public int B_Version { get; set; }

    public int A_Id { get; set; }
    public ICollection<DataModelA> As { get; set; } = [];
}

Note that both have a composite key, but they only join on half of the composite key, A_Id As a result, I could for example, have 5x DataModelA with A_Id = 1, and I could have 5x DataModelB with A_Id = 1, which would then produce a 5 <-> 5 relationship when I LEFT JOIN on this column. This pattern unfortunately is present across multiple tables on my db in this same way, the architect really liked to do this stuff and now I have to live with it and try and make it work with EF Core, I cannot modify the schema Current Solution Right now all over my database I have a bunch of Join operations like so:

var asToBs = db.As.Join(
    db.Bs,
    a => a.A_Id,
    b => b.A_Id,
    (a, b) => new { a, b }
)

var asToBs = db.As.Join(
    db.Bs,
    a => a.A_Id,
    b => b.A_Id,
    (a, b) => new { a, b }
)

Which works but its a lot of extra boilerplate as I have to do this for each navigation which very quickly produces a lot of code bloat to do basic stuff. I have tried to setup a property based navigation for this with the above schema but unfortunately it seems EF Core just has no API exposed to define a Many to Many relationship without an intermediary join table, and instead just define it as a basic LEFT JOIN from A to B. If anyone knows a way to do this, lemme know.

9 Replies

Angius•3mo ago

Worst case scenario, move that custom join into an extension method on IQueryable EF, generally, works when your schema is by the book, so many to many with a join table, and so on Once you start straying from the "standard" you find yourself having to employ similarly non-standard workarounds

PixxelKick•3mo ago

Mostly what Id like is simply to just define a nav prop as a LEFT JOIN, which honestly should be a pretty normal thing to do

Angius•3mo ago

Can also try asking in #database

PixxelKick•3mo ago

thats where I originally posted decided to move it over here since there was a lotta info and folks may wanna ref it later and I didnt wanna clog the chat up

leowest•3mo ago

As a result, I could for example, have 5x DataModelA with A_Id = 1, and I could have 5x DataModelB with A_Id = 1, which would then produce a 5 <-> 5 relationship when I LEFT JOIN on this column.

By this u mean u can have say:

A1 B1
A1 B2
A1 B3
A1 B4
A1 B5
B1 A2
B1 A3
B1 A4
B1 A5

A1 B1
A1 B2
A1 B3
A1 B4
A1 B5
B1 A2
B1 A3
B1 A4
B1 A5

? just making sure

PixxelKick•3mo ago

I'm not sure what your example there translates too, tbh. But it'd be something like

| A_Id | A_Version |
|------|-----------|
| 1    | 1         |
| 1    | 2         |
| 1    | 3         |
| 1    | 4         |
|   1  | 5         |

| A_Id | A_Version |
|------|-----------|
| 1    | 1         |
| 1    | 2         |
| 1    | 3         |
| 1    | 4         |
|   1  | 5         |

| B_Id     | B_Version     | A_Id     |
|------    |-----------    |------    |
| 1        | 1             | 1        |
| 1        | 2             | 1        |
| 2        | 1             | 1        |
| 2        | 2             | 1        |
|   3      | 1             | 1        |

| B_Id     | B_Version     | A_Id     |
|------    |-----------    |------    |
| 1        | 1             | 1        |
| 1        | 2             | 1        |
| 2        | 1             | 1        |
| 2        | 2             | 1        |
|   3      | 1             | 1        |

If you join A on B on both tables' respective A_Id cols, you'd get a 5<->5 many to many

leowest•3mo ago

B_Id  A_Id
1        1
1        1

B_Id  A_Id
1        1
1        1

And you can repeat ids like that? im not sure ef can do that out of the box

PixxelKick•3mo ago

yes because its a composite key. Table_A has a composite key of A_Id + A_Version, so you can have duplicates of A_Id but not duplicates of A_Id + A_Version combined

leowest•3mo ago

Yeah I don't see a way to link these at model level unhappily

Gaming

Programming

Define a Many to Many navigation in EF Core without any Join Table