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

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!

CodePudding user response：

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>>?.

CodePudding user response：

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();
}