How can I create a generic Join() in C# LINQ

Question

I need to write a generic Join() function to perform a query between two DBSets, entities of type TEntity and parentEntities of type TParent. What this will do is get me an IQueryable of cObjectNames, each object with the PK of the entity and the name of the parent entity. both types have an IBaseEntity interface so the Id column is available but I need a way to generically specify the foreign key column in entities (fkCol in the example) and the parentEntities name column (parentNameCol).

public static IQueryable<cObjectNames> Join<TEntity, TParent>(IQueryable<TEntity> entities, IQueryable<TParent> parenEntities,
    string fkCol, string parentNameCol)
    where TEntity : class, IBaseEntity where TParent : class, IBaseEntity
{
    IQueryable<cObjectNames> qNames = entities.Join(parenEntities, e => e.fkCol, p => p.Id, (e, p) =>
        new cObjectNames() { name = p.parentNameCol, eId = e.Id });

    return qNames;
}

I know it is possible to use EF to get the parent object, but I need a generic solution for several such fk relationships, where even the parent name column is not constant. And please save me the Dynamic LINQ suggestions - LINQ generic expressions are so much cooler...

The definition of cObjectNames is

public class cObjectNames 
{
    public int eId{ get; set; }
    public string name{ get; set; }
}

and the IBaseEntity interface is:

public interface IBaseEntity
{
    int Id { get; set; }
    DateTimeOffset Created { get; set; }
    DateTimeOffset? Lastupdated { get; set; }
    DateTimeOffset? Deleted { get; set; }
}

Thanks!

Show definition of `cObjectNames` – Svyatoslav Danyliv Dec 22 '22 at 17:57 — Svyatoslav Danyliv, Dec 22 '22 at 17:57

score 1 · Accepted Answer · answered Dec 22 '22 at 19:20

Here is implementation. I hope inline comments are useful.

public static class JoinExtensions
{
    public static IQueryable<cObjectNames> Join<TEntity, TParent>(this IQueryable<TEntity> entities, IQueryable<TParent> parentEntities,
        string fkCol, string parentNameCol)
        where TEntity : class, IBaseEntity where TParent : class, IBaseEntity
    {
        // we can reuse this lambda and force compiler to do that
        Expression<Func<TEntity, int>> entityKeySelector = e => e.Id;
        Expression<Func<TParent, int>> parentKeySelector = p => p.Id;

        var entityParam = entityKeySelector.Parameters[0];
        var parentParam = parentKeySelector.Parameters[0];

        // Ensure types are correct
        var fkColExpression = (Expression)Expression.Property(entityParam, fkCol);
        if (fkColExpression.Type != typeof(int))
            fkColExpression = Expression.Convert(fkColExpression, typeof(int));

        // e => e.fkCol
        var fkColSelector = Expression.Lambda(fkColExpression, entityParam);
        
        // (e, p) => new cObjectNames { name = p.parentNameCol, eId = e.Id }
        var resultSelector = Expression.Lambda(Expression.MemberInit(Expression.New(cObjectNamesConstrtuctor),
                Expression.Bind(cObjectNamesNameProp,
                    Expression.Property(parentParam, parentNameCol)),
                Expression.Bind(cObjectNamesIdProp,
                    entityKeySelector.Body)),
            entityParam, parentParam);

        //  full Join call
        var queryExpr = Expression.Call(typeof(Queryable), nameof(Queryable.Join),
            new Type[] { typeof(TEntity), typeof(TParent), typeof(int), typeof(cObjectNames) },
            entities.Expression,
            parentEntities.Expression,
            Expression.Quote(fkColSelector),
            Expression.Quote(parentKeySelector),
            Expression.Quote(resultSelector)
        );

        var qNames = entities.Provider.CreateQuery<cObjectNames>(queryExpr);

        return qNames;
    }

    static ConstructorInfo cObjectNamesConstrtuctor = typeof(cObjectNames).GetConstructor(Type.EmptyTypes) ??
                                                        throw new InvalidOperationException();

    static MemberInfo cObjectNamesNameProp = typeof(cObjectNames).GetProperty(nameof(cObjectNames.name)) ??
                                                throw new InvalidOperationException();
    static MemberInfo cObjectNamesIdProp = typeof(cObjectNames).GetProperty(nameof(cObjectNames.eId)) ??
                                                throw new InvalidOperationException();
}

This is brilliant!!! I wish I could buy you a coffee or a beer! Is there somewhere I can learn these recipes? It is the second time you are helping me out - GroupBy() was in October '21... — GilShalit, Dec 22 '22 at 20:13
Actually everything is here in SO. Just search for simple parts: how to call method, how to create Lambda, etc. Also you can always look into Microsoft's implementation of such methods. My experience is from LINQ Provider that I'm maintainig - `linq2db` and this `Join` was just a coffee break. — Svyatoslav Danyliv, Dec 22 '22 at 20:32

Olivier Jacot-Descombes · Answer 2 · 2022-12-22T18:14:18.750

You can retrieve meta-data from your EF-Core context:

IEntityType entityType = context.Model
    .FindEntityTypes(typeof(TEntity))
    .FirstOrDefault();

From here you can get the navigation properties:

foreach (IReadOnlyNavigation nav in entityType.GetNavigations()) {
    if (nav.IsOnDependent) {
        var parentProp = nav.Inverse?.PropertyInfo;
        var childProp = nav.PropertyInfo;
        Type parentType = nav.TargetEntityType.ClrType;
        var foreignKeyProps = nav.ForeignKey.Properties;
        ... etc., etc.
    }
}

At least, this is a starting point. Then you will have to create expressions through Reflection. See: How do I dynamically create an Expression<Func<MyClass, bool>> predicate from Expression<Func<MyClass, string>>?.

How can I create a generic Join() in C# LINQ

2 Answers2