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.
dyaroshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
0
dyaroshAuthor Commented:
It is but it doesn't handle complex data types.
0
Bob LearnedCommented:
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

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

dyaroshAuthor Commented:
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.
0
Bob LearnedCommented:
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.
0
dyaroshAuthor Commented:
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?
0
Bob LearnedCommented:
Here is another approach:

1) Start with Scott Guthrie's dynamic LINQ library for 2008:
http://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library

2) Learn that there is an updated library for .NET 4.0 projects:

System.Linq.Dynamic
https://github.com/kahanu/System.Linq.Dynamic

3) Add the NuGet package for System.Linq.Dynamic

4) Build a helper function:

using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic;

// Add NuGet package System.Linq.Dynamic
// http://dynamiclinq.codeplex.com

namespace LinqLayer
{
    public class LinqService
    {

        public static List<TElement> FilterList<TElement>(List<TElement> list, string whereClause)
        {
            var result = list.AsQueryable().Where(whereClause).ToList();

            return result;
        }
    }
}

Open in new window


5) Sample usage:

var result = LinqService.FilterList<Person>(people, "SupervisorID = 123 and an IDTypeID = 456");

Open in new window


6) Documentation here:

Documentation and Samples
http://msdn.microsoft.com/en-US/vstudio/bb894665.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dyaroshAuthor Commented:
This looks like it will work for me.  Thank you!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.