Databinding Mapped Columns Using LLBLGenProDataSource

by Al Beecy December 22, 2008
LLBLGen Pro, for those not familiar with it, is a ORM layer that provides reasonably good flexibility, support for lots of databases, and probably the best design-time tools of the various ORM layers out there.

A useful feature is the ability to map related columns onto an entity and, for the most part, access these columns as though they were part of the base entity. In general, when working with an individual entity instance, this works well. But it breaks down somewhat when working with entity collections, particularly in databinding scenarios.

For instance, using the Northwind DB, I create an entity collection from the orders table. My Orders entity has a column, ContactName, mapped from the Customers table. I use a LLBLGenProDataSource control and bind declaratively to a Telerik grid, with filtering and sorting enabled. The initial load seems to work fine, but if I click on the ContactName header to sort by that column, the app will crash.

To fix this, it is necessary to add a relation to the data source ccontrol so that it knows to include the join to Customers when it generates the sql for the sort:

// add relations to the datasource for any tables
// from which mapped columns will be bound,
// otherwise sorting on mapped fields will fail

LLBLGenProDataSource1.RelationsToUse = new RelationCollection();
LLBLGenProDataSource1.RelationsToUse.Add(OrdersEntity.Relations.CustomersEntityUsingCustomerId);

There is also a second, less obvious problem that could seriously impact performance on a busy server: lazy loading. If you run a trace in SQL Server profiler while loading the page, you see 2 queries to grab the main data - one that gets a total count of rows, and one that brings back the grid's current page of records. But the mapped column's data isn't included in the main query. What is does next is execute an additional query for each individual row to retrieve the mapped column.

To fix this, you need to add what LLBLGen calls a "PrefetchPath". This tells it to include the mapped data on the first trip to the DB:

// add PrefetchPaths to the datasource for any tables
// from which mapped columns will be bound,
// otherwise it will execute an additional query for
// each row returned * the number of mapped fields

IPrefetchPath path = new PrefetchPath((int)EntityType.OrdersEntity);
path.Add(OrdersEntity.PrefetchPathCustomers);
LLBLGenProDataSource1.PrefetchPathToUse = path;

Curiously, filtering works without adding any extra code-behind even though I would expect the filter query to have the same lack of knowedge as the sort query.

The support folks over at LLBLGen claim these additional bits of code are unavoidable due to the nature of mapped columns. I think this explanation is kind of weak given that it is their data control talking to their entity collection.

It doesn't seem like rocket science to add attributes to mapped columns in the entity collection marking them as such and identifying the relation they depend upon. This would make it rather easy for the LLBLGenProDataSource control to add the needed relations to itself. This would fix the sorting problem.

I'm not certain how much a data source control knows about which columns are bound by the control(s) using it, but if it does know which columns are being used and one or more of them are mapped, it would seem it could also make the prefetch determination without manual intervention.

Maybe some day when I have time I'll crawl through the source code for these and see if I can make them a little more user-friendly...

Tags:

Asp.Net | LLBLGen Pro

Comments

December 25, 2008 #

DotNetKicks.com

Trackback from DotNetKicks.com

Databinding Mapped Columns Using LLBLGenProDataSource

DotNetKicks.com

January 23, 2009 #

Web Development Community

Trackback from Web Development Community

Databinding Mapped Columns Using LLBLGenProDataSource

Web Development Community

Powered by BlogEngine.NET1.5.0.7 | Theme by Mads Kristensen