Retrieving Related Data with OpenAccessLinqDatasource

[Johann]

Hi there

I'm trying to retrieve a some basic related tables using an OpenAccessLinqDatasource control. I can retrieve one table just fine, but I get stuck trying to use the Custom Grouping as described in the documentation.

Basically I would like to have this query (which works just fine in SQL), work in the DataSource on my page:

"Select custorderitems.quantity, prodmain.itemtitle from custorderitems INNER JOIN prodmain on custorderitems.fkstoreitem = prodmain.id"

I've attached screenshots of the configuration screen where I enter the Custom Select Expression etc. Using a custom expression of new(quantity) actually works, I get that column in the RadGrid. The problem comes in when I specify another context with a field.. If I add Prodmain.itemtitle, I get no data at all.

Is there perhaps a clearer guide or tutorial explaining the Custom Grouping option of the OpenAccessLinqDatasource??

Regards
JohannS

[Doroteya]

Hi Johann,

It is possible to execute the mentioned query through OpenAccessLinqDataSource, although loading data from multiple tables though one datasource instance is not a scenario that is supported out-of-the-box. The key moments in the implementation are the handling of the Data Access context, and the usage of the QueryCreated event of the datasource. 

Attached to this message is a demo of the implementation, and following you can find the general guidelines for reproducing it on your side:

I. Exposing the context - The scenario requires you to supply the datasource with an instance of the context, which will be different from the one it would have used by default. To satisfy this requirement, you need to expose the context as a field on the page, and to consume its value during the ContextCreating event of the datasource. Here are the details:
    1. In this article, you will find a step-by-step tutorial, which shows an approach for managing the life-cycle of the context with the help of HttpContext, and consuming it in a web scenario (as a field of the page). You need to implement it on your side.
    2. Once, you expose the context, go to the XML representation of the page and:
        - Add a new OpenAccessLinqDataSource.
        - Make sure that only the Select Telerik Data Access Context page of the wizard provides configuration for the datasource.
        - From the Properties window, navigate to the ContextCreating event of the datasource and create an event handler for it in the backend of the page.
        - In the event handler, assign the value of the context field of the page to the ObjectInstance property of the event argument.

As a result of the latter four steps, the definition of the datasource and the code in the backend should be similar to the demonstrated below:

<telerik:OpenAccessLinqDataSource ID="myDataSource" runat="server"
    ContextTypeName="DataAccessModel2.EntitiesModel" EntityTypeName=""
    ResourceSetName="MyFirstTables" OnContextCreating="myDataSource_ContextCreating">
</telerik:OpenAccessLinqDataSource>

protected void myDataSource_ContextCreating(object sender,
    Telerik.OpenAccess.Web.OpenAccessLinqDataSourceContextEventArgs e)
{
    e.ObjectInstance = this.dbContext;
}

II. Providing a custom query to OpenAccessLinqDataSource - At this point the query you need to execute should be translated into a LINQ query and it should be provided to the datasource through an event handler for the QueryCreated even. For the translation of the INNER JOIN clause, I am using the simple join on statement in LINQ. The code should resemble the one below:

protected void myDataSource_QueryCreated(object sender, QueryCreatedEventArgs e)
{
    e.Query = from ft in dbContext.MyFirstTables
          join st in dbContext.MySecondTables on ft.MyFirstTableID equals st.MyFirstTableID
          select new
          {
              ID = ft.MyFirstTableID,
              Quantity = ft.Quantity,
              Title = st.ItemTitle
          };
}

III. Testing the result from the query - to test the query result, you can bind a grid, for example to the datasource. The definition could be similar to the following:

<telerik:RadGrid ID="RadGrid1" runat="server" DataSourceID="myDataSource"
         GroupPanelPosition="Top" CellSpacing="-1" GridLines="Both">
    <MasterTableView AutoGenerateColumns="False" DataSourceID="myDataSource"
                     DataKeyNames="MyFirstTableID">
        <Columns>
            <telerik:GridBoundColumn DataField="Quantity"
                     FilterControlAltText="Filter Quantity column"
                     HeaderText="Quantity" SortExpression="Quantity"
                     UniqueName="Quantity">
                <ColumnValidationSettings>
                    <ModelErrorMessage Text=""></ModelErrorMessage>
                </ColumnValidationSettings>
                <ColumnValidationSettings>
                    <ModelErrorMessage Text="" />
                </ColumnValidationSettings>
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Title"
                     FilterControlAltText="Filter Title column"
                     HeaderText="Title" SortExpression="Title"
                     UniqueName="Title">
                <ColumnValidationSettings>
                    <ModelErrorMessage Text=""></ModelErrorMessage>
                </ColumnValidationSettings>
                <ColumnValidationSettings>
                    <ModelErrorMessage Text="" />
                </ColumnValidationSettings>
            </telerik:GridBoundColumn>
        </Columns>
    </MasterTableView>
</telerik:RadGrid>

IV. Running the demo application - to start the demo on your side, you can apply the next workflow:
1. Unzip the archive in a convenient folder.
2. Make sure that the Northwind sample database is available on your local instance of SQL Server. If it is not, you can find the relevant files in archive.
3. Open the solution in Visual Studio.
4. Upgrade the references of Data Access as described here and the references of UI for ASP.NET AJAX as described here.
5. Edit the web.config file of the web application to provide valid credentials in the connection string. You need to follow this pattern:

<add name="NorthwindConnection"
     connectionString="data source=MACHINENAME\SERVERNAME;
            initial catalog=Northwind;integrated security=True"
     providerName="System.Data.SqlClient" />

6. Build the solution and run it for a test.

I hope this helps. Do let us know, if you need further information or assistance.


Regards,
Doroteya
Telerik