CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
Introduction
Welcome, .NET Developers, to this installment of the .NET Nuts & Bolts column. In my last column, Introducing the Entity Framework, I covered introductory examples on the Entity Framework and compared LINQ to SQL. For this piece, I’ll focus on building queries with the Entity Framework that involve multiple tables as well as binding in the UI with the EntityDataSource.
Queries with Joins
The Entity Framework is a new part of ADO.NET that allows you to build your applications against conceptual data models. It provides a greater level of abstraction and supports code that is independent of any particular relational database. It provides an Entity Data Model (EDM) for defining data at the database and conceptual level and mapping between the two. There are a nice set of tools that can be used to generate the Entity Data Model and corresponding objects from that represent the database. Part of that representation involves having a model of the relationships between entities. Figure 1 below depicts a snippet of an entity model and some of its relationships. Notice how the top half of an item such as Profile is made up of scalar properties. The bottom half of any given entity contains the associations/relationships with other entities.
Figure 1
The following example demonstrates the syntax involved in joining two entities together. Note the “equals” syntax used in the join statement. This syntax is different than comparison syntax in the query statement. Additionally, notice how the foreign key between the Review table and the Organization table is referenced for the Review. It is referenced through the Association name followed by any scalar properties. For example, my Review table has an in_OrganizationId that contains the reference. That table column is mapped to the actual in_id column in the Organization table through the association.
using (Example_DataEntities dc = new Example_DataEntities())
{
var review = (from r in dc.Review
join o in dc.Organization on r.Organization.in_id equals
o.in_id
where r.in_Id == reviewId
select new
{
o.vc_BusinessName,
o.ch_ProviderNum
}).FirstOrDefault();// …
}
It is also important with the entity model to understand how relationships are loaded. If we had selected the Review entity in the example above, that wouldn’t guarantee the related Organization information were already loaded. The reference that exists on the Review is lazy loaded by default and you must ensure it is loaded before use.
if (!review.OrganizationReference.IsLoaded)
{
review.OrganizationReference.Load();
}
It is important when deleting entities to ensure that its relationships are not loaded unless you are wanting to delete those records as well.
Stored Procedures
In working with LINQ and the Entity Framework, I’ve occasionally run in to situations where I can’t quite figure out the correct query statement to retrieve the data I need. It’s not that it can’t be done, but more that I run out of time or budget to continue to try and figure out the correct statement. At times like that, I’ve found it helpful to be able to revert to building the SQL statement I need as a T-SQL stored procedure and then including the stored procedure in the entity model. You update your entity model to include the stored procedure. From there, you right click in the model designer and choose the option to add a function import. During the import process you choose the stored procedure, set the desired name, and determine if there is no return type, scalar return type, or an entity return type. Refer to Figure 2 below for an example of what the function import user interface looks like.
Figure 2
Once you have performed the function import, then you will find your new method available within the context of your entity model. You call the item just like any other method and pass in any of the defined parameters. The example below demonstrates making a call to a stored procedure that has been setup as a function import to retrieve data to display on a dashboard based on the current user and search criteria.
using (Example_DataEntities dc = new Example_DataEntities())
{
this.gridViewReviews.DataSource =
dc.GetReviewsForDashboard(Page.User.Identity.Name,
this.txtSearch.Text);
this. gridViewReviews.DataBind();
}
Binding the UI with the EntityDataSource
The Entity Framework offers the EntityDataSource to use in binding the UI to your entity model. It implements an interface similar to other controls such as the LinqDataSource. You specify the context, entity set, and several other parameters that control the query.
Adjusting to EntityDataSource from the LinqDataSource
There is a primary difference between the EntityDataSource and the LinqDataSource, that is the ability to use the Selecting event to replace the results. With the LinqDataSource I’ve taken advantage of the Selecting event on a number of occasions such as with complex search forms where there are a number of criteria to factor in to the query and adjust. In the Selecting event I’d adapt and build the desired query to avoid getting hung up in a complexity of ControlParameters. The EntityDataSource does not support this same behavior. Instead, with the EntityDataSource you rely on the Select and Where or the CommandText properties to customize the query. The following example shows the use of the Select, Where, and WhereParameters to control the query. Note, you have the same kinds of options with the LinqDataSource, but I find it is more likely to need to depend on them with the EntityDataSource.
<asp:EntityDataSource ID=”ldsCompanies” runat=”server”
ContextTypeName=”DataLayer.Example_DataEntities”
EntitySetName=”Organization”
Select=”it.[in_id], it.[vc_BusinessName], it.[ch_ProviderNum]”
OrderBy=”it.[vc_BusinessName]”
Where=”it.[in_tenantId] == @in_tenantId AND
it.[vc_BusinessName] LIKE ‘%’+@OrganizationSearch+’%'”>
<WhereParameters>
<asp:ControlParameter ControlID=”txtOrganizationSearch”
PropertyName=”Text” Name=”OrganizationSearch” DbType=”String”
ConvertEmptyStringToNull=”false” />
</WhereParameters>
</asp:EntityDataSource>
The following sample shows the use of the CommandText property to build a custom query. Note the use of the CommandParameters, which are similar to WhereParameters.
<asp:EntityDataSource ID=”edSettings” runat=”server” ContextTypeName=”DataLayer.Example_DataEntities”
OrderBy=”it.[vc_Name]”
CommandText=”SELECT atf.in_Tenant_Id, atf.in_Feature_Id, atf.bt_Enabled,
atf.bt_Configurable, atf.bt_DefaultValue, af.vc_Name
FROM AppTenantFeatures AS atf
join AppFeatures AS af on atf.in_Feature_Id = af.in_Id
WHERE atf.in_Tenant_Id = @in_Tenant_Id
ORDER BY af.vc_Name”>
<CommandParameters>
<asp:ControlParameter ControlID=”ddlTenants” Name=”in_Tenant_Id”
PropertyName=”SelectedValue” Type=”Int32″ />
</CommandParameters>
</asp:EntityDataSource>
Summary
We covered examples of how to execute queries against the Entity Framework model that involve joining tables. We also explored how to use a stored procedure in the Entity Framework, which is certainly handy where there are complex queries you can’t figure out quite how to build with a LINQ query. Additionally, we explored the EntityDataSource and some of the differences between that and the LinqDataSource. Hopefully each of these topics will help you through the learning curve as you further adopt the Entity Framework within your solutions.
Future Columns
The topic of the next column is yet to be determined. If you have something else in particular that you would like to see explained here you could reach me at [email protected].
