How do I edit a Telerik RadGrid when using multiple tables as a data source

Question

Hi there, I have asked this question on Telerik forums with no reply so I'm turing to you.

I have seen this topic come up before but without an answer so I thought I'd define the problem as simply as possible.

I have a LINQ command creating a query:

var gridData = from au in dc.aspnet_Users join aur in dc.aspnet_UsersInRoles
on au.UserId equals aur.UserId join ar in dc.aspnet_Roles
on aur.RoleId equals ar.RoleId join am in dc.aspnet_Memberships
on au.UserId equals am.UserId select new { au.UserName, ar.RoleName, am.Email, am.IsApproved, am.IsLockedOut };

THE IMPORTANT POINT IS THAT THIS USES MORE THAN 1 TABLE.

Note: This is accessing the Roles and Users tables using the standard ASP.NET Membership framework.

Then I have a simple RadGrid:

<MasterTableView EditMode="InPlace"> 
   <RowIndicatorColumn> 
         <HeaderStyle Width="20px"></HeaderStyle> 
   </RowIndicatorColumn> 
   <ExpandCollapseColumn> 
         <HeaderStyle Width="20px"></HeaderStyle> 
   </ExpandCollapseColumn> 
</MasterTableView> 

Finally I bind the data to the grid in Page_Load

    rgUsers.AutoGenerateColumns = true; 
    rgUsers.DataSource = gridData.ToList(); 
    rgUsers.DataBind(); 
    rgUsers.EditIndexes.Add(1); 
    rgUsers.DataBind(); 

    GridView1.AutoGenerateColumns = true; 
    GridView1.DataSource = gridData.ToList(); 
    GridView1.DataBind(); 
    GridView1.EditIndex=1;         
    GridView1.DataBind(); 

This will display the data from the 4 tables correctly.

I included a GridView to compare functionality.

I also put the grid into Edit mode programatically to make sure that this was not just a problem with the Edit button.

However, when in Edit mode the RadGrid fields are not editable.

I have tried the following:

1) 1) I can use a LinqDataSource that allows Edit/Update BUT only if there is only 1 table in the LINQ query.

2) 2) I can use a ObjectDataSource onto a dataset but once again this only works if the the dataset adaptor is accessing a single table.

I have looked at the variouse suggested demos and example code but they have all ignored the multiple table/Join problem:

www.telerik.com/community/code-library/aspnet-ajax/grid/automatic-operations-with-linqdatasource.aspx

demos.telerik.com/aspnet-ajax/grid/examples/dataediting/threelevel/defaultcs.aspx

www.telerik.com/community/code-library/aspnet-ajax/grid/automatic-operations-with-linqdatasource.aspx

I am not wedded to LINQ. I am quite happy to adapt in order to get a RadGrid that can support Update and Edit to multiple tables.

What should I do to achieve a RadGrid that displays records composed from multiple tables with Update and Edit available?

Thanks for any help Richard

Answer 1

Have you considered using ADO.net's dataview class in conjunction with a dataset class? You should be able to pull you data using the system.data.sqlclient class with a stored proc if that is the type of database you're using. You can preserve your database's relationships in the dataset class and the dataview class will allow you to view the data as if it were a single table. Then you should be able to bind the dataview class to the Telerik control.

Answer 2

Why not using NeedDataSource binding as opposed to DataBind() calls? Thus the Telerik grid should manage its editing states without extra coding. If you use joins for different source tables and set you update or insert commands using appropriate T-SQL statements, everything should work fine. Check out this demo for a start:

http://demos.telerik.com/aspnet-ajax/grid/examples/dataediting/extractvalues/defaultcs.aspx

Dick

Answer 3

Turns out that the LINQ query returns an anonymous type that is read only.

I replaced it with a SQLDataSource using stored procedures so it works now.

Thanks