LINQ to SQL and DetailTables

[Stephen Rochford]

When creating an object class from an SQL database using the LINQ to SQL tool in Visual Studio 2008, SQL table relationships get mapped to object relationships. For example lets say I have a database with a "Customers" table and an "Orders" table and there is a foreign key relationship between the two tables.  When I use LINQ to SQL to create a C# class library for this database I will end up with two data classes; Customer and Order.  The nice thing is that LINQ will automatically create a parent/child realtionship between the two data classes (because of the foreign key relationship in the DB).  The LINQ generated Customer data class might  look something like this:

Customer:
    FirstName
    LastName
    Address
    Orders

where the "Orders" property is actually a data collection that is related to the parent Customer record. When populating an instance of the "Customer" object (call it aCustomer) LINQ will automatically do the heavy lifting of retrieving the related "Orders" collection, all we have to do is reference aCustomer.Orders and we have all the order records for that customer.

So now my question:  when setting up a Telerik grid using a LINQ datasource with the 'Customer' object being the <mastertable> data, can I use the 'Customer.Orders' column as the datasource for an embeded  <detailstable>?

Thanks,
Stephen

[Sebastian]

Hello Stephen,

Thank you for the detailed explanation.

In order to generate hierarchy using the underlying object structure of Customers and Orders, I suggest you utilize DetailTableDataBind event handling as illustrated in this online demo:

http://www.telerik.com/DEMOS/ASPNET/Prometheus/Grid/Examples/Programming/DetailTableDataBind/DefaultCS.aspx

Thus you can pass a collection of Customers as data source for the master table in the grid and bind the data for the child tables intercepting the DetailTableDataBind event of the control. Inside the corresponding handler you can reference the parent item (e.DetailTableView.ParentItem), extract its unique Customer identifier, get the Orders for that customer and pass them as source for the nested table.

Best regards,
Stephen
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[NS]

Hi Stephen,
I can follow your solution. However, you say: "...extract its unique Customer identifier, get the Orders for that customer "

Does that mean you have to open a new database connection to get the Orders for that customer (in DetailTableDataBind event ?) The orders were already available on the original databind of the customer object. So you have to retrieve them again ?

Thanks,
Nicolas

[Cres]

hi to all,

i think customer.orders has to be retrieved as stephen indicated, but it would really be nice if it need not be. hoping maybe on the next release...

-cris

[Yavor]

Hello Cris,

Basically, as shown in the example, when expanding a node, an extra query to the database is made, to populate the detail table. One possibility would be to set the HierarchyLoadMode to client, whereby all records will be fetched in one go, when the control is first loaded.

Regards,
Yavor
the Telerik team

Check out Telerik Trainer, the state of the art learning tool for Telerik products.

[NS]

I received a very helpful tip trough a support ticket.
Today, Telerik also posted a blog article (basically covers my support ticket): http://blogs.telerik.com/VladimirEnchev/Posts/08-08-11/How_To_RadGrid_hierarchy_from_objects_hierarchy.aspx

Regards,
Nicolas