Hi All,

This post is more for future reference for others who are struggling with the same problem as we were doing. We needed a solution to run the ASPxGridView in server mode via our framework while maintaining our LLBLGen datastructure. (It turned out to be quite simple btw, it just took me a day to understand that what I had found and disregarded in the morning turned out to be basis of the solution around 10pm).

An introduction: We started to see that our system, which allows users to define which columns to show in a grid, and which not to, got the potential for performance issues. Apart from the columns which directly link to fields of the records displayed (i.e. Order.Number), we offer the possibility to show related fields (i.e. Order.Customer.Name or Order.Customer.AccountManager.Department.Name (you get the point ). Since we used the ASPxGridView in standard (not Server Mode) this could cause huge performance issues.

We build a test case to find out the potential performance issues and with table of a 100k rows and a relation of 3 levels deep (i.e. OrderItem.Order.Customer.Debtor.Name) it took 4,5 minute and 5,5 Gb of RAM to set a filter on the Debtor.Name field. This was without prefetching which was considerably faster, but still used a large amount of RAM. With a simple LINQ query (via LLBL) it only took ca. 2.5 seconds and no noticeable memory increase. So we decided LINQ was the way to go, but then, how to get it dynamically? So we started.

We had the following requirements:

• The code in the framework can’t have direct access to the LLBLGen project code
• The whole process of calling the database via Linq to LLBLGen Pro should be dynamic. Meaning with just a few string parameters during runtime we should be able to request the required data.
• Our users can select and remove columns from their grids (custom views), so we needed to be able to query only the columns we needed for performance reasons
• The select/remove functionality of the columns also works recursively for all related entities, we needed that too. For example: Order.Customer.AccountManager.Lastname

• Performance had to be great (this was why we switched from “Standard” to “Server mode”) To fit these requirements it turned out we just needed to use the LINQ Dynamic Query Library (http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx). For ASP.NET and the ASPxGridView the following steps were required (it could be done more gently, this is just for illustration purposes):

• Place the Grid and a LinqServerModeDataSoure on the Page

• In the Page_Load set the LinqServerModeDataSoure as the DataSource for the Grid and bind an EventHandler to LinqServerModeDataSoure.Selecting event.
• In the LinqServerModeDataSoure.Selecting do the following (our code is below):
• Dynamically create an instance of the LinqMetaData class of your data project
• Invoke the Property by the name (a string) of the entity on LinqMetaData
• Call the Select method on DynamicQueryable from the Dynamic Query Library with a string containing the fields you want to retrieve and set the result of this method as the e.QueryAbleSource

• set e.KeyExpression to the PK fieldname (string) of the entity to be retrieved

• Done! In our case the code in the Selecting event handler looks like this. (Dionysos is the name of our framework, Data project is called ExtraVesting.Data)

    void LinqServerModeDataSource_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e)
    {
        LinqMetaData m = new LinqMetaData();
        
        // Create an instance of the LinqMetaData from the Data project
        string absolutePathToDataAssembly = 
            this.Server.MapPath("~/bin/ExtraVestiging.Data.dll");
        System.Reflection.Assembly assemblyData = 
            System.Reflection.Assembly.LoadFile(absolutePathToDataAssembly);
        object linqMetaData = Dionysos.InstanceFactory.CreateInstance(assemblyData, 
            Type.GetType("ExtraVestiging.Data.Linq.LinqMetaData,    
            ExtraVestiging.Data"));

        // Invoke the correct property on the LinqMetaData to get the correct DataSource<> for the Entity 
        IQueryable queryableDataSourceForEntity = 
            Dionysos.Reflection.Member.InvokeProperty(linqMetaData, "Order") as IQueryable;

        // Call the Select which is provided by the LINQ Dynamic Query Library  
        queryableDataSourceForEntity = DynamicQueryable.Select(queryableDataSourceForEntity, 
            "New(OrderId, Number, Customer.Name, Customer.AccountManager.Lastname)");   

        e.KeyExpression = "OrderId";
        e.QueryableSource = queryableDataSourceForEntity as IQueryable;
    }

For us this works great and will be incorporated in our framework asap. A little test on 500.000 records also showed great performance, thanks to neat queries of LLBLGen and the SQL Server and the ASPxGridView translating it’s needs (Paging, Filtering & Grouping) to Linq queries.

I hope other people can use this too! Gab.

Edit: Just tested it on a 500k table. Filtering on a 3 level deep relation took ca. 3.5-5 seconds. Can it that fast, slow or normal? Seems quite fast to me.

Thanks for sharing this to the LLBLGen community