locked
How to generate where clause dynamically from xml data when using entity framework RRS feed

  • Question

  • see a sample EF query where we specify the fields name

    var data =entity.Customers.Where(i=>i.MobileNumber==12321

    && i.Nmae=="abc").firstordefault();

    but if i need to generate where clause from xml data then how could i do it ?

    suppose my xml look like

    DECLARE @xmlvar xml          
    SET @xmlvar='          
    <NewDataSet>          
      <param>          
        <SearchField>ID</SearchField>          
        <FilterCondition>=</FilterCondition>          
        <ConditionData>4000</ConditionData>          
        <MatchCase>0</MatchCase>          
        <Table>Orders</Table>          
      </param>          
      <param>          
        <SearchField>CustName</SearchField>          
        <FilterCondition>=</FilterCondition>          
        <ConditionData>Nigel Graham</ConditionData>          
        <MatchCase>0</MatchCase>          
        <Table>BBAJobs</Table>          
      </param>          
    </NewDataSet>'  

    there are multiple data in xml for filter purpose.

    1) SearchField is field name

    2) FilterCondition is operator we use in sql like == or <>, % etc

    3) ConditionData is value which we would search in table

    4) MatchCase 0 or 1 indicate search will be case sensitive or not for string data type.

    looking for sample EF query with dynamically generated where clause with multiple filters from xml data. thanks.

    Friday, August 12, 2016 12:45 PM

Answers

  • Hi Mou_inn,

    We could use Linq to xml to retrieve related filter parameters, then use expression to generate where clause dynamically. and I create a demo as below for your reference.

    #related common handle class.

    using System;
    using System.Collections.Generic;
    using System.Linq.Expressions;
    using System.Reflection;
    
    namespace DynamicFilterDemo
    {
        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);
            }
        }
    
        public enum Op
        {
            Equals,
            GreaterThan,
            LessThan,
            GreaterThanOrEqual,
            LessThanOrEqual,
            Contains,
            StartsWith,
            EndsWith
        }
    
        public class Filter
        {
            public string PropertyName { get; set; }
            public Op Operation { get; set; }
            public object Value { get; set; }
        }
    }
    

    #Usage:

    string xmlString = @"          
                    <NewDataSet>
                      <param>
                        <SearchField>FirstName</SearchField>
                        <FilterCondition>%</FilterCondition>
                        <ConditionData>W</ConditionData>
                        <MatchCase>0</MatchCase>
                        <Table>Customers</Table>
                      </param>
                      <param>
                        <SearchField>LastName</SearchField>
                        <FilterCondition>=</FilterCondition>
                        <ConditionData>Cole</ConditionData>
                        <MatchCase>0</MatchCase>
                        <Table>Customers</Table>
                      </param>
                      <param>
                        <SearchField> CustName </SearchField>
                        <FilterCondition>=</FilterCondition>
                        <ConditionData> Nigel Graham</ConditionData>
                           <MatchCase>0</MatchCase>
                           <Table>BBAJobs </Table>
                         </param>
                       </NewDataSet>";
    
                XElement xe = XElement.Parse(xmlString, LoadOptions.SetLineInfo);
                List<XElement> ListElement = xe.Descendants("param").Where(x => x.Element("Table").Value == "Customers").ToList();
                List<Filter> list = new List<Filter>();
    
                foreach (XElement x in ListElement)
                {
                    Filter filter = new Filter();
                    filter.PropertyName = x.Element("SearchField").Value;
                    filter.Value = x.Element("ConditionData").Value;
                    switch (x.Element("FilterCondition").Value)
                    {
                        case "=":
                            filter.Operation = Op.Equals;
                            break;
                        case "%":
                            filter.Operation = Op.Contains;
                            break;
                        default:
                            break;
                    }
    
                    list.Add(filter);
                }
                Expression<Func<Customer, bool>> deleg = ExpressionBuilder.GetExpression<Customer>(list);
                using (var db = new EFDemoContext())
                {
                    var query = db.Customers.Where(deleg);
                }

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Sudip_inn Tuesday, August 16, 2016 12:25 PM
    Monday, August 15, 2016 3:14 AM

All replies

  • http://www.c-sharpcorner.com/UploadFile/c42694/dynamic-query-in-linq-using-predicate-builder/

    Maybe it will help you.

    Monday, August 15, 2016 1:55 AM
  • Hi Mou_inn,

    We could use Linq to xml to retrieve related filter parameters, then use expression to generate where clause dynamically. and I create a demo as below for your reference.

    #related common handle class.

    using System;
    using System.Collections.Generic;
    using System.Linq.Expressions;
    using System.Reflection;
    
    namespace DynamicFilterDemo
    {
        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);
            }
        }
    
        public enum Op
        {
            Equals,
            GreaterThan,
            LessThan,
            GreaterThanOrEqual,
            LessThanOrEqual,
            Contains,
            StartsWith,
            EndsWith
        }
    
        public class Filter
        {
            public string PropertyName { get; set; }
            public Op Operation { get; set; }
            public object Value { get; set; }
        }
    }
    

    #Usage:

    string xmlString = @"          
                    <NewDataSet>
                      <param>
                        <SearchField>FirstName</SearchField>
                        <FilterCondition>%</FilterCondition>
                        <ConditionData>W</ConditionData>
                        <MatchCase>0</MatchCase>
                        <Table>Customers</Table>
                      </param>
                      <param>
                        <SearchField>LastName</SearchField>
                        <FilterCondition>=</FilterCondition>
                        <ConditionData>Cole</ConditionData>
                        <MatchCase>0</MatchCase>
                        <Table>Customers</Table>
                      </param>
                      <param>
                        <SearchField> CustName </SearchField>
                        <FilterCondition>=</FilterCondition>
                        <ConditionData> Nigel Graham</ConditionData>
                           <MatchCase>0</MatchCase>
                           <Table>BBAJobs </Table>
                         </param>
                       </NewDataSet>";
    
                XElement xe = XElement.Parse(xmlString, LoadOptions.SetLineInfo);
                List<XElement> ListElement = xe.Descendants("param").Where(x => x.Element("Table").Value == "Customers").ToList();
                List<Filter> list = new List<Filter>();
    
                foreach (XElement x in ListElement)
                {
                    Filter filter = new Filter();
                    filter.PropertyName = x.Element("SearchField").Value;
                    filter.Value = x.Element("ConditionData").Value;
                    switch (x.Element("FilterCondition").Value)
                    {
                        case "=":
                            filter.Operation = Op.Equals;
                            break;
                        case "%":
                            filter.Operation = Op.Contains;
                            break;
                        default:
                            break;
                    }
    
                    list.Add(filter);
                }
                Expression<Func<Customer, bool>> deleg = ExpressionBuilder.GetExpression<Customer>(list);
                using (var db = new EFDemoContext())
                {
                    var query = db.Customers.Where(deleg);
                }

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Sudip_inn Tuesday, August 16, 2016 12:25 PM
    Monday, August 15, 2016 3:14 AM