locked
How do you create an expression that includes a join? RRS feed

  • Question

  • User-1072848465 posted

    In the following query I want to change the Where clause to an expression.

            var getMembers = context.SherlockMembers.Join(context.MemberRoleDetails,
                                member => member.Id,
                                memberroledetail => memberroledetail.SherlockMemberId,
                                (member, memberroledetail) => new { member, memberroledetail })
                                .Where(cc => cc.member.lastName.StartsWith("A") || cc.member.lastName.StartsWith("B"))
                                .Select(cc => new {id = cc.member.Id}).Distinct().ToList();

    The expression that I have build contains 'member' , but it does not recognize it.

    Expression<Func<SherlockMember, bool>> expression = cc => cc.member.lastName.StartsWith("A") || cc.member.lastName.StartsWith("B");

    I would like the final query to utilize the Expression and look like

            var getMembers = context.SherlockMembers.Join(context.MemberRoleDetails,
                                member => member.Id,
                                memberroledetail => memberroledetail.SherlockMemberId,
                                (member, memberroledetail) => new { member, memberroledetail })
                                .Where(expression)
                                .Select(cc => new {id = cc.member.Id}).Distinct().ToList();

    Is this possible?

    Thanks in advance

    Brad

    Friday, October 16, 2015 9:51 AM

Answers

  • User-271186128 posted

    Hi bdassow,

    From your description, it seems that you want to dynamically build where expression. If that is the case, you could refer to the following code:

        public class PersonExpressionBuilder
        {
            public static class ExpressionBuilder
            {
                private static MethodInfo containsMethod = typeof(string).GetMethod("Contains");
                private static MethodInfo startsWithMethod =
                typeof(string).GetMethod("StartsWith", new Type[] { typeof(string) });
                private static MethodInfo endsWithMethod =
                typeof(string).GetMethod("EndsWith", new Type[] { typeof(string) });
    
    
                public static Expression<Func<T,
                bool>> GetExpression<T>(IList<Filter> filters)
                {
                    if (filters.Count == 0)
                        return null;
    
                    ParameterExpression param = Expression.Parameter(typeof(T), "t");
                    Expression exp = null;
    
                    if (filters.Count == 1)
                        exp = GetExpression<T>(param, filters[0]);
                    else if (filters.Count == 2)
                        exp = GetExpression<T>(param, filters[0], filters[1]);
                    else
                    {
                        while (filters.Count > 0)
                        {
                            var f1 = filters[0];
                            var f2 = filters[1];
    
                            if (exp == null)
                                exp = GetExpression<T>(param, filters[0], filters[1]);
                            else
                                exp = Expression.AndAlso(exp, GetExpression<T>(param, filters[0], filters[1]));
    
                            filters.Remove(f1);
                            filters.Remove(f2);
    
                            if (filters.Count == 1)
                            {
                                exp = Expression.AndAlso(exp, GetExpression<T>(param, filters[0]));
                                filters.RemoveAt(0);
                            }
                        }
                    }
    
                    return Expression.Lambda<Func<T, bool>>(exp, param);
                }
    
                private static Expression GetExpression<T>(ParameterExpression param, Filter filter)
                {
                    MemberExpression member = Expression.Property(param, filter.PropertyName);
                    ConstantExpression constant = Expression.Constant(filter.Value);
    
                    switch (filter.Operation)
                    {
                        case Op.Equals:
                            return Expression.Equal(member, constant);
    
                        case Op.GreaterThan:
                            return Expression.GreaterThan(member, constant);
    
                        case Op.GreaterThanOrEqual:
                            return Expression.GreaterThanOrEqual(member, constant);
    
                        case Op.LessThan:
                            return Expression.LessThan(member, constant);
    
                        case Op.LessThanOrEqual:
                            return Expression.LessThanOrEqual(member, constant);
    
                        case Op.Contains:
                            return Expression.Call(member, containsMethod, constant);
    
                        case Op.StartsWith:
                            return Expression.Call(member, startsWithMethod, constant);
    
                        case Op.EndsWith:
                            return Expression.Call(member, endsWithMethod, constant);
                    }
    
                    return null;
                }
    
                private static BinaryExpression GetExpression<T>
                (ParameterExpression param, Filter filter1, Filter filter2)
                {
                    Expression bin1 = GetExpression<T>(param, filter1);
                    Expression bin2 = GetExpression<T>(param, filter2);
    
                    return Expression.AndAlso(bin1, bin2);
                }
            }
    
    
        }

    Code in page (.aspx)

                List<Person> persons = InitData();
    
                List<Filter> filter = new List<Filter>()
                    {
                        new Filter { PropertyName = "City" ,
                            Operation = Op .Equals, Value = "Mitrovice"  },
                        new Filter { PropertyName = "Name" ,
                            Operation = Op .StartsWith, Value = "L"  },
                        new Filter { PropertyName = "Salary" ,
                            Operation = Op .GreaterThan, Value = 9000.0 }
                    };
    
                var deleg = PersonExpressionBuilder.ExpressionBuilder.GetExpression<Person>(filter).Compile();
                var filteredCollection = persons.Where(deleg).ToList();
    
                GridView1.DataSource = filteredCollection;
                GridView1.DataBind();

    For more details, you could refer to the following articles:

    http://www.codeproject.com/Tips/582450/Build-Where-Clause-Dynamically-in-Linq

    https://msdn.microsoft.com/en-us/library/bb882637.aspx

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, October 18, 2015 9:43 PM