Dynamically set which entity to join in LINQ query

Question

I have a query like :

from joinedEntity in joinedEntities
join relatedEntity in dbContext.X on ....

Now, I want to write the above query in a way that "X" could be dynamically passed so that I don't need to write a separate linq query for every different "X". Any help? Should I use expression trees and reflections? If yes, how? Thank you.

I have some guess that it should be something like this:

var parameter =  Expression.Parameter(typeof (CompanyContext), "dbContext");
var member = Expression.MakeMemberAccess(parameter, typeof(CompanyContext).GetMember("X")[0]);
.....

Here is a sample of my Join:

       from joinedEntity in joinedEntities
       join relatedEntity in dbContext.Channels on joinedEntity.Id equals relatedEntity.CId
        select new JoinedEntities(joinedEntity)
        {
        Channel  = relatedEntity.Name
        };

And this is the method syntax of it:

joinedEntities.Join(dbContext.Channels, m => m.Id, k => k.CId, (m, k) => new JoinedEntities(m)    { Channel = k.Name });

This is also another one:

from joinedEntity in joinedEntities
join relatedEntity in dbContext.ActivityOutput on joinedEntity.Id equals relatedEntity.CId
        select new JoinedEntities(joinedEntity)
         {
              ActivityOutput = relatedEntity.Name
           };

Answer 1

The easiest way would be to create an interface that says there is an CId field.

// Don't know if you need this one or if you want to have the class of joinedEntities always being the same
public interface IJoinedEntities
{
    int Id { get; set; }
}

public interface IRelatedEntity
{
    int CId { get; set; }
}

All the related entities would need to implement IRelatedEntity. Entity Framework has all their class are partial so you would just need to make another partial class to add this interface.

Since you want the projections just create an extension method that we will chain into the standard join.

public static class CustomIQueryableExtensions
{
    public static IQueryable<TResult> CommonJoinQueryable<TOuter, TInner, TResult>(this IQueryable<TOuter> outer,
                                                                          IQueryable<TInner> inner,
                                                                          Expression<Func<TOuter, TInner, TResult>>
                                                                              resultSelector)
        where TOuter : IJoinedEntities
        where TInner : IRelatedEntity
    {
        // have to use expression trees to build the join otherwise cast to interface is in expression tree
        var outerParam = Expression.Parameter(typeof (TOuter), "outer");
        var outerBody = Expression.Lambda<Func<TOuter, int>>(Expression.Property(outerParam, "CId"), outerParam);

        var innerParam = Expression.Parameter(typeof (TInner), "inner");
        var innerBody = Expression.Lambda<Func<TInner, int>>(Expression.Property(innerParam, "Id"), innerParam);

        return outer.Join(inner, outerBody, innerBody, resultSelector);
    }


    public static IEnumerable<TResult> CommonJoinEnumerable<TOuter, TInner, TResult>(this IEnumerable<TOuter> outer,
                                                                           IEnumerable<TInner> inner,
                                                                           Func<TOuter, TInner, TResult>
                                                                               resultSelector)
        where TOuter : IJoinedEntities
        where TInner : IRelatedEntity
    {
        // have to use expression trees to build the join otherwise cast to interface is in expression tree
        Func<TOuter, int> outerJoin = outerParam => outerParam.Id;
        Func<TInner, int> relatedJoin = innerParam => innerParam.CId;

        return outer.Join(inner, outerJoin, relatedJoin, resultSelector);
    }
}

Now you can just use it and still handle the projection.

joinedEntities.CommonJoinQueryable(dbContext.Channels, (m, k) => new JoinedEntities()    { Channel = k.Name, tracking = m }); 

joinedEntities.CommonJoinEnumerable(dbContext.Channels, (m, k) => new JoinedEntities(m)    { Channel = k.Name }); 

You should still use SQL Profiler to watch what gets generated when you use the Enumerable because it will pull the results down and then join them in memory, not from SQL. I know that's what you requested but it's not common. Instead of passing a parameter into the constructor you should consider making a property that you can just set with the projection then stick with IQueryable.

I believe this is what lnanikian was trying to get at.