Remove From My Forums

How do you create an expression that includes a join?

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

Answered by:

How do you create an expression that includes a join?

Question

Answers