Navigation property between two models with "columns in common" without a join/pivot table

[atrauzzi]

If I have two tables with columns in common, it's plausible that they could loosely relate to one another in a many-to-many fashion, without requiring a join table and without requiring a joining model:

blue_table

• id - uuid
• group - string
• name - string

green_table

• id - uuid
• group - string
• name - string

Is it possible to map this kind of schema with models as follows?

public class Blue
{
    public Guid Id { get; set; }
    public string Group { get; set; }
    public string Name { get; set; }
    public ICollection<Green> Greens { get; set; }
}

public class Green
{
    public Guid Id { get; set; }
    public string Group { get; set; }
    public string Name { get; set; }
    public ICollection<Blue> Blues { get; set; }
}

I know I'll need some way to inform EF about the fact that the relation is to be facilitated by the Group and Name columns both tables have in common. Although if this is possible, I'm not quite sure what the correct incantation would be in my DbContext 😉

Regarding the overall motivation behind this, I deliberately want this to be loose and obviously I'd also be well served by making sure there are appropriate indexes under the hood.

[AndriySvyryd]

@roji

[bricelam]

Would your join table effectively be the result of this query?

SELECT
    b.id AS blueId,
    g.id AS greenId
FROM blue_table b
    CROSS JOIN green_table g ON b.group = g.group

[atrauzzi]

With the caveat that I'm not trying to have a join table, I guess so? I would want to be able to get back both blue and green data for blue and green instances.

[roji]

@atrauzzi what kind of table schema are you thinking of, and which kind of SQL are you expecting EF Core to generate for you? Having a clear schema with some data sample might make help clarify what you have in mind.

[bricelam]

I'll need some way to inform EF about the fact that the relation is to be facilitated by the Group and Name columns both tables have in common.

This is the part we need clarification on. How do the Group and Name columns relate instances of Blue and Green entities? In the SQL I provided above, each blue entity would be related to every other green entity in the same group. Like this:

[bricelam]

Note that you can also relate them on the client without a navigation property:

var blue = dbContext.Blues.Find(1);
var relatedGreens = from g in dbContext.Greens
                    where g.Group == blue.Group
                    select g;

[atrauzzi]

The idea is that blues and greens could be matched to one another based on both the group and name columns, but fetched together in a single query.

So if I fetched a blue with group 'group-1' and name 'name-1', EF would populate the navigation property Greens from my example models above with all the greens with the same group and name.

[AndriySvyryd]

Related: #23348

[atrauzzi]

@AndriySvyryd - Possibly, although I want to distinguish that this isn't self-referential. But you might see some valid/relevant commonalities 😉

[roji]

I may be missing something, but I'm still not clear on how you're representing a many-to-many relationship between the two tables via group and name. Could you please provide sample data and SQL queries?

[atrauzzi]

Query might look something akin to:

select * 
    from blue
    join green on green."group" = blue."group" and green.name = blue.name
    where blue.group = 'group-1' and blue.name = 'name-1'        

I'm not saying this is the best way to do it, but I'm just showing how I can connect the two. It's exactly as simple as it sounds.

It's not a hard reference, but it is a way in which these two tables can relate to one another.

[roji]

Thanks.

So this seems simply like a "group membership" model. To make this concrete, we could think of Customers and Suppliers, with each referencing a single Country in which they are located. We could then query for a Customer's (multiple) Suppliers, with that implicitly meaning "Suppliers in the same Country as that Customer".

Crucially, although in .NET this would be modelled like many-to-many (i.e. collection navigation properies on both sides), it's unlike traditional many-to-many in that you can't arbitrarily manage relationships between Customers and Suppliers - all you can do is change the Country of a Customer or Supplier.

PS Note that the "name" column above doesn't really add anything to the scenario - conceptually the same thing works with a single "group" column (as with Country in the above example). "name" is effectively part of a composite key to the grouping entity type alongside "group".

[atrauzzi]

That's correct. In my current problem that I'm trying to solve, I have a composite identifier. So it is important that both be able to be used when joining.

It's there any way to model this with EF? Or am I getting close to needing to throw in raw queries and/or dapper into the mix?

[roji]

@atrauzzi this simply seems to be Customer and Supplier entity types, with a Country entity type in the middle, having two one-to-many relationships to each side:

public class Customer
{
    public int Id { get; set; }
    public Country Country { get; set; }
    public List<Supplier> Suppliers { get; set; }
}

public class Supplier
{
    public int Id { get; set; }
    public Country Country { get; set; }
}

public class Country
{
    [Key]
    public string Name { get; set; }

    public List<Customer> Customers { get; set; }
    public List<Supplier> Suppliers { get; set; }
}

To load a specific Customer with all their Suppliers, you'd simply go through the Customer's Country navigation, and from there to the Country's Suppliers, e.g.

_ = await ctx.Customers.Include(c => c.Country).ThenInclude(c => c.Suppliers).ToListAsync();

It may be possible to configure a skip navigation to have a Suppliers navigation directly on Customers, just like with a real many-to-many relationship; but in any case, that would only be sugar.

I definitely don't see any need to drop down to raw SQL here, unless I'm missing something?

[atrauzzi]

I don't have and don't want the in-between record though. I need the matching to be a little more fluid than that as these records come and go on different lifecycles.

[roji]

If you want to avoid the Countries table, you can always just join yourself, bypassing EF Core's relationships mechanism:

foreach (var (customer, supplier) in ctx.Customers.Join(ctx.Suppliers, c => c.Country, s => s.Country, (c, s) => ValueTuple.Create(c, s)))
{
    // ...
}

public class Customer
{
    public int Id { get; set; }
    public string Country { get; set; }
}

public class Supplier
{
    public int Id { get; set; }
    public string Country { get; set; }
}

This produces the SQL:

SELECT [c].[Id], [c].[Country], [s].[Id], [s].[Country]
FROM [Customers] AS [c]
INNER JOIN [Suppliers] AS [s] ON [c].[Country] = [s].[Country]

That's far from ideal, but is certainly better than raw SQL.

Doing this with 1st-class EF relationships could be covered under the #240 somewhere, unless @AndriySvyryd has a better idea or remembers another issue?

[AndriySvyryd]

We don't plan to support this fully in EF Core. The best workaround would be to Map the navigation to a query

[roji]

To add to @AndriySvyryd's response above, one major problem with mapping this via collection relationships is that it's not possible to modify the collection navigation. In other words, if we have a Suppliers navigation on Customer (which is what's being discussed), we wouldn't be able to support adding/removing suppliers on that collection.

[atrauzzi]

Makes sense, thanks guys!