Data Access Options in Visual Studio 2008

With Visual Studio 2008 and .NET 3.5, developer’s data access options have increased substantially. In addition to using ADO.NET to create DataReaders or DataSets, Microsoft has added LINQ to SQL and Entity Framework as well as ADO.NET Data Services, which leverages those two. In addition to these new options, there are new syntaxes to learn. LINQ, which is built into Visual Basic and C#, has one implementation for LINQ to SQL and another for LINQ to Entities. In Entity Framework, you have the option to use LINQ to Entities as well as two other ways of querying with Entity SQL, as you can see in Figure 1.

In all, developers have gone from one “out-of-the-box” data access option plus a native query language of choice (e.g. T-SQL) to six different data access options along with some new query syntaxes to learn.

That sounds pretty scary. On the other hand, it offers the flexibility to choose the right tool for the job rather than have to constantly “make do” with the existing tools.

Because of the number of options to address, this article will focus on those that are native to .NET and Visual Studio. There are many third-party tools such as object relational mappers (ORM) and frameworks that you can use as well; but I won’t cover them in this article.

While it will be necessary to call out particulars of the different APIs, you should be able to benefit from this article without too much prior knowledge of, or experience with, LINQ to SQL or Entity Framework. However, this article is not aimed at teaching you how to use these technologies. I will focus on some of the most prominent differences between these options with the goal of giving you enough information to know which option makes sense for your scenario so that you can then drill into the appropriate technology and learn more.

I’ll base the examples in this article on unmodified versions of AdventureWorksLT and the Northwind database since they have familiar schemas for many readers.

Not All Databases Are Created Equal

The availability of database providers for a given data access option may impact your choice. There are myriad well-established data providers for ADO.NET-Microsoft providers such as SqlClient, OracleClient and OleDBClient as well as many written by third parties.

LINQ to SQL works only with SQL Server databases (2000+, Express, Compact 3.5). Matt Warren, one of the originators of LINQ to SQL, has a blog series on building IQueryable providers at http://blogs.msdn.com/mattwar.

Entity Framework can work with any database for which a provider exists. Microsoft has written the provider for SQL Server and included it with Entity Framework. As of late spring 2008, vendors have released into beta a number of providers that work with Entity Framework Beta 3, including Oracle, MySQL, PostgreSQL and a few IBM databases. David Sceppa at Microsoft is working with the provider writers and has a blog post listing who is currently working on the providers and when they should be available. See the sidebar, Upcoming Third-Party Providers for Entity Framework, for a link to his blog.

Entity Framework will not be able to support OleDB providers. The Entity Framework needs to transform its query expressions into an expression that will be recognized by a particular database. Since OleDB does not target a specific database, this isn’t possible.

What Does Your Data Access Code Interact With?

ADO.NET, LINQ to SQL, and Entity Framework all have different relationships to the data store.

ADO.NET-Work Against the Database

With ADO.NET you query the database directly, which means that you are required to have a very good understanding of the database schema. You must know the names of the stored procedures, views, and tables as well as the names and types of the parameters. Depending on how you handle the data upon return, you may also need to be well-versed in the schema of the return data. Strongly typed DataSets and TableAdapters make this significantly simpler because you can discover the schema through IntelliSense and they’ll take care of some of the connection functions for you.

LINQ to SQL-Work Against a Model that Is Essentially a Representation of the Database

LINQ to SQL queries are written against classes generated from the database. You can use a designer to do this work or code your classes manually.

Figure 2 shows a simple model built from three tables in the database. The existing one-to-many relationships were automatically identified by the designer.

Behind the visual model is a set of classes and properties with attributes that point to the database tables and columns represented in the model.

You can see in the declaration of the SalesOrderDetail class that it is bound directly to the database table through an attribute.

The properties are tightly bound as well.

Writing LINQ to SQL queries is made simple not only thanks to the strongly typed classes, but the relationships between the classes are strongly typed as well.

While you can customize the model somewhat, the model is still a one-to-one representation of the database. Therefore, in effect, LINQ to SQL classes represent the database and when writing queries, it’s as though you are constructing strongly typed queries against the database. IntelliSense not only helps you with the operators (SELECT, WHERE, FROM, etc.) but with the tables and related data as well. You’ll see more about querying further on.

While the database model is essentially a representation of the database schema, there are some customizations possible, both cosmetic and structural. I’ll show this in more detail later when I compare the designers and model customization.

By: Julia Lerman

Julie Lerman is a Microsoft MVP, .NET mentor and consultant who lives in the hills of Vermont. You can find Julie presenting on data access and other topics at user groups and conferences around the world. Julie blogs at thedatafarm.com/blog and is the author of the highly acclaimed Programming Entity Framework (O’Reilly Media). Follow Julie on twitter at julielermanvt.

jlerman@thedatafarm.com

ADO.NET Data Services

ADO.NET Data Services (aka Astoria) is a great way to expose Entity Framework and LINQ to SQL models over the web. Because it provides a wrapper around these technologies, it doesn’t fit into the particular goals of this article. One important point to be aware of, however, is that it solves a number of the problems that LINQ to SQL and Entity Framework developers are running into when using these models in Web and WCF services, most of which are around persisting change tracking across tiers. A great place to start with ADO.NET Data Services is the team’s blog at blogs.msdn.com/astoriateam.