[Solved] RadGrid + LINQ + JOINs

[James Legan]

I am trying to leverage the extensive editing capability built into RadGrid to replace a GridView / custom pages (insert/update code).

My data is being sourced from multiple tables and I am using LINQ to retrieve the data.

There is a small snippet of code below, all joined an an "OrgID" field. The join works fine and the resulting data is in fact what I am looking for. However, when I apply the data source and bind it, RadGrid cannot seem to display the columns, auto-generated or otherwise.

It might be related to how I am concanting the data (select new {p,o}) and if that is the case, I need to know what the prefered format of the data is.

My goal is to create a system using rad grid where I can edit a record and have it update as many as 10 tables at once that are all joined together without having to write any update/insert code (or very little).

                var v = (from p in m_db.OrgLists
                         join o in m_db.HostLocations on p.id equals o.OrgNameID
                         select new {p, o});

                // Get the data
                rgOrgList.DataSource = v;

                rgOrgList.DataBind();

Thanks in advance,

Jim

[James Legan]

Any insight?

[Nikolay Rusev]

Hello James,

The best possible solution to use RadGrid automatic operations(insert/update/delete) is with combination of LinqDataSource. It is absolutely codeless.
You can find a sample application attached to this post which demonstrates this.

You can find more information on the links below:
Automatic operations
Update/Insert/Delete in hierarchy
Automatic Operations with LinqDataSource

I hope this helps.

Sincerely yours,
Nikolay
the Telerik team

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

[Saurabh]

I'm facing the same problem, and the solution provided isn't good enough. For a grid with LINQ to SQL query(with joins) as the datasource, the grid shows the correct data. Following is the code for the datasource..

        protected void RadGrid1_NeedDataSource(object source, GridNeedDataSourceEventArgs e)

        {

            RadGrid1.DataSource = from site in DbContext.Sites

                                  from  address in DbContext.Addresses 

                                  where address.SiteID == site.SiteID

                                  orderby site.SiteName 

                                  select new {site, address

                                              ,addr = "Unit " + address.Unit + " " 

                                                     + address.StreetNumber + " " + address.Street + " " 

                                                     + address.Suburb + " " 

                                                     + address.State + " " 

                                                     + address.Country + " " + address.PostCode

                                              };

        }

The actual RadGrid code is as follows:-

   <telerik:radgrid ID="RadGrid1" runat="server" AllowFilteringByColumn="True" 

        AllowPaging="True" AllowSorting="True" 

        AutoGenerateColumns="False" 

        GridLines="None" OnNeedDataSource="RadGrid1_NeedDataSource" 

        OnUpdateCommand="RadGrid1_UpdateCommand"

        OnItemCreated="RadGrid1_ItemCreated" OnDeleteCommand="RadGrid1_DeleteCommand"

        OnInsertCommand="RadGrid1_InsertCommand" Skin="Web20">

    <MasterTableView DataKeyNames="site.SiteID" CommandItemDisplay="Top" EditMode="PopUp">

  

    <CommandItemSettings AddNewRecordText="Add new site" />

    <Columns>

        <telerik:GridEditCommandColumn ButtonType="ImageButton"/>

        <telerik:GridButtonColumn CommandName="Delete" ButtonType="ImageButton" Text="Delete" 

            UniqueName="column" ConfirmText="Delete this Site?" 

            ConfirmDialogType="RadWindow" ConfirmTitle="Delete">

        </telerik:GridButtonColumn>

        <telerik:GridBoundColumn DataField="site.SiteID" DataType="System.Int32" 

            HeaderText="SiteID" UniqueName="site.SiteID" ReadOnly="true">

        </telerik:GridBoundColumn>

        <telerik:GridBoundColumn DataField="site.AddrID" HeaderText="Addr ID"

            UniqueName="site.AddrID" >

        </telerik:GridBoundColumn>        

        <telerik:GridBoundColumn DataField="site.SiteName" HeaderText="SiteName" 

            UniqueName="site.SiteName">

        </telerik:GridBoundColumn>

        <telerik:GridBoundColumn DataField="site.SiteType" HeaderText="SiteType"

            UniqueName="site.SiteType" >

        </telerik:GridBoundColumn>

        <telerik:GridBoundColumn DataField="AddrID" HeaderText="AddrID">

        </telerik:GridBoundColumn>        

        <telerik:GridBoundColumn DataField="addr" HeaderText="Address" 

            ReadOnly="true">

        </telerik:GridBoundColumn> 

        <telerik:GridBoundColumn DataField="address.AddrID" DataType="System.Int32"

            HeaderText="Address ID" Display="false" ReadOnly="true">

        </telerik:GridBoundColumn>               

        <telerik:GridBoundColumn DataField="address.Unit" HeaderText="Unit" 

            UniqueName="Unit" Display="false">

        </telerik:GridBoundColumn> 

        <telerik:GridBoundColumn DataField="address.StreetNumber" HeaderText="StreetNumber" 

            Display="false">

        </telerik:GridBoundColumn> 

        <telerik:GridBoundColumn DataField="address.Street" HeaderText="Street" 

            Display="false">

        </telerik:GridBoundColumn> 

        <telerik:GridBoundColumn DataField="address.Suburb" HeaderText="Suburb" 

            Display="false">

        </telerik:GridBoundColumn>   

        <telerik:GridBoundColumn DataField="address.State" HeaderText="State" 

            Display="false">

        </telerik:GridBoundColumn>   

        <telerik:GridBoundColumn DataField="address.PostCode" HeaderText="PostCode" 

            Display="false">

        </telerik:GridBoundColumn>  

        <telerik:GridBoundColumn DataField="address.Country" HeaderText="Country" 

            Display="false">

        </telerik:GridBoundColumn>                                              

    </Columns>

<EditFormSettings>

<EditColumn ButtonType="ImageButton" />

</EditFormSettings>

</MasterTableView>

but the automatic updates/edits/deleted/etc don't work. I tried to create a RadGrid1_UpdateCommand event handler as follows..

        protected void RadGrid1_UpdateCommand(object source, GridCommandEventArgs e)

        {

            var editableItem = ((GridEditableItem)e.Item);

            var siteId = (int)editableItem.GetDataKeyValue("site.SiteID");

            var addrId = (int)editableItem.GetDataKeyValue("address.AddrID");

            //retrive entity form the Db

            var site1 = DbContext.Sites.Where(n => n.SiteID == siteId).FirstOrDefault();

            if (site1 != null)

            {

                editableItem.UpdateValues(site1);               

            }

            var addr = DbContext.Addresses.Where(n => n.AddrID == addrId).FirstOrDefault();

            if (addr != null)

            {

                editableItem.UpdateValues(addr);

            }

            try

            {

                //submit chanages to Db

                DbContext.SubmitChanges();

            }

            catch (System.Exception)

            {

                ShowErrorMessage();

            }

        }

Have spent 2 days trying to make this work. No proper documentation available for RadGrid using LINQ queries except a couple of samples. PLEASEEE HELPPP

[Nikolay Rusev]

Hello Saurabh,

You are binding your RadGrid to anonymous object produced by select new http://msdn.microsoft.com/en-us/magazine/cc163400.aspx

Even if you are using LinqDataSource and set Select property which is produce projection of the selection it will now allow you to set EnableUpdate/EnableInsert/EnableDelete as you are still having  anonymous object.

Other thing I noticed in you RadGrid usage is that you are binding it with OnNeedDataSource event. If you do this you cannot have automatic operations.

For more details about how to bind RadGrid to Linq with automatic operations please refer to the links from my previous post.

Regards,
Nikolay
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.