Is there a way to join by IKey/IForeignKey?

[OpenSpacesAndPlaces]

I already can query like (which is fine for a single field FK):

var query = (from child in mcc.Set<KChild>()
				    join parent in mcc.Set<KParent>() on child.GetPropertyValue(sChildKeyName) equals parent.GetPropertyValue(sParentKeyName)
				    select child);

The problem I'm running into is that I want to be able to generically do a join that might contain an unknown composite key with names that might vary across tables.

I already have IKey and 'IForeignKey' pulled - Is there a way to-do a query like the pseudo? (in particular agnostic to name differences)

var query = (from child in mcc.Set<KChild>()
				    join parent in mcc.Set<KParent>() on IKey equals IForeignKey
				    select child);

I see the path forward if you can guarantee the names were the same, but if the key names were different across KChild and KParent, then that falls apart.

---

I'm hoping there is already something I'm missing that has a dictionary like Dictionary<IProperty,IProperty> where we are mapping IKey properties to the 'IForeignKey' properties (one to one vs. inferred from reflection/naming/default because of there being one).

Thanks in advance for the help!

[smitpatel]

If both of the entities are fixed then why are the properties in join condition are changing? For composite key in a join, you can use anonymous types.

[OpenSpacesAndPlaces]

@smitpatel that would be true if I were doing a one time query setup, but the system design is such that it will evovle with changes to structure without code updates.

E.g. If add this to a legacy DB that so happens to have some dirty setup like:

Table 1
Field1
Field2
PK - Field1, Field2

Table 2
Field1Hello
Field2World
PK - Field1Hello, Field2World

I'm not seeing a way for this to happen automatically:
{Field1, Field2} equals { Field1Hello,Field2World }

Unless I'm missing something, IKey and 'IForeignKey' define the properties, but they don't define the one to one mapping.

It's a none issue for:
1 key field in each table
composites keys were all field names are the same

It becomes a problem for:
composites keys were some or all field names may be different

Somehow the EF Core internals have to know that one to one mapping, but If that's exposed in the meta data, I'm missing how you draw it out.

[smitpatel]

I'm not seeing a way for this to happen automatically

It does not happen automatically but you need to define members like this
new { A = Field1, B = Field2 } equals new { A = Field1Hello, B = Field2World }
Above statement would work in LINQ comprehension syntax without any issue. You can also use Queryable.Join method directly which allows you to specify key selector but that would also require types to be matching.
But I believe it is one type setup. If you change your model then you may need to update your queries too. The only possible way where you don't want to change linq queries would be dynamically generated queries. But that is not easy either since, you cannot create anonymous types at runtime when building expression tree dynamically. EF Core does have AnonymousObject type which takes expression array which you can use to satisfy type system and EF will expand out property matching one by one. (Beware it can break during any minor release too).

var query = (from child in mcc.Set<KChild>()
				    join parent in mcc.Set<KParent>() on IKey equals IForeignKey
				    select child);

What you are asking here not only invalid since IKey and IForeignKey are different types but also impossible for EF to do anything about it. Compiler generates expression tree out of comprehension syntax. We need compiler changes to generate anything like that. Best EF can do is provide some method to do it (it won't be pretty).

But then again, can you describe why you need such feature? (apart from the reason that you want to change design and don't update the code)

[OpenSpacesAndPlaces]

But then again, can you describe why you need such feature? (apart from the reason that you want to change design and don't update the code)

We're building out dynamic API that is agnostic to tables and table structure - this is the last little hurdle.

As mentioned I can already handle single keys no problem:

IKey ikChild = ifkQueryParentForeignKey.PrincipalKey;
String sChildKeyName = ikChild.Properties.FirstOrDefault().Name;
String sParentKeyName = ifkQueryParentForeignKey.Properties.FirstOrDefault().Name;

var query = (from child in mcc.Set<KChild>()
 join parent in mcc.Set<KParent>() on child.GetPropertyValue(sChildKeyName) equals parent.GetPropertyValue(sParentKeyName)
select child);

But then that gets trickier moving to composite keys, and seemingly impossible for composite keys that don't share a naming scheme.

The part that confuses me is that EF core under the hood has to know what the one to one FKs mappings are or it couldn't generate IKey and IForeignKey. But then when I look at all the Meta properties in debug mode I don't see any direct links with IProperty (only relationships I can infer).

---

If Queryable.Join supports dynamically generated lambda expressions, then that might solve the same name composite key case, but that still leaves me adrift on the different name composite key.

Looking at how AnonymousObject - that seems like alternate syntax to what I'm doing already - I'm guessing these would be the same/similar:
new { child.GetPropertyValue(sChildKeyName) } equals new { parent.GetPropertyValue(sParentKeyName)}
new AnonymousObject(new object[]{child.GetPropertyValue(sChildKeyName)}) equals new AnonymousObject(new object[]{parent.GetPropertyValue(sParentKeyName)})

If this is a non-exposed/non-available feature, then something like a Dictionary<IProperty,IProperty> would be perfect.

Even one step better would be if there is was a Join method that just takes that Dictionary<IProperty,IProperty> without having to get into generating dynamic/generic lambda expressions.

[ajcvickers]

@OpenSpacesAndPlaces We discussed this in triage, but it's really out-of-scope for what EF Core is intended to do. Specifically, we do not plan to support the ability to dynamically generate different queries from the same expression tree based on the underlying table structure changes. Even if you could find a way to generate the expression trees, the chance that EF would be able to translate them to SQL is very low.

[OpenSpacesAndPlaces]

@ajcvickers

I already have dynamic table agnostic expression trees for select/order by/where, that work just fine.

Any easy hack for the lack of a join input method in the composite case, same key/same name, could be like (inferred from reflection):
new { child.GetPropertyValue(arrChildkeys[0]),child.GetPropertyValue(arrChildkeys[1]) } equals new { parent.GetPropertyValue(arrParentKeys[0], parent.GetPropertyValue(arrParentKeys[1] )}

The fundamental thing I'm missing is the composite case, same key/different name. because there doesn't seem to be a meta property that has a one to one mapping. As I've mentioned several times, the confusing part to me is that EF Core would have to have the one to one mapping between IKey properties and IForeignKey properties, it just doesn't seem to be exposed.

In short - all that would be a needed is some kind of Dictionary<IProperty,IProperty> made publicly available.

I can understand not wanting to open the doors to some dynamic join method you have to maintain, but displaying IProperty to IProperty seems like an obvious upgrade to how IKey properties and IForeignKey properties are currently represented.

[smitpatel]

Are you sure that GetPropertyValue is evaluating on server? It could cause client eval which certainly disaster for joins.

[OpenSpacesAndPlaces]

@smitpatel

From what I can see in the logs, it does for the initial query like:
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT[item.tblChild].[Id] FROM [tbltblChild] AS [item.tblChild] INNER JOIN ( SELECT [item0].[Id] FROM [tblParent] AS [item0] ) AS [t] ON [item.tblChild].[Id] = [t].[Id]

Though it switches over to client eval for "Count":
QueryClientEvaluationWarning,Warning,The LINQ expression 'Count()
(that said, it is likely due to a custom projection of mine and nothing to-do with the join).

[ajcvickers]

@OpenSpacesAndPlaces If you're asking for how to find which properties in the dependent are constrained by correlated properties in the principal, then that's metadata on the IForeignKey class. For example, assuming foreignKey below is an IForeignKey, then:

var dependentProperties = foreignKey.Properties.ToList();
var principalProperties = foreignKey.PrincipalKey.Properties.ToList();

Each of these lists contains the properties, in order, on the dependent and principal side of the relationship. So for a simple Blog/Post model that constrains a Blog.BlogId FK property to a Post.Id PK property, both lists would contain a single element--the property for Post.BlogId in the first list, and the property for Post.Id in the second list.

[OpenSpacesAndPlaces]

@ajcvickers Correct - that's what I'm doing right now.

But from what I can tell using those methods assumes you only have single field FK or the composite FK fields are named the same in both tables.

Consider my earlier example - sometimes people will make tables with the same PK, but just name them slightly differently and in a different order in the table and/or the PK Index e.g.

Table 1
Field1
Field2
PK - Field1, Field2

Table 2
Field2World
Field1Hello
PK - Field2World, Field1Hello

Each of these lists contains the properties, in order

In the above example (which I've seen far worse in the wild) - you'd get
{Field1, Field2} equals { Field2World, Field1Hello }

And since the names are different - some kind of automatic reflection is out.

That's why I keep saying - IKey/IForeignKey really needs a meta field that has a mapping like:

new Dictionary<IProperty,IProperty> ()
{
{Field1,Field1Hello}
{Field2,Field2World}
}

What I keep going back to mentally is that "Scaffold-DbContext" or "Migrate" (from shell or code) would have to know some equivalent of Dictionary<IProperty,IProperty> or the translations would be all wrong.

[ajcvickers]

@OpenSpacesAndPlaces Assuming that mapping in OnModelCreating (or wherever) is correct, then the two lists will be in the correct order and each FK property will be "paired" with the appropriate PK property.

[OpenSpacesAndPlaces]

@ajcvickers

If that's true 100% of time, I'll roll with the assumption - I'm just worried coding something on order importance is a fragile solution long-term. Especially if you have a PK/FK that's comprised of two integers, they might collide in a way that still look like things are working correctly. Part of it too then , is I'm not seeing what a generic unit test I could even do to validate that assertion being accurate.