There are SO many ways to “skin the data access cat” in Orcas that I’m getting a little cross-eyed. (Though this could be from looking at all of the XML in the Entity Data Models.)

I thought I would try to sort it out a little here since I have done this for some of the presentations I am putting together.

ADO.NET in Visual Studio Orcas

The “new” ADO.NET is not new, but added to. Evolution and backwards compatibility are key to the ADO.NET team. So in Orcas you will have what you know as ADO.NET 2.0 basically in tact. Then added onto that are two things: Entity Framework and integration with LINQ.

LINQ Integration in ADO.NET (in VS Orcas).

Painting a broad brush stroke to define LINQ is that it gives you the ability to query in-memory objects (that are iEnumerable).

 While there is the basic LINQ to Objects syntax, there are four derivatives of this.

LINQ to XML allows you to use LINQ to query XML.

There are three others that all fall under the umbrella of LINQ to ADO.NET.

The first of these is LINQ to DataSet. DataSets are in-memory objects, right? So we can query them, too. What we are really querying is an Enumerable collection of DataRows. Because of the special nature of DataSets, this “flavor” of LINQ needs to be specialized.

Next is LINQ to SQL. Of course, this taints my brushstroke because SQL is not an in-memory object. 🙂 However, as long as we have this fabulous new language enhancement, and we are all over querying databases, this version of LINQ was created to work directly with SQL, not only to query, but to update the data as well.

The last of the LINQs is LINQ to Entities. This lets us use LINQ to query the objects that are created by the Entity Framework. So let me jump to that and then come back to this one.

Entity Framework

Entity Framework is a whole new set of APIs added into the System.Data namespace.

The key to the framework is a set of three schema files.

The first file describes the conceptual layer of your business entities using a schema file. This is not replacing your objects. All we are describing is structure and basic metadata. Two of the (many) big advantages of this are that 1) in the end, we can write our data access code against this schema (which creates classes for us) rather than having to code up connections to the db, create complex joins to pull together info from various related tables and write lots of update logic and 2) we can describe these conceptual entities based on what we want them to look like, rather than how they are best organized in a database.

The second files describes the schema of the database, including pk/fk relationships.

The third file is a map between the first two. So that when you code against the entities, the framework can translate your requests (or updates) into what the database is expecting.

So those are just three files (which can be created directly from your db using a wizard or a command line tool and then edited manually).

Interacting with the schema files

There are two APIs that know how to make these schemas do their magic, Object Services and Entity Client.

Object Services

Object Services know how to work with the entities as objects. You can query against the entities and get objects as your results. These objects are managed in memory by Object Services and you can update the database easily with changes made to the objects. So I said the magic word, query.

There are two ways to query with the object services. 1) Directly using (yet ANOTHER query syntax…) Entity SQL, which is similar to using SQL and is built using strings. Here you would use the ObjectServices API to create a query object and then pass in the Entity SQL string 2) Indirectly using LINQ to Entities. If you use LINQ to Entities to query the entity classes, in the background LINQ to Entities will use Object Services to interact with the objects. Either way, ObjectServices will maintain in-memory knowledge of the objects for updates.

There are  providers being built to interact with other databases. Regardless of the database, once the schemas and mapping are built, everytihng you do on the client side will be the same.

A caveat is that if you use Object Services plus Entity SQL to query and in your query you use projections (request specific columns/properties instead of entire objects) you will not get an updatable object. Instead you will get an IEnumerable collection of DbDataRecords. LINQ to Entities will return objects even with projections, because that’s what LINQ knows how to do.

Entity Client

The last thing I wanted to talk about is Entity Client, the other way to access entities. EntityClient is a provider, similar to SQLClient and the others. It let’s you build your data access code to query the entities in a familiar way, with connections (though the connection points to your entity objects) and commands. With EntityClient, you build queries using Entity SQL syntax. EntityClient queries return a dbDataReader. You cannot do updates directly through EntityClient. An interesting thing about this provider is that if you are building an app that leverages the Provider Independent API, you can have code that easily flips back and forth from access data in SQL Server, Oracle, Access, or other data engines. So (though I haven’t tested this, I’m making an educated guess here) you can add EntityClient into this flexible model.

Summary

So now you can see why I’m a little cross-eyed. We are working with 6 new query languages (though they are all related),5 new ways of accessing data, trying to keep track of which syntax goes with which access method and which access method returns what type of data. And it is dizzying for sure. But after some investment in this, I have definitely gotten it sorted out. And you can too!

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!