RadDataPager not loading all records when loading

[Sonia]

Hi,
I have to load in a RadGridView records from a table which has more than 10 millions of records. In my window (apart from the RadGridView) I also have a RadDataPager to paginate all those records from 100 to 100 records, but my problem is that all of the several millions of records are loaded at the beginning and paginated after that. Because of that, my windows becomes too slow when loading.
I have read in http://www.telerik.com/community/forums/wpf/gridview/gridview-loading-rows---tooks-5-to-10-sec-with-2500-records.aspx
that using LINQ-to-SQL or LINQ-to-Entities eliminates the problem, but I don´t know how to implementate it. I've try it in different ways but always with the same result (window takes too much to load). How do you tell RadDataPager to take next 100 records from last time ".Take()" executed?
Please, if it's possible give me an example of how to bring only the amount of records I want (for example, 100 records) and when user clicks on "next page" of RadDataPager bringing the next 100 records... (I'm currently using WPF with pattern MVVM).

Thanks for answer,

Sonia.

[Rossen Hristov]

Hi,

Since you are not binding the pager to an IQueryable (i.e. LINQ-to-SQL, LINQ-to-Entities, LINQ-to-Objects) I assume that you are somehow pulling your data from somewhere by hand. You did not specify how you are actually pulling your data to the client. What technology do you use?

Anyway, my suggestion is to use RadDataPager in its Unbound Mode. You will simply tell the pager the total amount of items and the page size and it will do the math and calculate the number of pages. From then on when the user clicks on a page, you will receive the respective event and you will be able to pull this page of data on the client. How you do that -- I am not sure since you never mentioned it. Anyway, having the current page index you should be able to pull this page from the server in any way you are doing it currently.

If you decide to bind the pager to a LINQ-enabled data source (i.e. IQueryable), the paging will happen on the server out-of-the-box for you. When the pager is bound to an IQueryable, it will automatically append Skip(n) and Take(m) statements to this query. Ultimately, these statements will reach SQL Server thanks to the query provider you will be using.

In my opinion, LINQ and IQueryable are the future and you should seriously consider them.

I hope this helps.

Regards,
Rossen Hristov
Telerik

[Sonia]

Hi,

Thanks for the answer. It has been very useful. Now I use LINQ to Entities. But when I've tried your solution, I've encountered 2 new problems:

1.- The PageSize of my RadDataPager is 100. In its unbound mode works fine, but when I do a filter in a column of the RadGridView, the filter only shows, for example, 20 records (of those 100 than was currently showing). But my problem is that there are more records in the database that fit the filter of the RadGridView, so how can I do to show until 100 records than fit the condition of the filter?
My code is:

private void radDataPager_PageIndexChanged(object  sender, PageIndexChangedEventArgs e)
{
this.radDataPager.ItemCount = entidadBD.PROTOCOLs.Count();

this.grdProtocol.ItemsSource = entidadBD.PROTOCOLs.OrderBy(p => p.objid).Skip(e.NewPageIndex * this.radDataPager.PageSize).Take(this.radDataPager.PageSize).ToList();
}

// entidadBD is the conexion to the database (Entities). PROTOCOLs is my table of the database. grdProtocol is my RadGridView.

Obviously, when I do the filter, this event cannot execute. So, what event have I to handle (in the RadGridView) to let me make the new 100 records with the condition of the filter?


2.- What I have prove works fine (except filtering), but without using MVVM pattern. In my solution I'm using this MVVM pattern. So, how can I do in the xaml to call a property (of my view model) passing the parameter "PageIndexChangedEventArgs e"?
Currently I have in my code:

<telerik:RadDataPager x:Name="radDataPager"
PageSize="100" FontSize="12" Margin="10,0,10,10"
VerticalAlignment="Bottom"
PageIndexChanged="radDataPager_PageIndexChanged"/>

But, for example, in a button of my window I have:

<Button x:Name="btnLoadProtocol" Content="Load data" Command="{Binding LoadProtocolTableCommand}" HorizontalAlignment="Left" Margin="10,10,0,0" VerticalAlignment="Top" Width="75"/>

An "LoadProtocolTableCommand" (in my view model called "MainViewModel") is:

private Lazy<RelayCommand> loadProtocolTableCommand;

public MainViewModel()
{

loadProtocolTableCommand = new Lazy<RelayCommand>(() => new RelayCommand(LoadProtocolTableCommandExecute));

}

public ICommand LoadProtocolTableCommand
{

get

{

return loadProtocolTableCommand.Value;

}
}
private void LoadProtocolTableCommandExecute()

{

// loading data

}

So, my question is:
How can I do to change "PageIndexChanged="radDataPager_PageIndexChanged"" and how have I to make my view model "MainViewModel.cs" so as to do it following MVVM pattern?



Thanks for answer,

Sonia.

[Rossen Hristov]

Hi,

Since you are now using LINQ you don't really need to use the pager in Unbound Mode anymore. Unbound Mode is when you don't have LINQ.

Simply bind the RadGridView.ItemsSource to your IQueryable instance and then bind RadDataPager.Source to RadGridView.Items as shown in this help article under the topic "Paging RadGridView"

You can also use RadEntityFrameworkDataSource. Open our online QSF and search for RadEntityFrameworkDataSource examples. In the example you will see a grid and a pager bound to it. All operations will happen on the server out-of-the-box.

I hope this helps.

Regards,
Rossen Hristov
Telerik

[Sonia]

Hi,

Thanks for the answer. But when I use IQueryable as you describe me in previous email (bind the RadGridView.ItemsSource to your IQueryable instance and then bind RadDataPager.Source to RadGridView.Items), it only works first time (when I load the first 100 records). When I click on "next" button of my RadDataPager (or in a numbered button to go to another page) it appears no data.
The code I have is:

<Button x:Name="btnCargarProtocol" Content="Cargar datos" Command="{Binding CargarTablaProtocolCommand}" HorizontalAlignment="Left" Margin="10,10,0,0" VerticalAlignment="Top" Width="75"/>

<telerik:RadGridView

    x:Name="grdProtocol"

    ItemsSource="{Binding TablaProtocolEnGridIQ}"

    SelectedItem="{Binding Path=ProtocolSeleccionado, Mode=TwoWay}"

    AutoGenerateColumns="False"

    Grid.Row="0"

    SelectionUnit="FullRow"

    GridLinesVisibility="Horizontal"

    CanUserDeleteRows="False"

    CanUserInsertRows="False"

    HorizontalAlignment="Stretch"

    VerticalAlignment="Stretch"

    Margin="10,37,10,38"

    CanUserFreezeColumns="False"

    RowIndicatorVisibility="Collapsed"

    IsReadOnly="True"

    ShowGroupPanel="False">

    <telerik:RadGridView.Columns>

        <telerik:GridViewDataColumn DataMemberBinding="{Binding objtype}" Width="*" Header="Objtype" />

        <telerik:GridViewDataColumn DataMemberBinding="{Binding objid}" Width="*" Header="Objid" />

        <telerik:GridViewDataColumn DataMemberBinding="{Binding action}" Width="*" Header="Action" />

        <telerik:GridViewDataColumn DataMemberBinding="{Binding param0}" Width="*" Header="Param0" />

        <telerik:GridViewDataColumn DataMemberBinding="{Binding param1}" Width="*" Header="Param1" />

        <telerik:GridViewDataColumn DataMemberBinding="{Binding date}" Width="*" Header="Date" DataFormatString="{}{0:d}" />

        <telerik:GridViewDataColumn DataMemberBinding="{Binding time}" Width="*" Header="Time" DataFormatString="{}{0:d}" />

    </telerik:RadGridView.Columns>

</telerik:RadGridView>

<telerik:RadDataPager x:Name="radDataPager"

                      PageSize="10" FontSize="12" Margin="10,0,10,10"

                      Source="{Binding Items, ElementName=grdProtocol}"

                      IsTotalItemCountFixed="True"

                      DisplayMode="FirstLastPreviousNextNumeric, Text"

                      VerticalAlignment="Bottom"/>

And my view model is:

public class MainViewModel : ViewModelBase

    {

   private Lazy<RelayCommand> cargarTablaProtocolCommand;
private IQueryable tablaProtocolEnGridIQ;

        private wafintellectEntities entidadBD;

         

        public MainViewModel()

        {

            cargarTablaProtocolCommand = new Lazy<RelayCommand>(() => new RelayCommand(CargarTablaProtocolCommandExecute));

        }

 

        public ICommand CargarTablaProtocolCommand

        {

            get

            {

                return cargarTablaProtocolCommand.Value;

            }

        }

 

        private void CargarTablaProtocolCommandExecute()

        {

            cargarTablaProtocol();

        }

 

        private void cargarTablaProtocol()

        {

            entidadBD = new wafintellectEntities(WPFBaseDatos.Configuracion.CadenaConexionBD);

            tablaProtocolEnGrid = new List<PROTOCOL>();

            var products = entidadBD.PROTOCOLs; // returns IQueryable

            TablaProtocolEnGridIQ = products;

        }

 

        public IQueryable TablaProtocolEnGridIQ

        {

            get { return tablaProtocolEnGridIQ; }

            set

            {

                tablaProtocolEnGridIQ = value;

                RaisePropertyChanged("TablaProtocolEnGridIQ");

            }

        }

 

        PROTOCOL protocolSeleccionado;

        public PROTOCOL ProtocolSeleccionado

        {

            get { return protocolSeleccionado; }

            set

            {

                protocolSeleccionado = value;

                RaisePropertyChanged("ProtocolSeleccionado");

            }

        }

}

So, when I click de button btnCargarProtocol, the first 100 records are loaded in the RadGridView, but when I click in "next" button of RadDataPager, no data is loaded (because it's not execute "cargarTablaProtocol()" method).
So, how can I solve it?


And, another problem I have is:
If a use RadEntityFrameworkDataSource my problem is that all the records are loaded first time. I mean, although paginate works fine, when a click btnCargarProtocol button all records from database are loaded (more than 10 millions records), so it become too slow... RadEntityFrameworkDataSource works this way or what am I doing wrong?



Thanks for answers.

Sonia.

[Rossen Hristov]

Hi,

I am not sure what you are doing wrong, since I can't really debug your project.

I have attached a properly working sample project with a RadGridView and RadDataPager both bound to a QueryableEntityCollectionView. The paging works as expected. The sample project uses the AdventureWorks2012 samples database.

Please, take a look at this project and use it as a blueprint for your real project.

A similar sample can be found in our online QSF. It is under the RadEntityFrameworkDataSource examples and is called MVVM. The QueryableEntityCollectionView is RadEntityFrameworkDataSource's inner view and can be used in MVVM scenarios, since it is illegal to place a UI control in your view model.

If you are using the old Entity Framework, which has ObjectContext, you can directly pass it in the QECV constructor. If you are using the new Entity Framework, which has a DbContext, you should extract the ObjectContext from it like I have shown in the attached sample project. But the idea is exactly the same -- create a new QECV by passing an ObjectContext and the entity set name to its constructor, then expose it on your view model as a public property and then bind both the grid and the pager to this QECV. Our QECV is also IQueryable and will listen to what the grid and the pager tell it and send it directly to the database.

I hope this helps.

This is about everything I can do to assist you.

Regards,
Rossen Hristov
Telerik