Selecting Data by Joining Tables using ADO.Net Entity Framework

In order to select data from a Model using the ADO.Net Entity Data Model, we should first create an ADO.Net Entity Data Model class, refer to the post Creating your First ADO.Net Entity Data Model to see on how to create an ADO.Net Entity Data Model, that maps to the physical database.

Once you have created the ADO.Net Entity Data Model class, we can proceed with querying the model to select data. In this post we shall see on how to use the ADO.Net Entity Data Model to query the data from more than 1 underlying physical database and display the results of the query in a GridView.

1. Create a new .aspx page, I have created a page ViewEmployees.aspx
2. Add a GridView control to the page, which will display the results of the Select Query.
3. Add the necessary Bound and Template columns to the Grid.

<asp:GridView

    ID="grdEmployees"

    runat="server"

    CellPadding="2"

    CellSpacing="2"

    HeaderStyle-Font-Names="Verdana"

    HeaderStyle-Font-Size="15px"

    HeaderStyle-BackColor="Silver"

    RowStyle-Font-Names="Verdana"

    RowStyle-Font-Size="14px"

    AutoGenerateColumns="false" onrowcommand="grdEmployees_RowCommand">

    <Columns>

        <asp:BoundField DataField="ID" HeaderText="Employee ID" />

        <asp:BoundField DataField="Name" HeaderText="Name" />

        <asp:BoundField DataField="Phone" HeaderText="Phone" />

        <asp:BoundField DataField="DepartmentName" HeaderText="Department" />

        <asp:BoundField DataField="Salery" HeaderText="Salery" />

        <asp:TemplateField HeaderText="EDIT">

            <ItemTemplate>

                <a href="ViewEmployees.aspx?ID=<%#Eval("ID")                 

                  %>">EDIT</a>

            </ItemTemplate>

        </asp:TemplateField>

        <asp:TemplateField HeaderText="DELETE">

            <ItemTemplate>

                <asp:LinkButton ID="lnkDelete" runat="server"

                    CausesValidation="false" CommandArgument='<%#Eval("ID") %>'

                    CommandName="lnkDeleteButton" OnClientClick="return confirm('Are                        You Sure, You Want to Delete this Employee?');"

                    Text="DELETE">

                </asp:LinkButton>

            </ItemTemplate>

        </asp:TemplateField>

    </Columns>

</asp:GridView>

4. Once we have the Grid in place, we shall switch to the code-behind to use the ADO.Net Entity Data Model to query the database.
5. Add a reference to the ADO.Net Entity Data Model

EmployeesEntities dbContext = new EmployeesEntities();

Refer to the post Creating your First ADO.Net Entity Data Model, to see on how to add an ADO.Net Entity Data Model class to the project, which maps to the Physical database.

6. Now use the reference, to run Queries against the model as follows.

var emp = (from e in dbContext.Employee

           select new { e.ID,

                        e.Name,

                        e.Phone,

                        DepartmentName = e.Department.Name,

                        e.Salery });
//

grdEmployees.DataSource = emp.ToList();

grdEmployees.DataBind();

7. Notice that we have not explicitly joined the tables in the query, insead we are just refering to the Name in the Department Table, ADO.Net Entity Framework does the Table Join internally.

8. That’s it once the Application runs the LINQ Query gets executed and the results get displayed on the Grid.
Related Post
Concepts
ADO.NET Entity Framework
ADO.NET Entity Framework Architecture
Entity Data Model
LINQ To SQL Vs ADO.Net Entity Framework

Programming
ADO.Net Entity Data Model Prerequisite
Creating your First ADO.Net Entity Data Model
Selecting Data using ADO.Net Entity Framework
Selecting Data by Joining Tables using ADO.Net Entity Framework
Inserting Data using ADO.Net Entity Framework
Inserting Data with Foreign Key Reference using ADO.Net Entity Framework
Updating Data using ADO.Net Entity Framework
Updating Data with Foreign Key Reference using ADO.Net Entity Framework
Deleting Data using ADO.Net Entity Framework