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!

I wrote up a list of some of the invaluable resources I have been relying on while diving deeply into the new March CTP of Orcas.

[A DevLife post]

In a previous interation, I managed to track down some sample code that built a LINQ call to a stored procedure, the class which it returns and executed it in LINQ to SQL.

The code has changed dramatically in the March CTP. Luckily, Mike Taulty beat me to it and I leveraged the C# code in his post to revise mine.

One of the things I was so silly to do in my previous test was to hand code the return class rather than just create it in the designer and return that.

But all of this is not totally necessary since you can now drag and drop stored procs onto the LINQ to SQL Visual Modeler and make calls to it. Interestingly the return type is just created on the fly at run time so it’s not even an issue – yay to anonymous types.

However, it’s still nice to know how to build this stuff becasue drag n drop doesn’t solve every coding problem. So check Mike’s post for the C# version.

Here is a working version in VB.

“CustOrderHist” is the name of the stored procedure in my database. 

What this is doing is

1) using the attribute to hook it up to the sproc
2) taking in a parameter of customerID and associating it with the parameter @CustomerID in the stored proceudre
3) Returning and IEnumerable of type CustOrderHistoryResult (the class I created in the designer that matches the structure of what the sproc returns).

On the client side, I can now take the result and do whatever I want with it, such as bind it to a gridview on a web page.

<StoredProcedure(Name:=”CustOrderHist”)> _
Public Function GetCustOrderHist(<Parameter(Name:=”CustomerID”)> ByVal customerID As String) As IEnumerable(Of CustOrderHistoryResult)

  Dim result As IQueryResults(Of CustOrderHistoryResult) = _
  ExecuteMethodCall(Of CustOrderHistoryResult)(Me,
  MethodInfo.GetCurrentMethod, customerID)
    
  Return result

  End Function

VB9 has come a long way in the new March CTP of Orcas! Paul Vick has a quick list of what is now in there. And there are a lot of good details in the msdn documentaiton that comes along with the CTP.

A few things I noticed quickly when bringing my LINQ for SQL demos over to the new bits.

• First, and happily, the named parameter syntax of := is no longer necessary. You can just type =, like a normal person.
• You need to be explicit about using the reference to the entity when refering to properties. In other words where I could say
   From s In db.Suppliers_
              Where CompanyName = “Exotic Liquids” …
  I now need to put  “s.” in front of the CompanyName property
• The anonymous types have been fleshed out more and you need to use the With keyword when creating one.
• Also in the anonymous types, when you are definining a new named parameter, you need the “.” in front of that parameter, so it is obviously a property of the anonymous type.
• Maybe this isn’t the ultimate way to do it, but it seems that if you want to further leverage one of the properties of that anonymous type, eg to order on, then you need to name the anonymous type and reference it in the order by clause. Again, maybe this isn’t THE way, but it’s the way I got my code working again.

Here’s a before (as in earlier CTP) and an after (March CTP) example of a simple query that does all of these things.

OLD
From s In db.Suppliers _
            Select New {Company := CompanyName, Country, Products}

NEW
From s In db.Suppliers _
            Select New With {.Company = s.CompanyName, s.Country, s.Products}

Here’s what it looks like with a nested query
  From s In db.Suppliers _
            Select New With {.Company = s.CompanyName, s.Country, _
            .Products = (From p In s.Products _
                Select New With {p.ProductName})}

OLD with Order By
From s In db.Suppliers _
            Select New {s.CompanyName, s.Country, s.Products} _
            Order By CompanyName

NEW with Order By
  Dim mylist = From s In db.Suppliers _
            Select s2 = New With {s.CompanyName, s.Country, s.Products} _
            Order By s2.CompanyName

I’ve seen samples where “s” has been used for both the reference to the items in the db.suppliers collection AND to the name of the new anonymous type. That scares me, so I used a new variable (s2).

There is also a whole new set of query samples in the MSDN documentation.

The ADO NET Team has been cranking out fab content on their blog. Brian Dawson just wrote a post all about Object Services.

In trying to sort out all of the options that we now have with Entity Framework and LINQ, I’m always happy to see a list like this. But go read the whole post so nothing is out of context

What can you do with Object Services?

Here’s a bullet list of some of the normal operations which object services can provide:

·         Query using LINQ or Entity SQL

·         CRUD

·         State Management – change tracking with events

·         Lazy loading

·         Inheritance

·         Navigating relationships

More features of Object Services allow for data conflicts resolution. For example, Object Services supports:

·         Optimistic concurrency

·         Identity resolution –  keeping on copy of the object even from several different queries

·         Merging

·         Refreshing data with overwrite options (ie: server wins or client wins)