[Solved] Update Grid from multiple table data source

[Richard Byrne]

Hi There,
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 in the standard MS Login scenario.

Then I have a simple RadGrid:

<telerik:RadGrid ID="rgUsers" runat="server" AllowAutomaticDeletes="True"
        AllowAutomaticUpdates="True" AutoGenerateDeleteColumn="True"
        AutoGenerateEditColumn="True" GridLines="None">

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

</telerik:RadGrid>

Finally I bind the data to the grid in Page_Load

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

 

This will display the data from the 4 tables correctly. 

However, when Edit is pressed on the Grid the 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:

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

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

http://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 grid that displays records composed from multiple tables with Update and Edit available?

Thanks for any help

Richard

[Princy]

Hi Richard,

The issue is caused due to the fact ssss that you are binding your RadGrid to anonymous object produced by select new

Please  refer to the link below where an explanation has been provided on th same:

http://www.telerik.com/community/forums/winforms/gridview/radgrid-linq-editing-with-select-new.aspx

An alternative would be to create a stored procedure returning the result of the join . This will allow you  to edit,insert ,delete rows in the grid.

Thanks,

Princy

[Richard Byrne]

OK I see now, anonymous objects are read only.

I changed the grid code to use an SqlDataSource and wrote stored procedures to handle Update, delete

Thanks for the suggestion.