Efcore: How To in EF Core 3.X: Dynamic/Runtime Join

[OpenSpacesAndPlaces]

In 2.X I used to be able to-do something like this to achieve a runtime join:
```C#
from child in mcc.Set()
join parent in mcc.Set() on child.GetValue(lstKChild, sChildKeyName) equals parent.GetValue(lstKParent,sParentKeyName)

public static object GetValue(this object value, List Props, String Name)
{
return Props.FirstOrDefault(p => p.Name.Equals(Name,
StringComparison.InvariantCultureIgnoreCase)).GetValue(value);
}
```

What's the EF Core 3.X compatible way to-do this?
Currently I'm getting the "could not be translated" warnings.

I'm hoping maybe there is some kind of generic lambda express tree that's out there that would work.

[OpenSpacesAndPlaces]

Related to solution discussion in:
https://github.com/dotnet/efcore/issues/14864

[ajcvickers]

@OpenSpacesAndPlaces This was evaluated on the client in 2.x because GetValue is an opaque method call that can't be translated. Even if GetValue is in-lined into the expression tree (or otherwise changed to work with expressions), this still can't be translated because of StringComparison.InvariantCultureIgnoreCase--see #1222

If you need this to work with EF Core 3.x or later in the same way as it did in EF Core 2.x, then pull the data into memory (e.g. with AsEnumerable()) and then perform the join on the client using LINQ-to-Objects.

[OpenSpacesAndPlaces]

@ajcvickers Do you have a sample sitting around for doing the native expression trees like this without the "StringComparison"? (Not having the casing ignored is not a dealbreaker for me.)

In the meantime I did like you had suggested already - more/less pulling both independently filtered with a where and then doing a client-side join. Real-world performance penalty is one extra DB request - so not earth shattering.

But that said, it would still be a useful for functionality like I'm after to be more accessible - especially given most API formats are moving more and more towards client based data shaping.

Thanks for your help!

[roji]

In the meantime I did like you had suggested already - more/less pulling both independently filtered with a where and then doing a client-side join. Real-world performance penalty is one extra DB request - so not earth shattering.

Note that client evaluation isn't just about the one extra DB request - you're also pulling the entire parent and child tables from the database to the client in order to join there. If those tables are big, that's can be a serious perf issue. It is in general recommended to try to avoid this kind of dynamic query generation.

In any case, it will never be possible to construct an expression tree that looks at .NET PropertyInfo and which gets translated to SQL for server-side evaluation - the database doesn't know anything about your .NET properties.

[OpenSpacesAndPlaces]

In any case, it will never be possible to construct an expression tree that looks at .NET PropertyInfo and which gets translated to SQL for server-side evaluation - the database doesn't know anything about your .NET properties.

I'm kind of confused here - in terms of how EF Core declares these things normally - it's a just .Net class tied to a DbContext initializer. Unless I'm missing something entirely, the source for EF would have to be performing more/less the same translations at some-point to be able to-do like:

from child in mycontextinstance.mytablechild
join parent in mycontextinstance.mytableparent on child.classpropertyname equals parent.classpropertyname

Maybe I'm over simplying/overreaching, but it seems more like a matter of not wanting this publicly available vs. it not being technically possible.

---

Some of this too is that - like without that kind of functionality, you're basically forced to deal with the shortcomings of generics or to effectively re-declare DBContext/Table Class settings over again in a different format.

---

Where I'm a little lost is what the harm would be in just having something like:

from child in mycontextinstance.mytablechild
join parent in mycontextinstance.mytableparent on child.GetByString(sKey) equals parent.GetByString(sKey) 

And if there is no property on the call to GetByString(sKey)
throw new Exception("Column not found")

[roji]

What EF Core generally does, is to take your LINQ expressions and translate them to SQL. When your join references simple properties on your .NET classes, EF Core recognizes those properties and translates them to the corresponding columns names in the SQL you produce. However, if instead of simple properties you call GetByString, then you're calling your own function which EF Core doesn't know - it contains arbitrary C# code your wrote - so there's simply no way to translate that to SQL. The only way to execute that is on the client, which is where your GetByString function can be executed.

[OpenSpacesAndPlaces]

I'm with you there - what I'm trying to say is that, why can't something like "GetByString" be a built-in EF Core method?

[smitpatel]

why can't something like "GetByString" be a built-in EF Core method?

And what do you expect us to translate it to?

[OpenSpacesAndPlaces]

from child in mycontextinstance.mytablechild
join parent in mycontextinstance.mytableparent on child.classpropertyname equals parent.classpropertyname

Could be done as:

from child in mycontextinstance.mytablechild
join parent in mycontextinstance.mytableparent on child.GetByString("classpropertyname") equals parent.GetByString("classpropertyname")

[roji]

@OpenSpacesAndPlaces @smitpatel was asking you what SQL this would be translated to... The point is that your LINQ query with GetByString cannot be translated to SQL.

[smitpatel]

@OpenSpacesAndPlaces - If that is your equivalent then you can write that already in LINQ.
```C#
from child in mycontextinstance.mytablechild
join parent in mycontextinstance.mytableparent on EF.Property(child, "classpropertyname") equals EF.Property(parent, "classpropertyname")
````

[OpenSpacesAndPlaces]

I will give that a whirl - thanks much!