Selecting Left Table on Inner Join to DbScanExpression in Entity Framework Interceptor

Question

Further to the SO question answered here, i'm trying to select all of the columns in the right table only without having to explicitly specify the column names together with UserId from the left table.

Any ideas on how this can be done?

public override DbExpression Visit(DbScanExpression expression)
{
    var table = expression.Target.ElementType as EntityType;
    if (table != null && table.Name == "User")
    {
        return expression.InnerJoin(
            DbExpressionBuilder.Scan(expression.Target.EntityContainer.BaseEntitySets.Single(s => s.Name == "TennantUser")),
            (l, r) =>
                DbExpressionBuilder.Equal(
                    DbExpressionBuilder.Property(l, "UserId"),
                    DbExpressionBuilder.Property(r, "UserId")
                )
        )
        .Select(exp => 
            new { 
                UserId = exp.Property("l").Property("UserId"), 
                Email = exp.Property("l").Property("Email") 
            });
    }

    return base.Visit(expression);
}

Did you find the solution? I've faced with the same issue as you and don't see any possible way except to build dynamically the expression that contains all columns — Killo, Dec 08 '15 at 20:56

score 2 · Accepted Answer · answered Dec 09 '15 at 19:20

Maybe the answer is a bit late, but anyway it may help a someone to save a time

 public override DbExpression Visit(DbScanExpression expression)
    {
        var table = expression.Target.ElementType as EntityType;

        if (table != null && table.Name == "User")
        {
            var rightExpression = expression.Target.EntityContainer.GetEntitySetByName("TennantUser", true).Scan();

            var join = expression.InnerJoin(rightExpression,
                (l, r) =>
                        DbExpressionBuilder.Equal(
                            DbExpressionBuilder.Property(l, "UserId"),
                            DbExpressionBuilder.Property(r, "UserId")
                        ));


            var select = join.Select(exp => DbExpressionBuilder.NewRow(
                expression.Target.ElementType.Members.Select(x => 
                    new KeyValuePair<string, DbExpression>(x.Name, exp.Property(join.Left.VariableName).Property(x.Name)))));


            return select;

        }

        return base.Visit(expression);
    }

Killo that's a nice simple solution. Where may I ask did you find the documentation for that? I solved this shortly after I posted the request, but had to go through a world of pain using CommandTree ExpressionBuilder. I've posted the code for the class that I wrote to achieve this. It may be of some use. — maggic, Jul 16 '16 at 17:27

score 0 · Answer 2 · answered Jul 16 '16 at 17:22

using LatticeUtils;
using System;
using System.Collections.Generic;
using System.Data.Entity.Core.Common.CommandTrees;
using System.Data.Entity.Core.Common.CommandTrees.ExpressionBuilder;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Authorisation
{
  public static class DbPropertyExpressionProjector
  {
    /// <summary>
    /// Constructs an anonymous function that returns a DbExpression and expects an Anonymous Type.
    /// Supports the ability to dynamically create the following lambda:
    /// p => new {
    ///   Column1 = p.Property("l").Property("Column1")
    /// }
    /// </summary>
    /// <param name="fields">List of column names</param>
    /// <returns>Func<DbExpression,T></returns>
    public static LambdaExpression CreateLambdaExpression(Dictionary<string, string> fields)
    {
      Type type = Type.GetType("System.Func`2");

      Type[] anonymousTypeArguments = fields.Keys.Select(p => typeof(DbPropertyExpression)).ToArray();
      Type anonymousType = AnonymousTypeUtils.CreateGenericTypeDefinition(fields.Keys);
      anonymousType = anonymousType.MakeGenericType(anonymousTypeArguments);

      Type[] typeArguments = new[] { typeof(DbExpression), anonymousType };
      var lambdaType = type.MakeGenericType(typeArguments);

      var pParam = Expression.Parameter(typeof(DbExpression), "p");
      ParameterExpression[] parameters = { pParam };

      ConstructorInfo constructor = anonymousType.GetConstructors().SingleOrDefault();

      MethodInfo memberInfo = typeof(DbExpressionBuilder).GetMethods().FirstOrDefault(m => m.ToString() == "System.Data.Entity.Core.Common.CommandTrees.DbPropertyExpression Property(System.Data.Entity.Core.Common.CommandTrees.DbExpression, System.String)");

      List<Expression> argumentList = new List<Expression>();
      List<MemberInfo> membersList = new List<MemberInfo>();

      foreach (string field in fields.Keys)
      {
        Expression[] leftTableExpressions = { pParam, Expression.Constant(fields[field]) };
        Expression methodCallExpression = Expression.Call(null, memberInfo, leftTableExpressions);

        Expression[] secondPropertyExpressions = { methodCallExpression, Expression.Constant(field) };
        argumentList.Add(Expression.Call(null, memberInfo, secondPropertyExpressions));
        membersList.Add(anonymousType.GetMembers().FirstOrDefault(m => m.ToString() == string.Format("System.Data.Entity.Core.Common.CommandTrees.DbPropertyExpression {0}", field)));
      }

      var body = Expression.New(constructor, argumentList, membersList);

      return Expression.Lambda(lambdaType, body, parameters);
    }
  }
}

Selecting Left Table on Inner Join to DbScanExpression in Entity Framework Interceptor

2 Answers2

Linked