none
Custom LinqQueryDataSource RRS feed

  • Question

  • Hi,

    I have been trying to develop a custom data source which will accept the results of a Linq Query and support server side paging and sorting.

    The code I have so far is below and all is working pretty good apart from one point. When using SQL server profiler I am not getting the 'ROW_NUMBER() OVER' I expect which indicates paging is occurring on the server. I suspect I am doing something at some point which is causing the query not to be deferred but from stepping over my code and using profiler this doesn't appear to be the case so I am stumped!

    The code is as follows: 

    Code Snippet

    using System;
    using System.Data;
    using System.Configuration;
    using System.Linq;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.HtmlControls;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Xml.Linq;
    using System.Collections;
    using System.Collections.Generic;
    using System.Linq.Expressions;
    using System.Reflection;

    /// <summary>
    /// Summary description for LinqQueryDataSource
    /// </summary>
    ///

    namespace Inflecto.Web.UI.WebControls
    {

        public class LinqQueryDataSource : DataSourceControl
        {
            private LinqQueryDataSourceView view;

            private IQueryable query;
            public IQueryable LinqQuery
            {
                get {return query;}
                set {query = value;}
            }

            public LinqQueryDataSource() : base() { }

            protected override DataSourceView GetView(string viewName)
            {
                // We only have one view... If it has not been created then create it else return
                if (view == null)
                    view = new LinqQueryDataSourceView(this, string.Empty, query);

                return view;
            }


            protected override ICollection GetViewNames() {
            ArrayList al = new ArrayList(1);
            al.Add(LinqQueryDataSourceView.DefaultViewName);
            return al as ICollection;


        }

            public class LinqQueryDataSourceView : DataSourceView
            {
                IQueryable originalQuery;
                string previousSortExpression;

                public static string DefaultViewName = "LinqQueryView";

                public LinqQueryDataSourceView(IDataSource owner, string name, IQueryable query)
                    : base(owner, DefaultViewName)
                {
                    originalQuery = query;
                    previousSortExpression = string.Empty;
                }

                public override bool CanPage
                {
                    get {return true;}

                }
                public override bool CanSort
                {
                    get{return true;}

                }
                public override bool CanRetrieveTotalRowCount
                {
                    get{return true;}
                }
               
                protected override IEnumerable ExecuteSelect(DataSourceSelectArguments arguments)
                {
                    if (originalQuery == null)
                    {
                        throw new Exception("LinqQueryDataSource must have LinqQuery set");
                    }

                    // Grab types needed for reflection
                    IQueryable thisQuery = originalQuery;
                    Type dataSourceType = thisQuery.GetType();
                    Type dataItemType = getDataItemType(thisQuery, dataSourceType);

                   
                    // Set total Row Count Property
                    if (arguments.RetrieveTotalRowCount)
                        arguments.TotalRowCount = getTotalRowCount(thisQuery, dataItemType, dataSourceType);
                   
                   

                    // If sorting requested perform this and get deferred query back
                    if (arguments.SortExpression != string.Empty)
                        thisQuery = performSort(thisQuery, arguments.SortExpression, dataSourceType, dataItemType);             

                    // If Paging requested perform paging
                    if (arguments.MaximumRows !=0)
                        thisQuery = performPaging(thisQuery, arguments.StartRowIndex, arguments.MaximumRows,
                            dataItemType, dataSourceType);

               
                    // Convert IQueryable to list as we need something that implements ICollection
                    // to stop the gridview whineing         

                    Type listCasterType = typeof(GenericListCaster<>).MakeGenericType(dataItemType);
                    object listCasterObject = Activator.CreateInstance(listCasterType);

                    return (IEnumerable) listCasterType.GetMethod("ToList", new Type[] { dataSourceType})
                        .Invoke(listCasterObject, new object[] { thisQuery });
                        
                }

                private IQueryable performSort(IQueryable thisQuery, string sortExpression, Type dataSourceType, Type dataItemType)
                {
                    SortDirection sortDirection;

                    if (sortExpression.EndsWith(" DESC"))
                    {
                        sortDirection = SortDirection.Descending;
                        sortExpression = sortExpression.Substring(0, sortExpression.IndexOf(" DESC"));
                    }
                    else
                    {
                        sortDirection = SortDirection.Ascending;
                    }

                    object sorterObject = null;

                    Type sorterType = null;

                    PropertyInfo property = dataItemType.GetProperty(sortExpression);

                    sorterType = typeof(GenericSorter<,>).MakeGenericType(dataItemType, property.PropertyType);

                    sorterObject = Activator.CreateInstance(sorterType);

                   return (IQueryable)sorterType.GetMethod("Sort", new Type[] { dataSourceType, typeof(string), typeof(SortDirection) })
                            .Invoke(sorterObject, new object[] { thisQuery, sortExpression, sortDirection });

                }

                private IQueryable performPaging(IQueryable thisQuery, int startRowIndex, int maximumRows, Type dataItemType, Type dataSourceType)
                {
                    Type pagerType = typeof(GenericPager<>).MakeGenericType(dataItemType);
                    object pagerObject = Activator.CreateInstance(pagerType);

                    return (IQueryable)pagerType.GetMethod("Page", new Type[] { dataSourceType, typeof(int), typeof(int) })
                        .Invoke(pagerObject, new object[] { thisQuery, startRowIndex, maximumRows });
                }

                private Type getDataItemType(IEnumerable dataSource, Type dataSourceType)
                {
                    Type dataItemType = typeof(object);

                    if (dataSourceType.HasElementType)
                    {
                        dataItemType = dataSourceType.GetElementType();
                    }
                    else if (dataSourceType.IsGenericType)
                    {
                        dataItemType = dataSourceType.GetGenericArguments()[0];
                    }
                    else if (dataSource is IEnumerable)
                    {
                        IEnumerator dataEnumerator = dataSource.GetEnumerator();

                        if (dataEnumerator.MoveNext() && dataEnumerator.Current != null)
                        {
                            dataItemType = dataEnumerator.Current.GetType();
                        }
                    }

                    return dataItemType;
                }

                private int getTotalRowCount(IQueryable thisQuery, Type dataItemType, Type dataSourceType)
                {
                    Type rowCounterType = typeof(GenericRowCounter<>).MakeGenericType(dataItemType);
                    object rowCounterObject = Activator.CreateInstance(rowCounterType);

                    return (int)rowCounterType.GetMethod("CountRows", new Type[] { dataSourceType })
                        .Invoke(rowCounterObject, new object[] { thisQuery });
                }
            }
        }

        public class GenericListCaster<T>
        {
            public List<T> ToList(IQueryable source)
            {

                return source.OfType<T>().AsQueryable<T>().ToList();
            }
        }

        public class GenericRowCounter<T>
        {
            public int CountRows(IQueryable source)
            {
                return source.OfType<T>().AsQueryable<T>().Count<T>();
            }
        }



        public class GenericPager<T>
        {
            public IEnumerable<T> Page(IEnumerable source, int pageIndex, int pageSize)
            {
                return source.OfType<T>().AsQueryable<T>().Skip<T>(pageIndex).Take<T>(pageSize);
            }
        }

        public class GenericSorter<T, PT>
        {
            public IEnumerable<T> Sort(IEnumerable source, string sortExpression, SortDirection sortDirection)
            {
                var param = Expression.Parameter(typeof(T), "item");

                var sortLambda = Expression.Lambda<Func<T, PT>>(Expression.Convert(Expression.Property(param, sortExpression), typeof(PT)), param);

                if (sortDirection == SortDirection.Descending)
                {
                    return source.OfType<T>().AsQueryable<T>().OrderByDescending<T, PT>(sortLambda);
                }
                else
                {

                    return source.OfType<T>().AsQueryable<T>().OrderBy<T, PT>(sortLambda);
                }
            }

            public IEnumerable<T> Sort(IEnumerable<T> source, string sortExpression, SortDirection sortDirection)
            {
                var param = Expression.Parameter(typeof(T), "item");

                var sortLambda = Expression.Lambda<Func<T, PT>>(Expression.Convert(Expression.Property(param, sortExpression), typeof(PT)), param);

                if (sortDirection == SortDirection.Descending)
                {
                    return source.AsQueryable<T>().OrderByDescending<T, PT>(sortLambda);
                }
                else
                {

                    return source.AsQueryable<T>().OrderBy<T, PT>(sortLambda);
                }
            }
        }

    }



    Thanks in advance if anyone can help!



    Thursday, August 14, 2008 2:30 PM

Answers

  • Hi Howard,

    Thanks for your reply. I did not want to use the LINQ datasource as I want to actually want to be able to pass around LINQ queries in the IQueryable form to perform further deferred actions on them.

    I had forgotten I had left this thread open but I actually found the answer in the meantime. Basically passing the query in to the generic sorting and paging methods as IEnumerable causes the query to execute breaking the intended deferred execution. Changing these to IQueryable solves the situation and gives a LINQ datasource that accepts queries in the IQueryable form.

    In case anyone is interest the full working code looks like this:

    using System;
    using System.Linq;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Collections;
    using System.Collections.Generic;
    using System.Linq.Expressions;
    using System.Reflection;

    /// <summary>
    /// Summary description for LinqQueryDataSource
    /// </summary>
    ///

    namespace Inflecto.Web.UI
    {

        public class LinqQueryDataSource : DataSourceControl
        {
            private LinqQueryDataSourceView view;

            private IQueryable query;
            public IQueryable LinqQuery
            {
                get {return query;}
                set {query = value;}
            }

            public LinqQueryDataSource() : base() { }

            protected override DataSourceView GetView(string viewName)
            {
                // We only have one view... If it has not been created then create it else return
                if (view == null)
                    view = new LinqQueryDataSourceView(this, string.Empty, query);

                return view;
            }


            protected override ICollection GetViewNames() {
            ArrayList al = new ArrayList(1);
            al.Add(LinqQueryDataSourceView.DefaultViewName);
            return al as ICollection;


        }

            public class LinqQueryDataSourceView : DataSourceView
            {
                IQueryable originalQuery;
                string previousSortExpression;

                public static string DefaultViewName = "LinqQueryView";

                public LinqQueryDataSourceView(IDataSource owner, string name, IQueryable query)
                    : base(owner, DefaultViewName)
                {
                    originalQuery = query;
                    previousSortExpression = string.Empty;
                }

                public override bool CanPage
                {
                    get {return true;}

                }
                public override bool CanSort
                {
                    get{return true;}

                }
                public override bool CanRetrieveTotalRowCount
                {
                    get{return true;}
                }
               
                protected override IEnumerable ExecuteSelect(DataSourceSelectArguments arguments)
                {
                    if (originalQuery == null)
                    {
                        throw new Exception("LinqQueryDataSource must have LinqQuery set");
                    }

                    // Grab types needed for reflection
                    IQueryable thisQuery = originalQuery;
                    Type dataSourceType = thisQuery.GetType();
                    Type dataItemType = getDataItemType(thisQuery, dataSourceType);

                   
                    // Set total Row Count Property
                    if (arguments.RetrieveTotalRowCount)
                        arguments.TotalRowCount = getTotalRowCount(thisQuery, dataItemType, dataSourceType);
                   
                   

                    // If sorting requested perform this and get deferred query back
                    if (arguments.SortExpression != string.Empty)
                        thisQuery = performSort(thisQuery, arguments.SortExpression, dataSourceType, dataItemType);             

                    // If Paging requested perform paging
                    if (arguments.MaximumRows !=0)
                        thisQuery = performPaging(thisQuery, arguments.StartRowIndex, arguments.MaximumRows,
                            dataItemType, dataSourceType);

               
                    // Convert IQueryable to list as we need something that implements ICollection
                    // to stop the gridview whineing         

                    Type listCasterType = typeof(GenericListCaster<>).MakeGenericType(dataItemType);
                    object listCasterObject = Activator.CreateInstance(listCasterType);

                    return (IEnumerable) listCasterType.GetMethod("ToList", new Type[] { dataSourceType})
                        .Invoke(listCasterObject, new object[] { thisQuery });
                        
                }

                private IQueryable performSort(IQueryable thisQuery, string sortExpression, Type dataSourceType, Type dataItemType)
                {
                    SortDirection sortDirection;

                    if (sortExpression.EndsWith(" DESC"))
                    {
                        sortDirection = SortDirection.Descending;
                        sortExpression = sortExpression.Substring(0, sortExpression.IndexOf(" DESC"));
                    }
                    else
                    {
                        sortDirection = SortDirection.Ascending;
                    }

                    object sorterObject = null;

                    Type sorterType = null;

                    PropertyInfo property = dataItemType.GetProperty(sortExpression);

                    sorterType = typeof(GenericSorter<,>).MakeGenericType(dataItemType, property.PropertyType);

                    sorterObject = Activator.CreateInstance(sorterType);

                   return (IQueryable)sorterType.GetMethod("Sort", new Type[] { dataSourceType, typeof(string), typeof(SortDirection) })
                            .Invoke(sorterObject, new object[] { thisQuery, sortExpression, sortDirection });

                }

                private IQueryable performPaging(IQueryable thisQuery, int startRowIndex, int maximumRows, Type dataItemType, Type dataSourceType)
                {
                    Type pagerType = typeof(GenericPager<>).MakeGenericType(dataItemType);
                    object pagerObject = Activator.CreateInstance(pagerType);

                    return (IQueryable)pagerType.GetMethod("Page", new Type[] { dataSourceType, typeof(int), typeof(int) })
                        .Invoke(pagerObject, new object[] { thisQuery, startRowIndex, maximumRows });
                }

                private Type getDataItemType(IEnumerable dataSource, Type dataSourceType)
                {
                    Type dataItemType = typeof(object);

                    if (dataSourceType.HasElementType)
                    {
                        dataItemType = dataSourceType.GetElementType();
                    }
                    else if (dataSourceType.IsGenericType)
                    {
                        dataItemType = dataSourceType.GetGenericArguments()[0];
                    }
                    else if (dataSource is IEnumerable)
                    {
                        IEnumerator dataEnumerator = dataSource.GetEnumerator();

                        if (dataEnumerator.MoveNext() && dataEnumerator.Current != null)
                        {
                            dataItemType = dataEnumerator.Current.GetType();
                        }
                    }

                    return dataItemType;
                }

                private int getTotalRowCount(IQueryable thisQuery, Type dataItemType, Type dataSourceType)
                {
                    Type rowCounterType = typeof(GenericRowCounter<>).MakeGenericType(dataItemType);
                    object rowCounterObject = Activator.CreateInstance(rowCounterType);

                    return (int)rowCounterType.GetMethod("CountRows", new Type[] { dataSourceType })
                        .Invoke(rowCounterObject, new object[] { thisQuery });
                }
            }
        }

        public class GenericListCaster<T>
        {
            public List<T> ToList(IQueryable source)
            {

                return source.OfType<T>().AsQueryable<T>().ToList<T>();
            }
        }

        public class GenericRowCounter<T>
        {
            public int CountRows(IQueryable source)
            {
                return source.OfType<T>().AsQueryable<T>().Count<T>();
            }
        }



        public class GenericPager<T>
        {
            public IEnumerable<T> Page(IQueryable source, int pageIndex, int pageSize)
            {
                return source.OfType<T>().AsQueryable<T>().Skip<T>(pageIndex).Take<T>(pageSize);
            }
        }

        public class GenericSorter<T, PT>
        {

            public IEnumerable<T> Sort(IQueryable source, string sortExpression, SortDirection sortDirection)
            {
                var param = Expression.Parameter(typeof(T), "item");

                var sortLambda = Expression.Lambda<Func<T, PT>>(Expression.Convert(Expression.Property(param, sortExpression), typeof(PT)), param);

                if (sortDirection == SortDirection.Descending)
                {
                    return source.OfType<T>().AsQueryable<T>().OrderByDescending<T, PT>(sortLambda);
                }
                else
                {
                    return source.OfType<T>().AsQueryable<T>().OrderBy<T, PT>(sortLambda);
                }
            }
        }

    }
     
    Tuesday, August 26, 2008 8:11 PM

All replies

  • If your datasource is LINQ to SQL use the LinqDataSource, this is all built in.

     

    If your datasource is say, LINQ to Objects, use an ObjectDataSource

     

    These two options are MUCH easier than writing a custom datasource.

     

    Tuesday, August 26, 2008 5:36 PM
    Answerer
  • Hi Howard,

    Thanks for your reply. I did not want to use the LINQ datasource as I want to actually want to be able to pass around LINQ queries in the IQueryable form to perform further deferred actions on them.

    I had forgotten I had left this thread open but I actually found the answer in the meantime. Basically passing the query in to the generic sorting and paging methods as IEnumerable causes the query to execute breaking the intended deferred execution. Changing these to IQueryable solves the situation and gives a LINQ datasource that accepts queries in the IQueryable form.

    In case anyone is interest the full working code looks like this:

    using System;
    using System.Linq;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Collections;
    using System.Collections.Generic;
    using System.Linq.Expressions;
    using System.Reflection;

    /// <summary>
    /// Summary description for LinqQueryDataSource
    /// </summary>
    ///

    namespace Inflecto.Web.UI
    {

        public class LinqQueryDataSource : DataSourceControl
        {
            private LinqQueryDataSourceView view;

            private IQueryable query;
            public IQueryable LinqQuery
            {
                get {return query;}
                set {query = value;}
            }

            public LinqQueryDataSource() : base() { }

            protected override DataSourceView GetView(string viewName)
            {
                // We only have one view... If it has not been created then create it else return
                if (view == null)
                    view = new LinqQueryDataSourceView(this, string.Empty, query);

                return view;
            }


            protected override ICollection GetViewNames() {
            ArrayList al = new ArrayList(1);
            al.Add(LinqQueryDataSourceView.DefaultViewName);
            return al as ICollection;


        }

            public class LinqQueryDataSourceView : DataSourceView
            {
                IQueryable originalQuery;
                string previousSortExpression;

                public static string DefaultViewName = "LinqQueryView";

                public LinqQueryDataSourceView(IDataSource owner, string name, IQueryable query)
                    : base(owner, DefaultViewName)
                {
                    originalQuery = query;
                    previousSortExpression = string.Empty;
                }

                public override bool CanPage
                {
                    get {return true;}

                }
                public override bool CanSort
                {
                    get{return true;}

                }
                public override bool CanRetrieveTotalRowCount
                {
                    get{return true;}
                }
               
                protected override IEnumerable ExecuteSelect(DataSourceSelectArguments arguments)
                {
                    if (originalQuery == null)
                    {
                        throw new Exception("LinqQueryDataSource must have LinqQuery set");
                    }

                    // Grab types needed for reflection
                    IQueryable thisQuery = originalQuery;
                    Type dataSourceType = thisQuery.GetType();
                    Type dataItemType = getDataItemType(thisQuery, dataSourceType);

                   
                    // Set total Row Count Property
                    if (arguments.RetrieveTotalRowCount)
                        arguments.TotalRowCount = getTotalRowCount(thisQuery, dataItemType, dataSourceType);
                   
                   

                    // If sorting requested perform this and get deferred query back
                    if (arguments.SortExpression != string.Empty)
                        thisQuery = performSort(thisQuery, arguments.SortExpression, dataSourceType, dataItemType);             

                    // If Paging requested perform paging
                    if (arguments.MaximumRows !=0)
                        thisQuery = performPaging(thisQuery, arguments.StartRowIndex, arguments.MaximumRows,
                            dataItemType, dataSourceType);

               
                    // Convert IQueryable to list as we need something that implements ICollection
                    // to stop the gridview whineing         

                    Type listCasterType = typeof(GenericListCaster<>).MakeGenericType(dataItemType);
                    object listCasterObject = Activator.CreateInstance(listCasterType);

                    return (IEnumerable) listCasterType.GetMethod("ToList", new Type[] { dataSourceType})
                        .Invoke(listCasterObject, new object[] { thisQuery });
                        
                }

                private IQueryable performSort(IQueryable thisQuery, string sortExpression, Type dataSourceType, Type dataItemType)
                {
                    SortDirection sortDirection;

                    if (sortExpression.EndsWith(" DESC"))
                    {
                        sortDirection = SortDirection.Descending;
                        sortExpression = sortExpression.Substring(0, sortExpression.IndexOf(" DESC"));
                    }
                    else
                    {
                        sortDirection = SortDirection.Ascending;
                    }

                    object sorterObject = null;

                    Type sorterType = null;

                    PropertyInfo property = dataItemType.GetProperty(sortExpression);

                    sorterType = typeof(GenericSorter<,>).MakeGenericType(dataItemType, property.PropertyType);

                    sorterObject = Activator.CreateInstance(sorterType);

                   return (IQueryable)sorterType.GetMethod("Sort", new Type[] { dataSourceType, typeof(string), typeof(SortDirection) })
                            .Invoke(sorterObject, new object[] { thisQuery, sortExpression, sortDirection });

                }

                private IQueryable performPaging(IQueryable thisQuery, int startRowIndex, int maximumRows, Type dataItemType, Type dataSourceType)
                {
                    Type pagerType = typeof(GenericPager<>).MakeGenericType(dataItemType);
                    object pagerObject = Activator.CreateInstance(pagerType);

                    return (IQueryable)pagerType.GetMethod("Page", new Type[] { dataSourceType, typeof(int), typeof(int) })
                        .Invoke(pagerObject, new object[] { thisQuery, startRowIndex, maximumRows });
                }

                private Type getDataItemType(IEnumerable dataSource, Type dataSourceType)
                {
                    Type dataItemType = typeof(object);

                    if (dataSourceType.HasElementType)
                    {
                        dataItemType = dataSourceType.GetElementType();
                    }
                    else if (dataSourceType.IsGenericType)
                    {
                        dataItemType = dataSourceType.GetGenericArguments()[0];
                    }
                    else if (dataSource is IEnumerable)
                    {
                        IEnumerator dataEnumerator = dataSource.GetEnumerator();

                        if (dataEnumerator.MoveNext() && dataEnumerator.Current != null)
                        {
                            dataItemType = dataEnumerator.Current.GetType();
                        }
                    }

                    return dataItemType;
                }

                private int getTotalRowCount(IQueryable thisQuery, Type dataItemType, Type dataSourceType)
                {
                    Type rowCounterType = typeof(GenericRowCounter<>).MakeGenericType(dataItemType);
                    object rowCounterObject = Activator.CreateInstance(rowCounterType);

                    return (int)rowCounterType.GetMethod("CountRows", new Type[] { dataSourceType })
                        .Invoke(rowCounterObject, new object[] { thisQuery });
                }
            }
        }

        public class GenericListCaster<T>
        {
            public List<T> ToList(IQueryable source)
            {

                return source.OfType<T>().AsQueryable<T>().ToList<T>();
            }
        }

        public class GenericRowCounter<T>
        {
            public int CountRows(IQueryable source)
            {
                return source.OfType<T>().AsQueryable<T>().Count<T>();
            }
        }



        public class GenericPager<T>
        {
            public IEnumerable<T> Page(IQueryable source, int pageIndex, int pageSize)
            {
                return source.OfType<T>().AsQueryable<T>().Skip<T>(pageIndex).Take<T>(pageSize);
            }
        }

        public class GenericSorter<T, PT>
        {

            public IEnumerable<T> Sort(IQueryable source, string sortExpression, SortDirection sortDirection)
            {
                var param = Expression.Parameter(typeof(T), "item");

                var sortLambda = Expression.Lambda<Func<T, PT>>(Expression.Convert(Expression.Property(param, sortExpression), typeof(PT)), param);

                if (sortDirection == SortDirection.Descending)
                {
                    return source.OfType<T>().AsQueryable<T>().OrderByDescending<T, PT>(sortLambda);
                }
                else
                {
                    return source.OfType<T>().AsQueryable<T>().OrderBy<T, PT>(sortLambda);
                }
            }
        }

    }
     
    Tuesday, August 26, 2008 8:11 PM