Tags

  • XML extension for entity framework

    by Frans van Ek | Nov 12, 2016

    XML field Extension for Entity framework

    The goal of this exercise is to investigate if it’s possible to get a typed selector on a XML field.

    In SQl there is a fieldtype XML. In this field it’s possible to store a serialized C# object. This mkes extentions on the data very easy but there is a down side to this. Query the field isn’t that straight forward. It will result in a string xml path selection. For instance:

    ((XMLContent.query('/SimpleItemSubObject/ID').value('.', 'bigint')  =  636142127126573004)


    So far it isn’t possible to make the query type strongly typed. And as I far as I could find it will not be implemented in the near future. So is it possible to do so ourselves?

    Final result has to be something like this:
     XMLFilter.Filter(x => x.ID, 636142127126573004, eCompairType.equals)

                    .And(x => x.Test.Name, "demo", eCompairType.less)

                    .And(x => x.Test.Time, DateTime.Now, eCompairType.less);


    And the apply it to the context

    var whereStatement = XMLFilter`

    .SetXMLColumnName<SimpleItem>(x => x.XMLContent)

    .GetQueryWhereClause();

      var db = new XMLTestContext();

      var items = GetItems<SimpleItem>(db.SimpleItems, whereStatement).ToList(); 

    So let’s get started:

    First we need to get a filter starting point. This is a generic type so we can reuse it for all types restricted the type is a class.

    public class XMLFilter<T> where T : class     { }


    In order to create a flexible selector there must be a possibility for and and or constructions. The way it will be implemented is to start with a or collection and place al the and filters in a or selection. This is similar to the way sql management studio works. Columns as or parts and rows inside column as And parts.

    public List<OrPart<T>> OrParts { get; set; } 


    The column on wich the filter must be applied;

     public List<OrPart<T>> OrParts { get; set; }

    public string XMlColumnName { get; private set; }


    to get the whereclause , there is an override on the ToString() function.

     

    public override string ToString()

            {

                var result = string.Empty;

                var orstring = string.Empty;

                foreach (var item in OrParts)

                {

                    result = string.Format("{0}{1}{2}"

    , result

    , orstring

    , item.ToString());

                    orstring = " or ";

                }

                return string.Format(result, XMlColumnName);

            }

     

            public string GetQueryWhereClause()

            {

                return this.ToString();

            }

     

    The override of the ToString() function will collect all the orparts and place the in a string separated bij the or statement. So the orpart will also have a override on the to string function to get this part of the filterstring.

    The hard part.

    Now in order to set the filter based upon the types properties there is a helper created. This helper is a static class to handle a lamda expression and make a kind of reflection on the property to get the correct xmlstring representation of the property. So how does this work?

     

    public OrPart<T> Filter(Expression<Func<T, object>> lambda, object value, eCompairType compairtype)

            {

                return Filter(PropertyFilterHelper<T>.GetPropertyInfo(lambda), value, compairtype);

            }



    First the xmlFilter has a function wich can handle a Lambda expression. This is done with a func. This func along with the filter value and the compairison type will be handled by the helper.

     

    public static PropertyFilter GetPropertyFilter(Expression<Func<T, object>> lambda, object value, eCompairType compairtype)

            {

                return new PropertyFilter { PropertyReference = GetPropertyInfo(lambda), Value = value, CompairType = compairtype };

            }


    Now the function has to translate the lamdaexpression to a propertyFilter. In this filter there is a list of properties. When the targeted propery is several levels deep into the object (with classes in classes by inheritance) the filter must name all the levels back to the rootobject in order to create the correct filter string for the xml.

    Building this list is the responsibility of the lambda analyser.

    This is a recursive method, to get to the root object.

     

    internal class LambaAnalyser<T>

        {

            private Expression<Func<T, object>> lambda;

            private List<PropertyInfo> result;

     

            public LambaAnalyser(Expression<Func<T, object>> lambda)

            {

                this.lambda = lambda;

                ClearResult();

            }

     

            internal IEnumerable<PropertyInfo> GetExpressionList()

            {

                if (lambda.Body.NodeType == ExpressionType.Convert)

                {

                    return GetPropertyListfor((lambda.Body as UnaryExpression).Operand as MemberExpression);

                }

     

                return GetPropertyListfor(lambda.Body as MemberExpression);

     

            }

     

            private IEnumerable<PropertyInfo> GetPropertyListfor(MemberExpression body)

            {

                ClearResult();

                if (body != null && body.Expression != null)

                {

                    AddPropertyInfoList(body);

                }

                return result;

            }

     

            private void ClearResult()

            {

                this.result = new List<PropertyInfo>();

            }

     

            private void AddPropertyInfoList(MemberExpression expression)

            {

                result.AddRange(GetPropertyListfor(expression.Expression as MemberExpression));

                result.Add((expression as MemberExpression).Member as PropertyInfo);

            }

     


    To get the list a bit more flexible it is wrapped in a properyrefence class.

    public class PropertyReference

        {

            public PropertyReference()

            {

                SetDefaults();

            }

     

            private void SetDefaults()

            {

                Properties = new List<PropertyInfo>();

            }

     

            public List<PropertyInfo> Properties { get; private set; }

        }


     

     

    So now we have a method that collects all the properties back to root, from the reference specified in the lambda expression. One small trick. Due to the structure of the Func function there is the possibility that the lambda will add an convert function in which the property reference is wrapped. So there is a if statement to deal with this.

    The homestretch

     

    Now we can find the properties in the lambda expression. And we can place them in a orpart we have make it possible to add items to the andparts within the orparts.

    Within this andpart there is a collection of propertyfilters to specifiy each filter.

    Within these propertyfilters the is some logic to convert types to the right sql represention.

    And that’s all.

    Let wrap it into a nuget bundle and share it.

     

    A few samples

    Entity in EF

    public class SimpleItem

        {

            public int Id { get; set; }

     

            public string Text { get; set; }

     

            [Column(TypeName = "xml")]

            [EditorBrowsable(EditorBrowsableState.Never)]

            [DebuggerBrowsable(DebuggerBrowsableState.Never)]

            public string XMLContent { get; set; }

     

     

            public SimpleItemSubObject GetSubObject()

            {

                return XMLContent.ConverToObject<SimpleItemSubObject>();

            }

     

     

            public SimpleItem SetSubObject(SimpleItemSubObject value)

            {

                XMLContent = value.ConvertToXmlString<SimpleItemSubObject>();

                return this;

            }

        }

     

     

     
     

    XML content base

    public class SimpleItemSubObject

        {

            public long ID { get; set; }

            public string Name { get; set; }

            public DateTime Date { get; set; }

     

            public long getal { get; set; }

            public TestObject Test { get; set; }

        }

     


     

    Subobject

     

     

     public class TestObject

        {

            public long ID { get; set; }

            public string Name { get; set; }

            public DateTime Time { get; set; }

        }

       


     

    Filters:

    Sample 1

     

    var XMLFilter = new XMLFilter<SimpleItemSubObject>();

     

                XMLFilter.Filter(x => x.ID, 636142127126573004, eCompairType.equals)

                    .And(x => x.Test.Name, "demo", eCompairType.less)

                    .And(x => x.Test.Time, DateTime.Now, eCompairType.less);

     

                var result = XMLFilter.SetXMLColumnName<SimpleItem>(x => x.XMLContent).GetQueryWhereClause();

     


    Sample 2

     

     

    var XMLFilter = new XMLFilter<SimpleItemSubObject>();

     

                XMLFilter.Filter(x => x.Test.Time, DateTime.Now.AddDays(-1), eCompairType.greater);

     

                var whereStatement = XMLFilter.SetXMLColumnName<SimpleItem>(x => x.XMLContent).GetQueryWhereClause();

                var db = new XMLTestContext();

                var items = GetItems<SimpleItem>(db.SimpleItems, whereStatement).Take(1).ToList();

     

        private DbSqlQuery<T> GetItems<T>(DbSet<T> dbset, string whereStatement) where T :class

            {

                var tsql = string.Format("{0} where {1} ", dbset.ToString(), whereStatement);

                return dbset.SqlQuery(tsql);

            }


     

     

     

     

     

     

     

     

     

    163 Comments

Sitefinity Web Content Management