The Entity Framework allows you to query your database and work with your database using more advanced techniques. It allows you to use different database servers such as SQL Server, Oracle or MySQL and to abstract away from your database schema to generate a conceptual level model to query against which allows for greater flexibility.
Creating your model
The core of using the Entity Framework is the Entity Data Model (EDM). The EDM is built up from three XML files, each of which defines a different part of your model and database. These files are the Conceptual Schema Definition Language (CSDL) which defines how the model can be queried from the viewpoint of the application, Mapping Schema Language (MSL) which defines the links between the conceptual level and the store, and the Store Schema Definition Language (SSDL) which defines your actual database schema as shown in Figure 1.
Figure 1: The EDM
The SSDL file contains your database ‘Store’. The first element within the xml is ‘EntityContainer’, this contains two different elements to describe the database structure. One is ‘EntitySet’ which maps a table or view in the database to the ‘EntityType’ within your model. The other attribute is the ‘AssociationSet« which describes references between two tables within your database. Alongside EntityContainer elements, you have ‘EntityType’ which have the columns of your table together with their data types, constraints and keys. There are also ‘Association’ elements which define relationships and their referential constraints. Finally, the ‘Function’ element outlines any stored procedures or functions with their parameters. In the end, this file contains a complete picture of your database detailed as XML for the entity framework to use when generating the classes to query against. The following listing is an example of the Northwind.Customers table within the SSDL file:
<EntityType Name="Customers"> <Key> <PropertyRef Name="CustomerID" /> </Key> <Property Name="CustomerID" Type="nchar" Nullable="false" MaxLength="5" /> <Property Name="CompanyName" Type="nvarchar" Nullable="false" MaxLength="40" /> ... </EntityType>Above the SSDL is the MSL, this again is XML-based and contains an ‘EntityContainerMapping’ element, containing ‘EntitySetMapping’ and ‘AssociationSetMapping’. The mapping elements detail the link between the CSDL and the SSDL. Here is the Northwind.Customers entry:
<EntitySetMapping Name="Customers" StoreEntitySet="Customers" TypeName="Northwind.Customers"> <ScalarProperty Name="CustomerID" ColumnName="CustomerID" /> <ScalarProperty Name="CompanyName" ColumnName="CompanyName" /> <ScalarProperty Name="ContactName" ColumnName="ContactName" /> ... </EntitySetMapping>The final file is the CSDL, this is what the model looks like to the application and how to query it. The file looks very similar to the SSDL, one additional item included in the EntityType is the NavigationProperty which defines the relationship between two models at the conceptual level. The entity from this file is as follows:
<EntityType Name="Customers"> <Key> <PropertyRef Name="CustomerID" /> </Key> <Property Name="CustomerID" Type="String" Nullable="false" MaxLength="5" FixedLength="true" /> <Property Name="CompanyName" Type="String" Nullable="false" MaxLength="40" /> <Property Name="ContactName" Type="String" MaxLength="30" /> ... <NavigationProperty Name="Orders" Relationship= "Northwind.FK_Orders_Customers" FromRole="Customers" ToRole="Orders" /> <NavigationProperty Name="CustomerDemographics" Relationship= "Northwind.CustomerCustomerDemo" FromRole="Customers" ToRole="CustomerDemographics" /> </EntityType>However, you still have to create the files. One way to do this is by hand, however that will get boring very quickly so Microsoft has released a set of tools to help. The basic tool is a command line application called edmgen.exe which can be pointed at any database and can produce either the complete set of files or a sub-set based on your requirements, and can also produce the class libraries in C# or VB.NET. The command to use this is:
EdmGen /mode:FullGeneration /project:Northwind /provider:System.Data.SqlClient /connectionstring:"server=.; integrated security=true;database=northwind"After running this, five files will be created. Northwind.csdl, Northwind.msl, Northwind.ssdl, Northwind.ObjectLayer.cs and Northwind.Views.cs. The first three have already been discussed, the latter two are the classes created to query your model which we will come to in a moment.
Microsoft has also released a designer which is built into Visual Studio 2008. After installing the tools, you will have a new item in the ‘Add New Item’ dialog called ‘ADO.NET Entity Data Model’. This will present you with a wizard where you select your database and the tables, stored procedures and views you want to be included in your model.
Figure 2: The Designer
Figure 2 is a screenshot of how the designer looks. The main screen is the design surface (highlighted in blue) displaying all of the entities, their properties and their relationship to other entities within the model. Using this design surface, you can add new entities, modify entities such as adding additional properties, associations, inheritance of your entities or remove them.
The model browser, highlighted in orange, details the contents of the CSDL (NorthwindModel) and the SSDL (NorthwindModel.Store) in a viewable fashion. If you right click on the model there is an option to update it from the database to include any schema changes.
The mapping detail, highlighted in red, is the information based in the MSL. This details which columns from the database\SSDL map onto the properties in the model\CSDL. This can also map the insert, update and delete operations for the entity to stored procedures or functions instead of auto generated code.
However, when you create the ADO.NET entity model using the designer it doesn’t appear to create the same files as the edmgen application. The files created are an edmx file and a .designer file. The edmx is in fact a combination of the CSDL, SDDL and MSL files until it is compiled when it is split into three separate files. The contents of the edmx file are identical. The following are the outer elements of the files.
<edmx:Runtime> <!- CSDL content -> <edmx:ConceptualModels>... </edmx:ConceptualModels> <!- SSDL content -> <edmx:StorageModels>... </edmx:StorageModels> <!- C-S mapping content -> <edmx:Mappings>...</edmx:Mappings> </edmx:Runtime>The .designer.cs file contains the classes for the entities, this is the combination of the Northwind.ObjectLayer.cs and Northwind.Views.cs files from edmgen.
The main class created is NorthwindEntites, this is derived from the built-in ObjectContext object which manages all data access involved with the entity framework, including connections, transactions, change tracking and relationships and is the gateway for all create, read, update and delete (CRUD) operations.
Inside the Entities class are a set of properties for each entity within your system. Each property returns a generic ObjectQuery<T>, where T is the class for the entity, the ObjectQuery is a built-in class which allows you to execute a query against the Store (i.e. the database).
The classes created have a series of properties as defined in the CSDL, the relationships are also created as properties, for example the Customers class has an Orders property which returns an ‘EntityCollection’ object. When querying the data, this allows you to navigate the relationships in an OO fashion.
The Entities class has extension hooks to use when developing your application. All of the generated classes are marked as partial; this means that you can create a separate class that is merged when compiled. This is very important as you cannot add your own code to the generated code as it will be removed the next time you make changes to the model. Using partial classes also improves the readability of code. The Entities also have a series of partial methods which are a new feature of C# 3.0. These can be implemented in partial classes to include additional logic. These partial methods are called before and after the value of the property changes, one possible use of this is validation of the value before it changes, or logging information for any changes to the information.
Querying the model
With the entity framework, there are multiple ways of querying the model. Deciding which approach to use will depend on the requirements for the application. However, you can mix and match the different approaches to query your model.To query your model, there is the Entity SQL (eSQL) syntax which is based on T-SQL with a few modifications for use with the Entity Framework. By using eSQL, the framework together with the database providers will be able to transform the eSQL query into SQL which the database uses for querying, such as T-SQL for SQL Server or PSQL for Oracle. eSQL is only designed for pulling data, and does not support updating or deleting data, for that you will need to use the ObjectServices or Linq to SQL discussed later.
To use eSQL, you can use the ADO.NET Data Provider which follows the same pattern as the SqlClient provider found in .NET 2.0. This means you can create an EntityConnection for the connection, an EntityCommand for the command to execute and use an EntityDataReader to process the data returned. One missing element is the EntityDataAdapter which has not been implemented by the team as DataSets doesn’t fit with the entity model approach.
The eSQL command looks similar to a standard T-SQL command, however it is querying your EDM (NorthwindEntities) and the Customers entity. There is an extra ‘VALUE’ keyword which is part of the new collection of keywords for eSQL. The value keyword is used to return the entity as actual data, executing the query below returns all of the properties of customer.
using (EntityConnection eConn =
new EntityConnection(
ConfigurationManager.ConnectionStrings
["NorthwindEntities"].ConnectionString))
{
string eSQL = "SELECT VALUE c FROM
NorthwindEntities.Customers as c";
EntityCommand eCmd = new
EntityCommand(eSQL, eConn);
eConn.Open();
EntityDataReader eDR =
eCmd.ExecuteReader(
CommandBehavior.SequentialAccess);
while (eDR.Read())
Console.WriteLine(
eDR["CustomerID"]);
}
If we wanted to return a subset we would use the ROW keyword, “SELECT VALUE row (c.CustomerID as CustomerID, c.ContactName as ContactName) FROM NorthwindEntities.Customers as c”, this would result in only the CustomerID and the ContactID being returned.
Finally, the other important part of querying the model is the connection string which has two important parts; one part is the standard connection string to connect to the server and database, the other relates to the location of the metadata files (CSDL, MSL, SSDL). Another important setting is the use of the MultipleActiveResultSets, which can be used for any ADO.NET 2.0 connection strings as it improves performance and memory usage and makes certain queries possible:
"metadata=.\Northwind.csdl|.\Northwind. ssdl|.\Northwind.msl; provider=System.Data.SqlClient; provider connection string="Data Source=.;Initial Catalog=Northwind; Integrated Security=True; MultipleActiveResultSets=True""The next approach is using ObjectServices which provides more abstraction than the ADO.NET pattern. ObjectServices takes advantage of the .NET classes which the designer, or edmgen, created for you when developing your model. ObjectServices allows you to query, update and delete the entities within your model all using OO techniques.
The following snippet demonstrates the same query as above but using the ObjectServices objects. However all the rows are being returned as strongly typed Customers objects which mean we can use the CustomerID property to gain access to the related column from the resultset.
string eSQL = "SELECT VALUE row
(c.CustomerID as CustomerID,
c.ContactName as ContactName)
FROM NorthwindEntities.Customers as c";
using(ObjectContext oc =
new ObjectContext(
ConfigurationManager.ConnectionStrings
["NorthwindEntities"].ConnectionString))
{
foreach(Customers c in
oc.CreateQuery<Customers>(
eSQL, new ObjectParameter[]{}))
{
Console.WriteLine(c.CustomerID);
}
}
The ObjectContext handles the lifecycle, while CreateQuery returns an ObjectQuery object which represents a query against the database.
An even easier way to query the model using ObjectServices is to use the derived classes which were created by the model designer. These still use the above code underneath however have been abstracted away for readability. Below is the same query but by using the property on the NorthwindEntities model we do not need to write any plumbing.
using(NorthwindEntities ne =
new NorthwindEntities())
{
foreach (Customers c in ne.Customers)
{
Console.WriteLine(c.CustomerID);
}
}
In both these two aproaches, the foreach statement calls the GetEnumerator() method on the ObjectQuery<T> which causes the SQL to be executed against the server and for the results to be converted into objects for use within the statement. However, if we wanted to access the Orders of the customer, the results aren’t returned as part of the original query as we only asked for the Customers. When we access the Orders property another query would be executed against the server, this is called Deferred Loading. In certain situations, deferred loading improves performance as only the data required is returned from the server; however it can lead to additional queries being executed unnecessarily. To solve this, as part of the query we can explicitly say which relationships we want to be loaded:
using (NorthwindEntities ne =
new NorthwindEntities())
{
foreach (Customers c in]
ne.Customers.Include(
"Orders.Order_Details"))
{
Console.WriteLine(c.CustomerID +
"\t Order Count: " +
c.Orders.Count);
foreach (Orders order in c.Orders)
{
Console.WriteLine("\tOrderID " +
order.OrderID + "\tItems: " +
order.Order_Details.Count);
}
}
}
As demonstrated here, by adding the Include method to our query, we can specify the relationships to navigate and the additional rows to load. For example, this query will load all of the Orders and Order Details records for each customer as a single query, when we output the results it will use this version instead of executing another query.
As we are using our derived entities, we can easily update the items within the database. The following snippet first queries the database to return all the customers who are based in the UK, the ‘it’ keyword refers to the current row, then updates the Country of the customer to be GB. To save the changes back to the database, SaveChanges() is called on the NorthwindEntities object:
using (NorthwindEntities ne =
new NorthwindEntities())
{
foreach (Customers c in
ne.Customers.Where(
"It.Country = 'UK'"))
{
c.Country = "GB";
Console.WriteLine("Updated: " +
c.CustomerID);
}
ne.SaveChanges();
}
To delete, instead of updating the property we would call ‘ne.DeleteObject(c);’.
Another approach is to use LINQ to Entities. This is an implementation of LINQ, just like SQL, XML and Objects, within 3.5, but allows you to query your entity model in the same fashion. Below we have the same query as before using the same NorthwindEntities object. For the connection we provide the name as in the App.Config and let the framework do the rest. We then use the LINQ syntax to select the Customers:
NorthwindEntities ne = new
NorthwindEntities(
"Name=NorthwindEntities");
var query = from c in ne.Customers
select c;
foreach (var cus in query)
{
Console.WriteLine(cus.CustomerID);
}
This is the same query as the first one using ObjectServices, but here we only return the entities we are interested in and store them in an anonymous object, a new feature of C# 3.0.
var query = from c in ne.Customers
select new { CustomerID =
c.CustomerID,
ContactName = c.ContactName };
Summary
To conclude, the Entity Framework is a very powerful way for querying your data, with the added advantage that you can query other database stores apart from SQL Server. You now know how you get started using the framework, and how to create and query your model using various different approaches.Ben Hall is a UK-based C# developer/tester who enjoys all aspects of the development lifecycle and technology in general. Ben works for Red Gate Software as a Test Engineer, while regularly blogging about .NET-related development.
Comments