• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

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.
0
dyarosh
Asked:
dyarosh
  • 4
  • 4
1 Solution
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
dyaroshAuthor Commented:
This looks like it will work for me.  Thank you!!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now