ADO.NET EF and LINQ pagging records & translation to SQL?

For more information about the Entity Data Model see the EDM reference at the end of this document. The EDM is a conceptual data model that can be used to model the data of a given domain. However, at some point the data needs to be stored in an actual database, typically a relational database.

In order to provide a mechanism for storing data modeled using the EDM in relational databases, the ADO.NET" rel="nofollow">ADO.NET Entity Framework houses a powerful client-views infrastructure designed to manage the transformations between the logical database schema that's present in the relational store and the conceptual EDM schema used by the application. In addition to the EDM schema, the system takes as input a mapping specification; this mapping specification is produced by the mapping tools and is also an XML file. When the mapping tool is used to create a conceptual to logical mapping, it produces an XML file that can be consumed by the run-time components of the ADO.NET" rel="nofollow">ADO.NET Entity Framework.

The appendix includes the XML representation of the mapping shown above in the section 6.2 "Sample mapping represented as XML". Fortunately, tools will make it unnecessary for the vast majority of users to have to understand or deal with these XML files. In addition to providing support for surfacing schemas as EDM schemas, the client-views infrastructure in ADO.NET" rel="nofollow">ADO.NET has other benefits.

At the beginning of this section we discussed how databases with schemas now owned by the application developer can introduce complexity into the application code. By using client views, the complexity brought in by the external logical schemas can be stopped before it reaches the application code; instead, views can be created to perform any re-shaping required for the data the application consumes. That way the application has a view of the data that makes sense for the problem space that it addresses.

This is useful independently of whether new EDM constructs are used in the resulting model. An obvious question at this point would be why not just use traditional database views for this. While database views can abstract many of the mappings, often that solution won't work for several process and functional reasons: (a) many of the views are simply too complex to be generated and maintained by developers in a cost-effective way, even for some simple conceptual to logical mappings, (b) the classes of views that have the property of being automatically updatable at the store are limited, and (c) databases for core-systems in medium and large companies are used by many central and departmental applications, and having each individual application create several views in the database would pollute the database schema and create significant maintenance workload for the database administrators.

In addition, database views are limited to the expressivity of the relational model, and typically lack some of the more real-world concepts of the Entity Data Model, such as inheritance and complex types. ADO.NET" rel="nofollow">ADO.NET client-views work entirely on the client, so each application developer can create views that adapt the data to a shape that makes sense for each particular application without affecting the actual database or other applications. The class of updatable views supported in the Entity Framework is much broader than those supported by any relational store.

The EDM and mapping concepts seem to be fairly abstract at first, so at this point one could wonder how they are concretely surfaced in the ADO.NET" rel="nofollow">ADO.NET API. We chose to introduce a new data-access provider for ADO.NET" rel="nofollow">ADO.NET called the "mapping provider". Just like a regular provider connects to a store and provides the application with a view of the store data in its logical schema, the mapping provider connects to a conceptual EDM model and provides the application with a conceptual view of the data.

The mapping provider is given the EDM schema and the mapping information, so it can internally use the mapping infrastructure to translate between the logical and conceptual schemas. Note that the pattern should be very familiar to ADO.NET" rel="nofollow">ADO.NET developers; it looks just like ADO.NET" rel="nofollow">ADO.NET 2.0 code, with the only difference that it uses a different provider. Under the covers, the mapping provider will use the EDM schema and the mapping/view information to translate to and from the conceptual model.

Then it'll use a regular ADO.NET" rel="nofollow">ADO.NET provider to talk to the underlying database (e.g. It would use System.Data. SqlClient to talk to a SQL Server database). When an application uses an EDM model and the mapping provider to access it, it no longer connects directly to a database or sees any database-specific construct; the entire application operates in terms of the higher-level EDM model.

In order to enable query against EDM models, the ADO.NET" rel="nofollow">ADO.NET Entity Framework introduces a query language that's designed to work with the EDM and can leverage the full expressivity of the entity data model. The language is called Entity SQL and it should look familiar to all developers that have used some SQL dialect before. Entity SQL provides the Entity Framework with a dynamic query capability, where queries can be statically formulated at design time or constructed at runtime in the context of late bound applications.

The overall structure of an Entity SQL query is the usual SELECT-FROM-WHERE sequence that's present in traditional SQL. Dealing with entities. Conceptual EDM schemas are designed around entities.

Business concepts are reflected directly in EDM entities types whose instances are stored in entity-sets. In the same way queries in the relational world are formulated against tables, queries in the EDM world are formulated against entity-sets. So the starting point for a query is a set of entities coming from one or more entity-sets.