asked on

Can ExpressionBuilder work with complex data types?

I am trying to use ExpressionBuilder to build dynamic queries to retrieve data. I was able to get it to work successfully for a simple example.

        public class Person
        {
            public string Name { get; set; }
            public string Surname { get; set; }
            public int Age { get; set; }
            public string City { get; set; }
            public double Salary { get; set; }
            public bool IsHomeOwner { get; set; }
        }

        public List<Person> PopulatePersonsList()
        {
            List<Person> persons = new List<Person>
            {
                new  Person  { Name = "Flamur" , Surname = "Dauti" ,    Age = 39, 
                               City = "Prishtine" , IsHomeOwner = true ,  Salary = 12000.0 },
                new  Person  { Name = "Blerta" , Surname = "Frasheri" , Age = 25, 
                               City = "Mitrovice" , IsHomeOwner = false , Salary = 9000.0 },
                new  Person  { Name = "Berat" ,  Surname = "Dajti" ,    Age = 45, 
                               City = "Peje" ,      IsHomeOwner = true ,  Salary = 10000.0 },
                new  Person  { Name = "Laura" ,  Surname = "Morina" ,   Age = 23, 
                               City = "Mitrovice" , IsHomeOwner = true ,  Salary = 25000.0 },
                new  Person  { Name = "Olti" ,   Surname = "Kodra" ,    Age = 19, 
                               City = "Prishtine" , IsHomeOwner = false , Salary = 8000.0 },
                new  Person  { Name = "Xhenis" , Surname = "Berisha" ,  Age = 26, 
                               City = "Gjakove" ,   IsHomeOwner = false , Salary = 7000.0 },
                new  Person  { Name = "Fatos" ,  Surname = "Gashi" ,    Age = 32, 
                               City = "Peje" ,      IsHomeOwner = true ,  Salary = 6000.0 },
            };
            return persons;
        }

        [TestMethod]
        public void Returns_Three_Records_When_Name_Equals_Flamur_Or_City_Equals_Peje_And_Age_Equals_39_()
        {
            // Arrange
            List<Person> persons = PopulatePersonsList();
            List<Filter> filter = new List<Filter>()
            {
                new Filter { PropertyName = "Name", Operation = Op.Equals, Value = "Flamur", Conditional = ConOp.Or },
                new Filter { PropertyName = "City", Operation = Op.Equals, Value = "Peje", Conditional = ConOp.And },
                new Filter { PropertyName = "Age", Operation = Op.Equals, Value = 39, Conditional = ConOp.None }
            };

            // Act
            var deleg = ExpressionBuilder.GetExpression<Person>(filter).Compile();
            var filteredCollection = persons.Where(deleg).ToList() as List<Person>;

            // Assert
            Assert.AreEqual(3, filteredCollection.Count, "Record counts do not match");
        }

Open in new window

Now I want to use a class with a complex data type and I am having a problem. Here is what I have so far:

        public partial class EMP_ALIGNMENT
        {
            public decimal ALIGNMENTID { get; set; }
            public decimal DIVISIONID { get; set; }
            public decimal DEPARTMENTID { get; set; }
            public decimal GROUPID { get; set; }
            public decimal FUNCTIONID { get; set; }
            public Nullable<System.DateTime> LASTUPDATETIMESTAMP { get; set; }
            public string ACTIVESTATUSFLAG { get; set; }
        }
        public partial class EMP_CALLCENTER
        {
            public int CALLCENTERID { get; set; }
            public int EMPLOYEEID { get; set; }
            public Nullable<System.DateTime> PODDATE { get; set; }
            public Nullable<System.DateTime> FLOORDATE { get; set; }
            public Nullable<int> LANGUAGEID { get; set; }
            public Nullable<int> PRIMARYSYSTEMID { get; set; }
            public string DCTM_R_OBJECT_ID { get; set; }
            public Nullable<System.DateTime> LASTUPDATETIMESTAMP { get; set; }

            public virtual EMP_EMPLOYEE EMP_EMPLOYEE { get; set; }
        }
        public partial class EMP_EMPLOYEE
        {
            public EMP_EMPLOYEE()
            {
                this.EMP_IDS = new HashSet<EMP_IDS>();
            }

            public int EMPLOYEEID { get; set; }
            public string NT_ID { get; set; }
            public string FIRSTNAME { get; set; }
            public string LASTNAME { get; set; }
            public string NICKNAME { get; set; }
            public string EMAILADDRESS { get; set; }
            public string COSTCENTER { get; set; }
            public string CJACKNO { get; set; }
            public int TITLEID { get; set; }
            public Nullable<int> MANAGERSUPERVISORID { get; set; }
            public Nullable<int> APPROVALSUPERVISORID { get; set; }
            public int LOCATIONID { get; set; }
            public Nullable<int> SUBLOCATIONID { get; set; }
            public int ALIGNMENTID { get; set; }
            public int STATUSID { get; set; }
            public Nullable<System.DateTime> HIREDATE { get; set; }
            public Nullable<System.DateTime> PROMOTIONDATE { get; set; }
            public Nullable<int> PROMOTIONREASONID { get; set; }
            public Nullable<System.DateTime> TRANSFERDATE { get; set; }
            public Nullable<int> TRANSFERREASONID { get; set; }
            public Nullable<System.DateTime> TERMINATIONDATE { get; set; }
            public Nullable<int> TERMINATIONREASONID { get; set; }
            public Nullable<System.DateTime> LOASTARTDATE { get; set; }
            public Nullable<System.DateTime> LOAENDDATE { get; set; }
            public string TIMEZONE { get; set; }
            public Nullable<System.DateTime> LASTUPDATETIMESTAMP { get; set; }
            public string SUFFIX { get; set; }

            public virtual EMP_ALIGNMENT EMP_ALIGNMENT { get; set; }
            public virtual EMP_CALLCENTER EMP_CALLCENTER { get; set; }
            public virtual EMP_EMPLOYEEALIGNMENT EMP_EMPLOYEEALIGNMENT { get; set; }
            public virtual ICollection<EMP_IDS> EMP_IDS { get; set; }
        }
        public partial class EMP_EMPLOYEEALIGNMENT
        {
            public int EMPLOYEEID { get; set; }
            public int SUPERVISORID { get; set; }
            public int MANAGERID { get; set; }
            public int DIRECTORID { get; set; }
            public Nullable<System.DateTime> LASTUPDATETIMESTAMP { get; set; }
        }
        public partial class EMP_IDS
        {
            public int IDSID { get; set; }
            public int EMPLOYEEID { get; set; }
            public int IDTYPEID { get; set; }
            public string IDVALUE { get; set; }
            public Nullable<System.DateTime> LASTUPDATETIMESTAMP { get; set; }

            public virtual EMP_EMPLOYEE EMP_EMPLOYEE { get; set; }
        }

        public List<EMP_EMPLOYEE> PopulateEmployeeProfile()
        {
            List<EMP_EMPLOYEE> employee = new List<EMP_EMPLOYEE>
            {
                new EMP_EMPLOYEE { 
                    EMPLOYEEID = 5810, NT_ID = @"AIGM_WILM\JSMITH", FIRSTNAME = "JOHN", LASTNAME = "SMITH", NICKNAME = "JONNY", 
                    EMAILADDRESS = "JOHN.SMITH@21ST.COM", COSTCENTER = "21177A1010", CJACKNO = "14627", TITLEID = 38, MANAGERSUPERVISORID = 0, 
                    APPROVALSUPERVISORID = 231, LOCATIONID = 3, SUBLOCATIONID = 0, ALIGNMENTID = 727, STATUSID = 1, 
                    HIREDATE = Convert.ToDateTime("06/04/2012"), PROMOTIONDATE = Convert.ToDateTime("05/01/2014"), PROMOTIONREASONID = 23, 
                    TRANSFERDATE = Convert.ToDateTime("07/07/2014"), TRANSFERREASONID = 196, TERMINATIONDATE = null, TERMINATIONREASONID = 0, 
                    LOAENDDATE = null, LOASTARTDATE = null, SUFFIX = null, TIMEZONE = "EDT", 
                    EMP_ALIGNMENT = new EMP_ALIGNMENT { ALIGNMENTID = 727, DIVISIONID = 4, DEPARTMENTID = 63, GROUPID = 261, FUNCTIONID = 401 },
                    EMP_CALLCENTER = new EMP_CALLCENTER { CALLCENTERID = 1, EMPLOYEEID = 5810, PODDATE = Convert.ToDateTime("06/04/2012"),
                                                          FLOORDATE = Convert.ToDateTime("09/04/2012"), LANGUAGEID = 1, PRIMARYSYSTEMID = 1,
                                                          DCTM_R_OBJECT_ID = null 
                    },
                    EMP_EMPLOYEEALIGNMENT = new EMP_EMPLOYEEALIGNMENT { EMPLOYEEID = 5810, SUPERVISORID = 231, MANAGERID = 5201, DIRECTORID = 399 },
                    EMP_IDS = new List<EMP_IDS> {
                                                    new EMP_IDS { IDSID = 22270, EMPLOYEEID = 5810, IDTYPEID = 2, IDVALUE = "0162161" },
                                                    new EMP_IDS { IDSID = 22272, EMPLOYEEID = 5810, IDTYPEID = 4, IDVALUE = "70162161" },
                                                    new EMP_IDS { IDSID = 22273, EMPLOYEEID = 5810, IDTYPEID = 8, IDVALUE = "32381" },
                                                    new EMP_IDS { IDSID = 25001, EMPLOYEEID = 5810, IDTYPEID = 5, IDVALUE = "23" },
                                                    new EMP_IDS { IDSID = 24867, EMPLOYEEID = 5810, IDTYPEID = 69, IDVALUE = "Y" }
                                                }
               }
            };
            return employee;
        }

        [TestMethod]
        public void Returns_One_Record_When_Empoyee_Supervisorid_Is_Specified()
        {
            // Arrange
            List<EMP_EMPLOYEE> emp = PopulateEmployeeProfile();

            List<Filter> filter = new List<Filter>()
            {
                new Filter { PropertyName = "EMP_EMPLOYEEALIGNMENT.SUPERVISORID", Operation = Op.Equals, Value = 231, Conditional = ConOp.None },
            };

            // Act
            var deleg = ExpressionBuilder.GetExpression<EMP_EMPLOYEE>(filter).Compile();
            var filteredCollection = emp.Where(deleg).ToList() as List<EMP_EMPLOYEE>;
 
            // Assert
            Assert.AreEqual(0, filteredCollection.Count, "Record counts do not match");
        }

Open in new window

When I run the test I get the following error:

Test method ExpressionBuilderClassTests.ExpressionBuilderTests.Returns_One_Record_When_Empoyee_Supervisorid_Is_Specified threw exception: 
System.ArgumentException: Instance property 'EMP_EMPLOYEEALIGNMENT.SUPERVISORID' is not defined for type 'ExpressionBuilderClassTests.ExpressionBuilderTests+EMP_EMPLOYEE'

Open in new window

Here is the line of code that is throwing the error:

private static Expression GetExpression<T>(ParameterExpression param, Filter filter)
        {
            MemberExpression member = Expression.Property(param, filter.PropertyName);

Open in new window

If I set PropertyName = "EmployeeID" it works. I appreciate any help in trying to figure this out.

Bob Learned

That looks like code from CodeProject:

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

dyarosh

ASKER

It is but it doesn't handle complex data types.

Bob Learned

I can't quite get enough information from the code that you posted.

What is "ExpressionBuilderClassTests.ExpressionBuilderTests"?

Open in new window

Can you show me one expression that you are working with?

Open in new window

dyarosh

ASKER

Here is the ExpressionBuilder Class (I got it from CodeProject and modified it to add more operations and to allow for OR conditions)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Linq.Expressions;

namespace ExpressionBuilderClass
{
    /// <summary>
    /// Creates a dynamic filter that can be used in a Linq statement
    /// Example:
    ///  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 = ExpressionBuilder.GetExpression<Person>(filter).Compile();
    ///  var filteredCollection = persons.Where(deleg).ToList();
    /// </summary>
    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;
            ConOp prevConOp;
           
            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);
                    // Save Conditional Operation to be used when there are more than 2 conditions
                    prevConOp = f2.Conditional;
                    filters.Remove(f2);

                    if (filters.Count == 1)
                    {
                        switch (prevConOp)
                        {
                            case ConOp.And:
                                exp = Expression.AndAlso(exp, GetExpression<T>(param, filters[0]));
                                break;
                            case ConOp.Or:
                                exp = Expression.OrElse(exp, GetExpression<T>(param, filters[0]));
                                break;
                            default:
                                throw new InvalidFilterCriteriaException("Invalid Conditional Specified in Filter");
                        }
                        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.NotEqual:
                    return Expression.NotEqual(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);

            switch (filter1.Conditional)
            {
                case ConOp.And:
                    return Expression.AndAlso(bin1, bin2);
                case ConOp.Or:
                    return Expression.OrElse(bin1, bin2);
                default:
                    throw new InvalidFilterCriteriaException("Invalid Conditional Specified in Filter");
            }
        }
    }

    public class Filter
    {
        public string PropertyName { get; set; }
        public Op Operation { get; set; }
        public object Value { get; set; }
        public ConOp Conditional { get; set; }
    }

    public enum Op
    {
        Equals,
        NotEqual,
        GreaterThan,
        LessThan,
        GreaterThanOrEqual,
        LessThanOrEqual,
        Contains,
        StartsWith,
        EndsWith
    }

    public enum ConOp
    {
        And,
        Or,
        None
    }
}

Open in new window

If I don't access a complex property then the ExpressionBuilder works. The following Unit Test is successful:

        [TestMethod]
        public void Returns_One_Record_When_Empoyeeid_Is_5810()
        {
            // Arrange
            List<EMP_EMPLOYEE> emp = PopulateEmployeeProfile();

            List<Filter> filter = new List<Filter>()
            {
                new Filter { PropertyName = "EMPLOYEEID", Operation = Op.Equals, Value = 5810, Conditional = ConOp.None },
            };

            // Act
            var deleg = ExpressionBuilder.GetExpression<EMP_EMPLOYEE>(filter).Compile();
            var filteredCollection = emp.Where(deleg).ToList() as List<EMP_EMPLOYEE>;

            // Assert
            Assert.AreEqual(1, filteredCollection.Count, "Record counts do not match");
        }

Open in new window

When I try and access a complex property such as

            List<Filter> filter = new List<Filter>()
            {
                new Filter { PropertyName = "EMP_EMPLOYEEALIGNMENT.SUPERVISORID", Operation = Op.Equals, Value = 231, Conditional = ConOp.None },
            };

Open in new window

I get the error as stated in my post.

Bob Learned

The problem that I see is that it is trying to look for a property with the name 'EMP_EMPLOYEEALIGNMENT.SUPERVISORID', which doesn't exist. You would need to access property 'EMP_EMPLOYEEALIGNMENT', and then 'SUPERVISORID'. That requires splitting up the operation. It might be possible to split the string by '.', and then work on each part separately.

dyarosh

ASKER

When you say work on each part separately, what do you mean? Some of the complex properties could be moved into the EMP_Employee definition because they are 1 : 1 relationships (i.e. only 1 EMP_CallCenter record for each EMP_Employee record) but how would I work with the properties that have multiple records such as EMP_IDS (i.e. an employee can have many ids associated with them)?

If I wanted to find all the employees that have a SupervisorID = 123 and an IDTypeID = 456 how would you suggest breaking it up?

ASKER CERTIFIED SOLUTION

Bob Learned

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dyarosh

ASKER

This looks like it will work for me. Thank you!!