We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on : Nov 16th 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Pure Virtualizing Grid based on IQueryable

Thread ID:

Created:

Updated:

Platform:

Replies:

121835 Jan 29,2016 04:07 PM UTC Feb 8,2016 12:44 PM UTC WPF 5
loading
Tags: SfDataGrid
Nathan Arnott
Asked On January 29, 2016 04:07 PM UTC

I would like to pass an IQueryable created from the Entity Framework to a CollectionView and use that to provide the data to the SfDataGrid.  I have a table that has 100,000+ records, and I don't want to download them all in order to get filtering/sorting/grouping functionality, as that take forever (these rows aren't small), and a waste of bandwidth.

I tried passing an IQueryable into a GridVirtualizingCollectionView, but that resulted in a "There is already an open DataReader associated with this Command which must be closed first." error, so I'm guess it doesn't support it.  Is there any way to get something like this to work.  I've seen this work with other vendors' grids, but can't seem to get it to work here.

Sowndaiyan Paulpandi [Syncfusion]
Replied On February 1, 2016 12:47 PM UTC

Hi Nathan,

Thanks for contacting Syncfusion Support.

We have analyzed your query and also we have checked with the IQueryable into the GridVirtualizingCollectionView. It is working fine from our side. For your reference we have attached the test sample on the below location. If you are still facing the issue, we recommend you to convert the IQueryable into the IEnumerable by using the AsEnumerable method or else please revert by modifying the attached sample based on your application to replicate the issue. It will be helpful for us to analyze further. 


Sample: http://www.syncfusion.com/downloads/support/forum/121835/ze/DataVirtualization1838171686


Regards,

Sowndaiyan


Nathan Arnott
Replied On February 1, 2016 03:19 PM UTC

Thanks for your response.  However, the sample is basically converting an IEnumerable into an IQueryable.  My issue arrives by using an IQueryable generated from the Entity Framework (version 6 in my case).  Also, using AsEnumerable would force a complete query download, which is what I want to avoid. 

I ran a trace through Entity Framework, and the simple act of passing an EF IQueryable to the GridVirtualzingCollectionView results in 5 separate full selects against the table on a basic test table (no filtering at all).  That is worrisome. 

I haven't been able to reproduce the DataReader error in a sample app yet, but I have provided asample application that produces the multiple selects I just described above, which is just as much a deal-breaker.  I can't have a full select on the table occur, let alone 5!

Attachment: DataVirtualizationUsing_EF_17f3878a.zip

Jayaleshwari N [Syncfusion]
Replied On February 4, 2016 02:38 AM UTC

Hi Nathan,

Thanks for the update.

We have analyzed your query “to avoid complete query download”. Using Tolist() will download query for once and it will not hit complete select for more times.

Code Snippet:

public ViewModel()

{

    var repository= new EmployeeInfoRespository();

    var ch = repository.GetEmployeesDetails(100000);

    var localViewSource = new GridVirtualizingCollectionView(repository.GetEmployeesDetails(100000).ToList());



    //throw new Exception("Take a look at the console output here, and notice how many selects occurred...");

           

    viewSource = localViewSource;
}



Or if you want to do all the operations in on-demand you can create a custom virtualizing collection using GridVirtualizingCollectionView.
When you use CustomDataVirtualization you should override below methods.

public class GridVirtualizingCollectionViewExt : GridVirtualizingCollectionView

{

    public IQueryable<Employee> CustomVirtualizingItemsSource { get; set; }


    public GridVirtualizingCollectionViewExt()

    {          

        CustomVirtualizingItemsSource = new EmployeeInfoRespository().GetEmployeesDetails(100000);                     

    }


    /// <summary>

    /// Method to return the item at specified index

    /// </summary>

    /// <param name="index"></param>

    /// <returns></returns>

    public override object GetItemAt(int index)

    {

        return CustomVirtualizingItemsSource.ElementAt(index);           

    }



    /// <summary>

    /// Method to return the index of specified item

    /// </summary>

    /// <param name="item"></param>

    /// <returns></returns>

    protected override int GetIndexOf(object item)

    {

           

        var enumerator = CustomVirtualizingItemsSource.GetEnumerator();

       var count = CustomVirtualizingItemsSource.Count();

        for (int i = 0; i < count; i++)

        {

            if (enumerator.Current == null)

                enumerator.MoveNext();


            if (enumerator.Current.Equals(item))

                return i;              


            if (enumerator.MoveNext())

                continue;

        }

        return 0;

    }


    /// <summary>

    /// Method to return the count of records

    /// </summary>

    /// <returns></returns>

    public override int GetViewRecordCount()

    {          

        return CustomVirtualizingItemsSource.Count();

    }


    /// <summary>

    /// Methdo to retunr the source.

    /// </summary>

    /// <returns></returns>

    public override System.Collections.IEnumerable GetInternalSource()

    {           

        return CustomVirtualizingItemsSource;

    }


    protected override void ProcessSort(System.ComponentModel.SortDescriptionCollection sortDescription)

    {

        if (this.SortDescriptions.Count == 0)

            return;

           

        for (int i = 0; i < SortDescriptions.Count; i++)

        {

            var sortDecription = SortDescriptions[i];

            if (sortDecription.Direction == System.ComponentModel.ListSortDirection.Ascending)

                CustomVirtualizingItemsSource = CustomVirtualizingItemsSource.OrderBy(sortDecription.PropertyName, GetFunc(sortDecription.PropertyName)).AsQueryable<Employee>();

            else

                CustomVirtualizingItemsSource = CustomVirtualizingItemsSource.OrderByDescending(sortDecription.PropertyName, this.GetFunc(sortDecription.PropertyName)).AsQueryable<Employee>();

        }

    }


    public override System.Collections.IEnumerable GetSourceListForFilteringItems()

    {

        return CustomVirtualizingItemsSource;

    }


    protected override IEnumerable<GroupResult> GetGroupedSource(string[] groupBy)

    {

        IQueryable queryable = CustomVirtualizingItemsSource.OfQueryable().AsQueryable();


        var result = CustomVirtualizingItemsSource.GroupByMany(this.SourceType, (property) => this.GetExpressionFunc(property), groupBy);


        return result;

    }
}



And set the GridVirtualizingCollectionViewExt as items source to SfDataGrid

sfDataGrid.ItemsSource = new GridVirtualizingCollectionViewExt();


Please refer the following UG for reference.
http://help.syncfusion.com/wpf/sfdatagrid/data-virtualization#custom-data-virtualization

Please let me know any concern on this.

Regards,

Jayaleshwari N.



Nathan Arnott
Replied On February 4, 2016 03:43 PM UTC

That definitely puts me on the right track, thank you.  So I'm working on this a basic implementation, and am running into a issue with filters.  As soon as I attempt to apply a filter, I get an error.  I've override and am supplying data at every point you've mentioned (and also followed the link), but I can't figure out why this is happening. 

System.NullReferenceException was unhandled
  HResult=-2147467261
  Message=Object reference not set to an instance of an object.
  Source=Syncfusion.Data.WPF
  StackTrace:
       at Syncfusion.Data.Extensions.QueryableExtensions.OfQueryable(IEnumerable items)
       at Syncfusion.Data.VirtualizingCollectionView.RefreshFilter()
       at Syncfusion.Data.CollectionViewAdv.set_FilterPredicates(ObservableCollection`1 value)
       at Syncfusion.UI.Xaml.Grid.GridModel.FilterColumn(GridColumn column, List`1 filterPredicates)
       at Syncfusion.UI.Xaml.Grid.GridFilterControl.RefreshFilter()
       at Syncfusion.UI.Xaml.Grid.GridFilterControl.<ApplyFilters>b__10(Object s, RunWorkerCompletedEventArgs e)
       at System.ComponentModel.BackgroundWorker.OnRunWorkerCompleted(RunWorkerCompletedEventArgs e)
       at System.ComponentModel.BackgroundWorker.AsyncOperationCompleted(Object arg)
       at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
       at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(Object source, Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)
       at System.Windows.Threading.DispatcherOperation.InvokeImpl()
       at System.Windows.Threading.DispatcherOperation.InvokeInSecurityContext(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Windows.Threading.DispatcherOperation.Invoke()
       at System.Windows.Threading.Dispatcher.ProcessQueue()
       at System.Windows.Threading.Dispatcher.WndProcHook(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
       at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
       at MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)
       at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
       at System.Windows.Threading.ExceptionWrapper.TryCatchWhen(Object source, Delegate callback, Object args, Int32 numArgs, Delegate catchHandler)
       at System.Windows.Threading.Dispatcher.LegacyInvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Int32 numArgs)
       at MS.Win32.HwndSubclass.SubclassWndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam)
       at MS.Win32.UnsafeNativeMethods.DispatchMessage(MSG& msg)
       at System.Windows.Threading.Dispatcher.PushFrameImpl(DispatcherFrame frame)
       at System.Windows.Threading.Dispatcher.PushFrame(DispatcherFrame frame)
       at System.Windows.Application.RunDispatcher(Object ignore)
       at System.Windows.Application.RunInternal(Window window)
       at System.Windows.Application.Run(Window window)
       at System.Windows.Application.Run()

Jayaleshwari N [Syncfusion]
Replied On February 8, 2016 12:44 PM UTC

Hi Nathan,

Thanks for your update.

We have analyzed your query “Getting Exception while filtering”. You can avoid the exception by overriding RefreshFilter method in GridVirtualizingCollectionViewExt.
RefreshFilter method applies filter to your collection based on the filter predicates by ExcelLikeFiltering.

public class GridVirtualizingCollectionViewExt : GridVirtualizingCollectionView
{

    /// <summary>

    /// Method to apply filter
    /// </summary>

    public override void RefreshFilter()

    {

         var viewModel = this.dataGrid.DataContext as ViewModel;

       

        if (string.IsNullOrEmpty(viewModel.FilterText))

            ClearFilter();

        else
            FilterData( viewModel.FilterColumn, viewModel.FilterText);
    }

    /// <summary>

    /// Method filtering the data using query

    /// </summary>

    /// <param name="columnName"></param>

    /// <param name="filterText"></param>

    public void FilterData(string columnName,string filterText)

    {

        CustomVirtualizingItemsSource = new EmployeeInfoRespository().GetFilteredSource(columnName,filterText);

        this.Refresh();
    }

}


You cannot achieve the advanced filter with Linq to Lamda expressions. You can write your own filter logic and return the source from the data base and need to refresh the view using Refresh method while filter and clear the filter.

// Apply filter to Custom Data Virtualization.

dataGrid.View.RefreshFilter();


Please let me know any concern on this.

Regards,

Jayaleshwari N.

CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;