Monday, December 11, 2006

ADO.NET vNext Tutorial

So, I took some time over the past weekend to check out ADO.NET vNext. In case you don't know what ADO.NET vNext is, it is an O/R mapping framework that will be included in C# 3.0, and will work seamlessly with Language Integrated Query (LINQ).

Anyway, if you are interested, below is a simple tutorial as to how to get up and running.

Prerequisites:

Before you get started, you are going to need to download the August CTP of the ADO.NET vNext framework. Note: There are different teams as Microsoft working on LINQ and ADO.NET vNext, so vNext has a different build of the LINQ assemblies that the latest CTP of LINQ, so keep that in mind when making references in projects.

Create Database Structure:

The ADO.NET vNext CTP includes a utility that will read a database schema and create all of your mappings (EDM) for you, which is pretty handy, although you'll see below that we need to do some tweaking of those files to get a model that is suitable. Below is a simple DB Schema that I used for this tutorial:

CREATE DATABASE [ADOvNext]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Firms]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Firms](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Description] [ntext] NULL,
CONSTRAINT [PK_Firms] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[People]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[People](
[ID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[MiddleName] [nvarchar](50) NULL,
[FirmID] [int] NOT NULL,
CONSTRAINT [PK_People] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys
WHERE object_id = OBJECT_ID(N'[dbo].[FK_People_Firms]')
AND parent_object_id = OBJECT_ID(N'[dbo].[People]'))
ALTER TABLE [dbo].[People] WITH CHECK
ADD CONSTRAINT [FK_People_Firms] FOREIGN KEY([FirmID])
REFERENCES [dbo].[Firms] ([ID])
GO
ALTER TABLE [dbo].[People] CHECK CONSTRAINT [FK_People_Firms]
Create New ADO.NET vNext Project:

First, I want to create a new blank solution to add my entity project and my test project to. So, open Visual Studio, click File on the toolbar, then New and choose Project... Expand Other Project Types in the Project types tree and highlight Visual Studio Solutions, and select Blank Solution from the Templates view. Call the solution "ADOvNextTutorial".

Then, right-click on the newly created solution, and choose Add... and then New Project. Expand Visual C# from the Project types tree, then highlight ADO.NET vNext CTP, and select Model Object from the Templates view. Call this project "ADOvNextTutorial.Model".

After you click OK, you will see the Entity Data Model Wizard. Choose Generate from Database and click Next. Enter your server information, and pick your newly created database, and click Next. On the next screen, leave all the tables selected and click Finish.

At this point you will noticed that your project contains a one class, and three XML files. The model class is just an empty class, and you will see in this tutorial, that we won't have to add any more code to this class to get a fully operational set of business entities.

EDM Schemas and Mapping Specifications:

The XML files are where the meat and potatoes are at. These are used to describe your entities, map your entities to the database structures, and the framework uses Compilation Providers to generate the business entities as you edit the XML. Now, the vision of the ADO.NET team, is to have you do all of this modeling in the class designer, which will in turn, edit these underlying XML files. But until then, we will need to edit these by hand. Thankfully they are generated initially for us.

Now, I won't get too far into the EDM schemas and mapping specifications, because as you can imagine, there are so many possible configurations and mappings that are possible when modeling databases and the entities that expose that data. But, the gist of the three files is this. There is one that describes the business entities (.csdl), there is another that defines the DB entities (.ssdl), and finally one that defines the mapping between the two (.msl).

Create Test Project:

Now, before we get too far, I want to create our test project for testing our new entities. Right-click on the solution, and select Add... and then New Project, and highlight Visual C# in the Project types tree, then select Class Library. Name the project "ADOvNextTutorial.Model.IntegrationTest". Let's go ahead and rename Class1.cs that was created to ModelTest.cs. Now we should add the appropriate references to our Model project and our NUnit, ADO.NET vNext and LINQ assemblies. Add the following references:

  • Project reference to ADOvNextTutorial.Model
  • System.Configuration
  • NUnit.Framework
  • System.Query at {PROGRAMS DIR}\Microsoft SDKs\ADO.NET vNext CTP\Bin
  • System.Data.CTP at {PROGRAMS DIR}\Microsoft SDKs\ADO.NET vNext CTP\Reference
  • System.Data.Entity at {PROGRAMS DIR}\Microsoft SDKs\ADO.NET vNext CTP\Reference

Another thing we have to do is edit the project file so that the test project uses the C# 3.0 compiler. To do this, right-click on the ADOvNextTutorial.Model.IntegrationTest project and select Unload Project. Right-click on the unloaded project and select Edit ADOvNextTutorial.Model.IntegrationTest.csproj. This will open the project as XML. At the bottom of your project file, replace:

With:

Close the XML file and then right-click on the project and select Reload Project.

Next, we need to add the configuration file that will provide the connection string information, as well as the compilation provider info so we can get Intellisense on the generated EDM models in Visual Studio.NET. Right click on the ADOvNextTutorial.Model.IntegrationTest project and select Add and then New Item... In the file templates window, choose Application Configuration File and click Add. Place the following within the section of the App.config file

EDM Schema Concepts:

Before we edit the files, there are four concepts that exist in each of the three configuration files, that are helpful to be aware of.

Entity Set - An entity set is synonymous with a collection of entities, and the EDM schema uses this concept to generate the collection structures for the entities.

Entity Type - An entity type is a singular entity, which contains attributes and relationships.

Association Set - An association set is a list of entities that are involved in an association (relationship).

Association - An association is a relationship between entities.

Modify the Generated EDM Schemas and Mapping Specifications:

One thing that is not in ADO.NET vNext that would be nice, and something that is in Rails that I like, is the assumption that table names are plural and object names are singular. For instance, in Rails, if we had a table called People, and we created our models with the scripts provided by Rails, we would get an object called Person for our entity. ADO.NET vNext doesn't currently do this, and who knows if it will. But it'd be nice if it did. So basically what we are going to do here is modify the XML files so that we have plural naming where we need plural naming (collections), and singular naming when we need singular naming (the models).

First, we can open the ADOvNextTutorial.Model.cs.msl file and edit the EntityTypeMapping TypeName attributes to all be singular. For instance our Person entity looks like this by default:

But we want it to look like this:

Next, we will want to modify the person to firm relationship in this file to be something more friendly than the foreign key name. So we'll edit the AssociationSetMapping node's Name attribute to be "PersonsFirm" and TypeName attribute to be "ADOvNextModel.PersonsFirm".

Now we can move on to the ADOvNextTutorial.Model.Model.csdl file. For this file (as well as the target file), the XML that is generated has references to the namespace, however, should that namespace change, you would have to change it throughout the file. So, it appears as if they have provided an "Alias" attribute that will alias the namespace so you don't have to make direct reference to the namespace (not sure why the generated file doesn't use that in the first place.) So, everywhere we see a reference to the ADOvNextModel namespace (with the exception of the declaration), we will want to replace it with "Self". Additionally, we want to make similar changes to the ones we made in the previous XML file.

One other change we want to make to the Model's EDM schema is a NavigationProperty. Adding this node to the EntityType node in both Firm and Person will allow us from Firm to get/update a list of People related to it, and from Person allow us to update the Firm. So, in the Firm Entity Type add the following below the Name property node:

And in the Person Entity Type, add the following below the MiddleName property node:

Finally, we want to make similar changes in the ADOvNextTutorial.Model.Target.ssdl to the previous XML file.

Write Test:

At this point, you can attempt to build. You should get compilation errors if your XML is not correct, however, this is not super reliable at this point.

Once you build successfully, let's write a test that will add a firm to the database.

[Test]
public void AddFirm()
{
using (ADOvNext db = new ADOvNext(
ConfigurationManager.ConnectionStrings["ADOvNextConnectionString"]
.ConnectionString))
{
string firmName = "Testing";
Firm firm = new Firm();
firm.Name = firmName;
db.AddObject(firm);
db.SaveChanges();

Assert.AreNotEqual(0, firm.ID);
int id = firm.ID;

firm = (Firm)db.GetObjectByKey(firm.Key);
Assert.IsNotNull(firm);
Assert.AreEqual(id, firm.ID);
Assert.AreEqual(firmName, firm.Name);

firm.Delete();
db.SaveChanges();
}
}

Now, let's write a test that creates a person that is related to a firm:

[Test]
public void AddPerson()
{
using (ADOvNext db = new ADOvNext(
ConfigurationManager.ConnectionStrings["ADOvNextConnectionString"]
.ConnectionString))
{
string firmName = "Testing";
Firm firm = new Firm();
firm.Name = firmName;
db.AddObject(firm);
db.SaveChanges();

string firstName = "Jim";
string middleName = "Anthony";
string lastName = "Fiorato";
Person person = new Person();
person.FirstName = firstName;
person.MiddleName = middleName;
person.LastName = lastName;
person.PersonsFirm = firm;
db.AddObject(person);
db.SaveChanges();

Assert.AreNotEqual(0, person.ID);
int id = person.ID;

person = (Person) db.GetObjectByKey(person.Key);
Assert.AreEqual(firstName, person.FirstName);
Assert.AreEqual(middleName, person.MiddleName);
Assert.AreEqual(lastName, person.LastName);
Assert.AreEqual(firm.ID, person.PersonsFirm.ID);

person.Delete();
firm.Delete();
db.SaveChanges();
}
}

Ok, pretty easy, seeing as though the only code we've written is the test code. Obviously, my vanilla application is pretty straight forward, and we all have far more complicated ones out there. That said, it appears that this framework is more extensible, providing the ability to add a little more business logic into these models. Now, where some of the real power lies here is in the integration with LINQ. To show this, let's write an integration test that adds a bunch of people to the database, and then finds a specific person by name and firm:

[Test]
public void FindPersonByNameAndFirm()
{
using (ADOvNext db = new ADOvNext(
ConfigurationManager.ConnectionStrings["ADOvNextConnectionString"]
.ConnectionString))
{
Firm microsoft = new Firm();
microsoft.Name = "Microsoft";
db.AddObject(microsoft);
db.SaveChanges();

Firm google = new Firm();
google.Name = "Google";
db.AddObject(google);
db.SaveChanges();

Firm yahoo = new Firm();
yahoo.Name = "Yahoo";
db.AddObject(yahoo);
db.SaveChanges();

Person billg = new Person();
billg.FirstName = "Bill";
billg.LastName = "Gates";
billg.PersonsFirm = microsoft;
db.AddObject(billg);
db.SaveChanges();

Person steveb = new Person();
steveb.FirstName = "Steve";
steveb.LastName = "Ballmer";
steveb.PersonsFirm = microsoft;
db.AddObject(steveb);
db.SaveChanges();

Person davidf = new Person();
davidf.FirstName = "David";
davidf.LastName = "Filo";
davidf.PersonsFirm = yahoo;
db.AddObject(davidf);
db.SaveChanges();

Person larryp = new Person();
larryp.FirstName = "Larry";
larryp.LastName = "Page";
larryp.PersonsFirm = google;
db.AddObject(larryp);
db.SaveChanges();

Person sergeyb = new Person();
sergeyb.FirstName = "Sergey";
sergeyb.LastName = "Brin";
sergeyb.PersonsFirm = google;
db.AddObject(sergeyb);
db.SaveChanges();

try
{
var people = db.People.Where(p => p.LastName == "Gates")
.Where(p => p.PersonsFirm.ID == microsoft.ID);

int count = 0;
foreach (Person person in people)
{
count++;
}
Assert.AreEqual(1, count);

}
finally
{
billg.Delete();
steveb.Delete();
davidf.Delete();
larryp.Delete();
sergeyb.Delete();
microsoft.Delete();
google.Delete();
yahoo.Delete();
db.SaveChanges();
}
}
}

I used the lambda expression in the example above, but you can also use a SQL-like expression as well through the Query object like below:

Query people =
db.GetQuery(@"SELECT VALUE p
FROM People AS p
WHERE p.LastName = @lastName
AND p.PersonsFirm.ID = @firmId", new QueryParameter[] {
new QueryParameter("lastName", "Gates"),
new QueryParameter("firmId", microsoft.ID) });

Now, there are pros and cons to each. With the Lambda expressions, you will be getting more compile time support than with the Query syntax. However, as you can see below, the Lambda query generates significantly different SQL than does the QUERY. And the Lambda query has a few more joins than does the query syntax.

Below is the SQL executed when using the Lambda expression:

SELECT
N'0' AS [C1],
[Filter1].[ID1] AS [ID],
[Filter1].[LastName1] AS [LastName],
[Filter1].[FirstName1] AS [FirstName],
[Filter1].[MiddleName1] AS [MiddleName]
FROM (SELECT [Extent1].[ID] AS [ID1], [Extent1].[LastName] AS [LastName1]
, [Extent1].[FirstName] AS [FirstName1]
, [Extent1].[MiddleName] AS [MiddleName1]
, [Extent1].[FirmID] AS [FirmID1], [Extent2].[ID] AS [ID2]
, [Extent2].[LastName] AS [LastName2]
, [Extent2].[FirstName] AS [FirstName2]
, [Extent2].[MiddleName] AS [MiddleName2]
, [Extent2].[FirmID] AS [FirmID2]
FROM [dbo].[People] AS [Extent1]
INNER JOIN [dbo].[People] AS [Extent2] ON [Extent1].[ID] = [Extent2].[ID]
WHERE (([Extent2].[FirmID] = 47) AND ([Extent1].[LastName] = N'Gates'))
AND ([Extent1].[LastName] IS NOT NULL) ) AS [Filter1]
INNER JOIN (SELECT
[Extent3].[ID] AS [ID],
[Extent3].[FirmID] AS [FirmID]
FROM [dbo].[People] AS [Extent3]
WHERE [Extent3].[FirmID] IS NOT NULL ) AS [Project1]
ON [Filter1].[ID1] = [Project1].[ID]

And this is the query using the query syntax:

exec sp_executesql N'SELECT
N''0'' AS [C1],
[Extent1].[ID] AS [ID],
[Extent1].[LastName] AS [LastName],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[MiddleName] AS [MiddleName]
FROM [dbo].[People] AS [Extent1]
INNER JOIN [dbo].[People] AS [Extent2] ON [Extent1].[ID] = [Extent2].[ID]
WHERE ([Extent1].[LastName] = @lastName) AND ([Extent2].[FirmID] = @firmId)'
,N'@lastName nvarchar(5),@firmId int',@lastName=N'Gates',@firmId=53

I think I will end this blog at this point. There's a lot of info here, and hopefully it was helpful and not too verbose.

Oh, and by the way. I did this post with Windows Live Writer, and I have to say, it's a great tool. Very handy for taking many days to draw up a blog post.

Jim