LINQ to Entities

By now, we have seen how to leverage LINQ queries to select data over SQL databases and DataSet instances, using a data-centric approach. Every single query we ran was against a particular data table, with a one-to-one mapping between data tables and query results. However, the real world of enterprise application development requires abstraction from a physical data persistence layer to guarantee maintainability and platform independency.

Whenever we work with an Entity Data Model (EDM), we create an abstraction, modeling entities at a conceptual level rather than at the data level. LINQ to Entities allows querying a list of entities abstracted from the physical data layer, using LINQ syntax. We define entities as instances of entity types (such as Order, Customer, Product, and so on), which are structured and built starting from any kind of persistence layer but independent of it. Entities are grouped into EntitySet objects and can be related to each other through relationships, which are instances of relationship types (such as CustomerOrders, OrderProducts, and so on).

With LINQ to Entities, we can leverage object inheritance and data complexity. For instance, we can have a complex Customer type that includes its Orders as a sequence property, inheriting its base structure from the Contact type. From an entity point of view, we do not care how the Contact, Customer, and Orders of the Customer are brought to us; we care about them only when they are in memory. On the other hand, under the cover, we need a program to query a particular database to extract the content of these entities. Whatever makes the magic is defined by the mapping between .NET classes and the database structure. This mapping is made of a set of files and can be defined using a tool, called with an EDM schema as input, that generates the .NET classes that map to the EDM structure. This tool maps the conceptual layer (entities) with the database physical schema (persistence layer), making the inner workings transparent. We can imagine having a Customer entity, defined starting from a subset of the classic Northwind sample application, like the one defined in Listing 5-33.

Listing 5-33: A handmade Customer entity

  public partial class Customer {     public string CustomerID {         get { return this._CustomerID; }         set { this._CustomerID = value; }     }     private string _CustomerID = string.Empty;     public string CompanyName {         get { return this._CompanyName; }         set { this._CompanyName = value; }     }     private string _CompanyName = string.Empty;     public string Country {         get { return this._ Country; }         set { this._ Country = value; }     }     private string _ Country = string.Empty; } 

We defined this entity by hand, and you probably know the effort required to keep each instance of Customer synchronized with a physical data layer, including eventually handling relationships with instances of SalesOrders. To solve this problem, you can use one of the many Object Relational Mapping (ORM) frameworks built on top of .NET, or you can simply base your solution on LINQ to Entities and ADO.NET Entity Framework. If you use LINQ to Entities, you should define any of your entities by using a designer for Visual Studio, or define them manually with a text or XML editor. Whatever technique you use, you must define a set of XML files that describes each Entity, EntitySet, and Association and that maps each of them to corresponding tables and views on a particular data layer. The result of this mapping will be a set of auto-generated classes that define entities such as the Customer shown in Listing 5-33, but with metadata information to influence the behavior of their instances. Listing 5-34 shows how this is done.

Listing 5-34: An ADO.NET Entity Framework auto-generated Customer entity

   [System.Data.Objects.DataClasses.EntityTypeAttribute(   SchemaName = "NorthwindLib", TypeName = "Customer" )] public partial class Customer : global::System.Data.Objects.DataClasses.Entity {   // ...   [System.Data.Objects.DataClasses.EntityKeyPropertyAttribute()]   [System.Data.Objects.DataClasses.NullableAttribute( false )]   public string CustomerID {     get { return this._CustomerID; }     set {       this.ReportPropertyChanging( "CustomerID",                   this._CustomerID );       this._CustomerID = global::System.Data.Objects.DataClasses.StructuralObject.Set ValidValue(  value, false, 5, true );       this.ReportPropertyChanged( "CustomerID", this._CustomerID );       }   }   private string _CustomerID = string.Empty;   [System.Data.Objects.DataClasses.EdmScalarPropertyAttribute()]   [System.Data.Objects.DataClasses.NullableAttribute( false )]   public string CompanyName {     get { return this._CompanyName; }     set {       this.ReportPropertyChanging( "CompanyName",                   this._CompanyName );       this._CompanyName = global::System.Data.Objects.DataClasses.StructuralObject.Set ValidValue(  value, false, 40, true );       this.ReportPropertyChanged( "CompanyName", this._CompanyName );       }   }   private string _CompanyName = string.Empty;   [System.Data.Objects.DataClasses.EdmScalarPropertyAttribute()]   [System.Data.Objects.DataClasses.NullableAttribute(true)]   public string Country {     get { return this._Country; }     set {       this.ReportPropertyChanging("Country", this._Country);       this._Country = global::System.Data.Objects.DataClasses.StructuralObject.SetValidValue(   value, true, 15, true);       this.ReportPropertyChanged("Country", this._Country);       }   }   private string _Country; } 

This second Customer class, shown in Listing 5-34, is broadly decorated with attributes of the System.Data.Objects.DataClasses namespace. These attributes provide information about the entity model, regardless of its database mapping. You can see that they describe relationships between the class properties and the schema model, not the physical database model. Listing 5-35 shows a sample query to select all the instances of Italian customers.

Listing 5-35: A query to select all the Italian customers

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

As you can see, the query uses an SQL-like syntax that works with typed entities (that is, instances of our Customer type) rather than with tables. A lot of the plumbing code-usually defined to work with databases, invoke specific SQL statements, iterate over records, and so forth-has been moved under the cover. Starting from the previous query, we can see that LINQ works with deferred queries, evaluated when we effectively access them, which are converted to the right extension method for the sequences we are querying. Consider Listing 5-36, which shows the LINQ query against the previously seen sequence of Customers entities.

Listing 5-36: A LINQ query over a sequence of Customers entities

  using (Northwind db = new Northwind( NWIND_CONNECTION_STRING ) ) {   var customers =     from   c in db.Customers     where  c.Country == "Italy"     select c;   foreach(Customer c in customers) {     Console.WriteLine( c );   } } 

The result of this query, when it is evaluated, will be the one shown in Listing 5-36. The query expression deferred evaluation will produce it. This approach can be very interesting whenever we define the business layer and the components that need to handle business entities, and handle them independently from the persistence layer and without concern for the real source of entity data sources.

With these concepts, you can make a case for using the capabilities of LINQ to Entities in everyday programming. More details on LINQ to Entities and ADO.NET Entity Framework are provided in the Appendix, “ADO.NET Entity Framework.”