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
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.
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
where r.in_Id == reviewId
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.
It is important when deleting entities to ensure that its relationships are not loaded unless you are wanting to delete those records as well.
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.
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())
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
EntityDataSource does not support this same behavior. Instead, with the
EntityDataSource you rely on the
Where or the
CommandText properties to customize the query. The following example shows the use of the
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
<asp:EntityDataSource ID=”ldsCompanies” runat=”server”
Select=”it.[in_id], it.[vc_BusinessName], it.[ch_ProviderNum]”
Where=”it.[in_tenantId] == @in_tenantId AND
it.[vc_BusinessName] LIKE ‘%’[email protected]+’%'”>
PropertyName=”Text” Name=”OrganizationSearch” DbType=”String”
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
<asp:EntityDataSource ID=”edSettings” runat=”server” ContextTypeName=”DataLayer.Example_DataEntities”
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”>
<asp:ControlParameter ControlID=”ddlTenants” Name=”in_Tenant_Id”
PropertyName=”SelectedValue” Type=”Int32″ />
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.
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]