Sql – Linqdatasource and relational data problem

gridviewlinq-to-sqllinqdatasourcepaging

I have a problem with linqdatasource. I have gridview in my page and I set it's datasource to linqdatasource,also I set AllowPaging="True" , AllowSorting="True".

<asp:GridView ID="cityGrid" runat="server" AutoGenerateColumns="False" 
DataKeyNames="CityId" AllowPaging="True" 
AllowSorting="True" DataSourceID="LinqCityData">

Now in linqdatasource I want to retrieve data from two tables (relational tables with FK), there is no problem in this step.
I can use Select property of linqdatasource like this to select from other table

<asp:LinqDataSource ID="LinqCityData" runat="server" 
ContextTypeName="ContactSysDataContext" 
TableName="Office_ContactSys_Cities" 
Select="new (CityId, CityName , Office_ContactSys_Province.ProvinceName)">
</asp:LinqDataSource>

or I cas use Selection event in linqdatasource 

protected void LinqCityData_Selecting(object sender, LinqDataSourceSelectEventArgs e)
    {
        ContactSysDataContext db = new ContactSysDataContext();
        var CityResult= from p in db.Office_ContactSys_Cities join o in db.Office_ContactSys_Provinces on p.ProvinceId equals o.ProvinceId select new { o.ProvinceName, p.CityId, p.CityName };
        e.Result = CityResult;
    }

but after this step I can't use automatic delete in linqdatasource and instead I recieve this error:

LinqDataSource 'LinqCityData' does not
support the Select property when the
Delete, Insert or Update operations
are enabled

Here is my question: How can I implement paging in gridview (of course for relational tables) using linqdatasource (linqdatasource with enabled delete or update)?

Best Answer

If you remove the select statement from the linqdatasource, you will not get that error anymore. And then you can use update, delete and insert. Oh you'll also have to enable the delete insert and update in the datasource.

Here's an example:

<asp:LinqDataSource ID="LinqDataSource1" runat="server" 
    ContextTypeName="Custom.Data.DataAccessDataContext"

    TableName="CustomerSegmentMappings" 
    EnableDelete="True"
    EnableInsert="True"
    EnableUpdate="True">
</asp:LinqDataSource>
Related Topic