Querying Entities with ADO.NET

You have seen how to model your data layer at the right level of abstraction, using the ADO.NET metadata files and the EDM schema tools.

ADO.NET Orcas also provides a set of classes that allows you to work with the entities produced by the metadata files parsing result. Starting from the already defined and broadly adopted architecture of ADO.NET, this last version provides a new ADO.NET Managed Provider to manage entities instead of records.

Note 

An ADO.NET Managed Provider is a set of classes defined to manage a particular kind of data source. There are providers for SQL Server, Oracle, ODBC, OLE DB, and so on. All of them implement common interfaces defined by ADO.NET. Every developer can use them from an abstract point of view-accessing their members through common interfaces instead of using explicit type casting. The DbProviderFactories engine that we have previously seen is mainly based on this concept.

This new managed provider, defined within the System.Data.EntityClient namespace of System.Data.Entity assembly, offers an EntityConnection class that inherits from System.Data.Common.DbConnection, like any other ADO.NET connection does (for example, SqlConnection, OleDbConnection, and so on). EntityConnection accepts a particular connection string that requires not only a database connection, but also a path to the set of XML metadata files (CSDL, SSDL, and MSL) and the type of the provider to use for the physical data layer. Here is a sample connection, defined in an App.Config file, for an EntityConnection instance:

 <connectionStrings>   <add name="Northwind"     connectionString="metadata=.\Northwind.csdl|.\Northwind.ssdl|.\Northwind.msl;        provider=System.Data.SqlClient;provider connection string=&quote;Data Source=.;        Initial Catalog=Northwind;Integrated Security=True;        multipleactiveresultsets=true &quote;"        providerName="System.Data.EntityClient"/> </connectionStrings>

Pay attention to the MultipleActiveResultsets feature configured in the connection string. This feature is very useful whenever you need to dynamically load entities while traversing the objects graph. With that setting, the EntityClient provider can leverage multiple parallel queries to achieve this result.

EntityConnection can be used by an EntityCommand instance to select a set of entities instead of a set of records. In Listing A-7, you can see an example of a query that retrieves the full list of Northwind customer entities.

Listing A-7: A sample query against a set of entities, using EntityCommand and EntityConnection

  using (EntityConnection cn = new EntityConnection(NwindConnectionString)) {     EntityCommand cmd = new EntityCommand(         "SELECT c FROM NorthwindEntities.Customers AS c", cn);     cn.Open();     using (DbDataReader dr =                cmd.ExecuteReader(CommandBehavior.SequentialAccess)) {         while (dr.Read()) {             Console.WriteLine(((DbDataRecord)dr.GetValue(0))[0]);         }     } } 

The query provided as an argument to the EntityCommand constructor is interesting. It looks like a classic SQL query, but it is not. In reality, it is a query against the EntitySets of the conceptual model. NorthwindEntities is the EntityContainer, and Customers is the EntitySet of all the Customer instances. The result of this query is a list of DbDataRecord instances, each of which describes a customer.

By now, this code is not very interesting. In fact, it is not much different than any other code to query a database using ADO.NET. Nevertheless, consider the code automatically generated from the CSDL file. It defines a .NET type (a class) for each Entity defined in the source CSDL file-for instance, consider the Customer type shown in Listing A-8.

Listing A-8: An excerpt of the Customer type auto-generated from the CSDL file

  [System.Data.Objects.DataClasses.EntityTypeAttribute(     SchemaName = "NorthwindModel", TypeName = "Customer")] [System.Runtime.Serialization.DataContractAttribute()] public partial class Customer: System.Data.Objects.DataClasses.Entity {     // [...     [System.Data.Objects.DataClasses.EntityKeyPropertyAttribute()]     [System.Runtime.Serialization.DataMemberAttribute()]     [System.Data.Objects.DataClasses.NullableAttribute(false)]     public string CustomerID {         get { return this._CustomerID; }         set {           this.ReportPropertyChanging("CustomerID", this._CustomerID);           this._CustomerID =         System.Data.Objects.DataClasses.StructuralObject.SetValidValue(         value, false, 4000, true);           this.ReportPropertyChanged("CustomerID", this._CustomerID);     }   }   private string _CustomerID = string.Empty;   // [...     [System.Data.Objects.DataClasses.RelationshipPropertyAttribute(         SchemaName = "NorthwindModel",         RelationshipName = "FK_Orders_Customers", TargetEndName = "Order")]     [System.Xml.Serialization.XmlIgnoreAttribute()]     public System.Data.Objects.DataClasses.EntityCollection<Order> Orders {         get { return FK_Orders_Customers.GetOrderEntities(this);}     } } 

The Customer type inherits from the base class Entity defined in the common namespace System.Data.Objects.DataClasses. This base class provides all the features to manage object state, change events, validation rules, and so on. The code is decorated with .NET attributes taken from the same namespace of the Entity base class for the purpose of defining the behavior of each property to be compliant with the EDM definition. For instance, you can see that the CustomerID property is marked with an EntityKeyPropertyAttribute instance. This marking indicates that from a conceptual point of view the property is the unique identifier of each Customer instance. Moreover, these types are serializable as a DataContract of System.Runtime.Serialization version 3.0; thus, they can be used as message content in Windows Communication Foundation (WCF) services.

The code also defines a root .NET type called NorthwindEntities-which is also described in the CSDL file-that acts as a container for all the EntitySet instances. In Listing A-9, you can see an excerpt of its definition.

Listing A-9: An excerpt of the NothwindEntities type auto-generated from the CSDL file

  public partial class NorthwindEntities: System.Data.Objects.ObjectContext {     public NorthwindEntities()         : base("name=NorthwindEntities", "NorthwindEntities") { }     public NorthwindEntities( string connectionString )         : base(connectionString, "NorthwindEntities") { }     public NorthwindEntities( System.Data.Common.DbConnection connection,         System.Data.Metadata.Edm.MetadataWorkspace workspace )         : base(connection, workspace) { }     public System.Data.Objects.ObjectQuery<Customer> Customers {         get { return base.CreateQuery<Customer>("Customers"); }     }     public System.Data.Objects.ObjectQuery<Order> Orders {         get { return base.CreateQuery<Order>("Orders"); }     } } 

The class inherits from System.Data.Objects.ObjectContext and internally defines the Customers and Orders sets as instances of the generic type ObjectQuery. ObjectQuery describes a typed result of a query over a set of entities. We can use instances of NorthwindEntities to access and query all the typed and object-oriented entities. In Listing A-10, you can see an example of querying the list of Customer objects to extract all the customers.

Listing A-10: An ADO.NET Entities query to extract all the customers

  using (NorthwindEntities db = new NorthwindEntities()) {     var customers = db.CreateQuery<DbDataRecord>(         "SELECT c FROM NorthwindEntities.Customers AS c");     foreach (var c in customers) {         Console.WriteLine( ((Customer)c[0]).Display() );     } } 

We create the query over entities by invoking the CreateQuery generic method of the NorthwindEntities object. The CreateQuery method is defined in the base ObjectContext class and does not return its result immediately; instead, it defines a query tree that will be evaluated when it is enumerated. This behavior suggests a potential conjunction with LINQ queries, which we will discover later in this appendix. The result of the query is a set of DbDataRecord objects, each of which contains a single column row representing a single Customer instance. In fact, we cast the zero column of each row into a Customer instance and invoke a custom extension method to Display the result.

We are working with a typed environment, and Customer is not a database row, but a defined and known entity. Therefore, we can invoke the CreateQuery method, assigning to its generic type the Customer type itself, as shown in Listing A-11.

Listing A-11: An ADO.NET Entities query to extract all the typed customers

  using (NorthwindEntities db = new NorthwindEntities()) {     var customers = db.CreateQuery<Customer>(         "SELECT VALUE c FROM NorthwindEntities.Customers AS c");     foreach (var c in customers) {         Console.WriteLine(c.Display());     } } 

We used the VALUE keyword to ask for an already typed and fully compiled Customer instance-for each item in the physical data storage-as a result to the query engine.

We can also filter entities based on conditions, which are eventually mapped to parameters. Consider the sample code in Listing A-12, in which we extract all the customers located in Italy.

Listing A-12: An ADO.NET Entities query to extract all the customers located in Italy

  using (NorthwindEntities db = new NorthwindEntities()) {     var customers = db.CreateQuery<Customer>(         "SELECT VALUE c FROM NorthwindEntities.Customers AS c " +         "WHERE c.Country = 'Italy'");     foreach (var c in customers) {         Console.WriteLine(c.Display());     } } 

We can also use parameters to feed a parametric query instead of using explicitly declared values. In Listing A-13, you can see a parametric query used to filter customers on a Country property basis.

Listing A-13: An ADO.NET Entities query to extract all the customers of a particular country

  using (NorthwindEntities db = new NorthwindEntities()) {     var customers = db.CreateQuery<Customer>(         "SELECT VALUE c FROM NorthwindEntities.Customers AS c " +         "WHERE c.Country = @Country");     customers.Parameters.Add(new ObjectParameter("Country", country));     foreach (Customer c in customers) {         Console.WriteLine(c.Display());     } } 

Under the cover, the System.Data.EntityClient framework parses the queries over entities and converts them to SQL queries to extract from the physical data layer the data mapped on the entities. After this, it loads the entities with the content received from the database server. In the end, this is a smart and maintainable surrogate for a custom data layer. In the following block, you can see the SQL code produced to execute the query described in Listing A-13:

 exec sp_executesql N'SELECT 0 AS [C1], [Extent1].[CustomerID] AS [CustomerID], [Extent1].[CompanyName] AS [CompanyName], [Extent1].[ContactName] AS [ContactName], [Extent1].[Country] AS [Country] FROM [dbo].[Customers] AS [Extent1] WHERE [Extent1].[Country] = @Country',N'@Country nvarchar(7)',@Country=N'Germany'

Important 

The SQL queries generated by ADO.NET Entity Framework that we illustrate in this chapter are only indicative. Microsoft reserves the right to change the SQL code that is generated, and sometimes we simplify the text. Therefore, you should not rely on it.

You can probably appreciate that the querying engine of EntityClient tries to optimize query plans and avoids SQL injection by using parametric queries. The EDM also defines relationships between entities. Consider the example in Listing A-14, in which we retrieve all orders filtered by customers of a specific Country.

Listing A-14: An ADO.NET Entities query to extract all the orders of customers of a specific country

  using (NorthwindEntities db = new NorthwindEntities()) {     ObjectQuery<Order> orders = db.CreateQuery<Order>(         "SELECT VALUE o FROM NorthwindEntities.Orders AS o " +         "WHERE o.Customer.Country = @Country");     orders.Parameters.Add(new ObjectParameter("Country", country));     foreach (Order o in orders) {         Console.WriteLine(o.Display());     } } 

In this last example, we traverse the object graph to filter each Order on a Customer’s property basis. There are many other opportunities for querying entities. For instance, in Listing A-15 we extract all customers who placed orders in the last two years, leveraging the NAVIGATE keyword to traverse an object relationship between Customer and Order instances.

Listing A-15: An ADO.NET Entities query to extract all the customers who placed orders in the last two years

  using (NorthwindEntities db = new NorthwindEntities()) {     var customers = db.CreateQuery<Customer>(         "SELECT VALUE c FROM NorthwindEntities.Customers AS c " +         "WHERE EXISTS( " +         " SELECT VALUE o " +         " FROM NAVIGATE(c, NorthwindModel.Relationship_Orders_Customers)" +         " AS o " +         " WHERE o.OrderDate > @Date)");     customers.Parameters.Add(new ObjectParameter("Date",         DateTime.Now.AddYears(-2)));     foreach (var c in customers) {         Console.WriteLine(c.Display());     } }