RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Explore the Data Access Options in Visual Studio 2008 : Page 3

Visual Studio 2008 and .NET 3.5 give developers substantially increased data access options.

Querying against the target database or model is very different in ADO.NET, LINQ to SQL, and the Entity Framework.

LINQ to SQL creates a set of classes that represent the database; you write queries with the benefit of IntelliSense and strong typing against the database. And it's extremely easy to do.
LINQ wins hands down when it comes to writing queries, whether you are writing LINQ to SQL, LINQ to Entities or some other type of LINQ. LINQ's strongly typed operators and functions and a slew of useful extension methods make it easy to create queries against the strongly typed objects produced by LINQ to SQL and Entity Data Model.

LINQ to SQL Queries
With LINQ to SQL you must first instantiate a class that represents the model. This class is automatically created as part of the model and inherits from LINQ to SQL's DataContext class. Through the DataContext, you can access the other classes. You can write LINQ queries against the model using LINQ to SQL syntax. LINQ to SQL transforms your query into a T-SQL query, orchestrates the query execution, and plugs the resulting data into the model objects. The query isn't executed until your application executes code that enumerates the query results.

The following example shows a query that returns OrderDetail objects for large orders where the customer received a discount. Note that because LINQ to SQL is aware of the relationships between the tables, you don't need a JOIN; you can simply traverse into the related data. The strongly typed classes combine with IntelliSense to make writing queries very easy:

   Dim context = New AWModelDataContext
   Dim query = From detail _
      In context.OrderDetails _
      Where detail.Order.TotalDue > 1000 _
      And detail.UnitPriceDiscount > 0 _
      Select detail
   Dim detailList=query.ToList()
The preceding query returns a List of OrderDetail types—a class that exists in the model. It is also possible to project specific columns or functions in a query. As an example, you could replace the above Select clause with the following clause.

   Select New With {.ID = _
      detail.Order.SalesOrderID, _
      detail.Order.AccountNumber, _
      detail. Order.TotalDue, _
      detail.UnitPrice, _
      detail.UnitPriceDiscount, _
The query syntaxes in VB and C# are slightly different. For example, the above query with the projection would look like this in C#:

   from detail in context.OrderDetails 
   where detail.Order.TotalDue > 1000
      && detail.UnitPriceDiscount > 0
   select new {ID = 
Because the new type created by this projection does not exist in the model, LINQ returns an object that is an anonymous type. Anonymous types are another new language construct in VB and C# that let you create short-lived, strongly typed objects on the fly, without having to define classes in advance.

Figure 8. Many-to-Many Relationships: Many-to-many relationships can hide the link table in the model if the link table contains only the key fields for the join.
LINQ to Entities Queries
The same simplistic query expressed against an EDM using LINQ to Entities looks identical, though this won't always be the case. Instead of a DataContext, Entity Framework has a similar wrapper class called an ObjectContext. This wrapper is defined in the model and generated as a class. In the code below, the ObjectContext is named AWEntities:

   Dim context = New AWEntities
   Dim query = From detail _
      In context.OrderDetail _
      Where detail.Order.TotalDue > 1000 _
      And detail.UnitPriceDiscount > 0 _
      Select detail
   Dim detailList=query.ToList()
LINQ to Entities queries that do projection will also return an anonymous type.

Object Services
Underneath the covers, LINQ to Entities leverages one of Entity Framework's critical APIs, Object Services. It is Object Services that has the ability to send the query down the stack for execution, and then turn the results back into objects (aka "object materialization").

You can write queries using Object Services directly with the ObjectQuery class. The ObjectQuery class does not use the LINQ to SQL syntax. Instead, it takes query expressions in the form of Entity SQL, a T-SQL like syntax designed specifically for querying against the Entity Data Model.

Here is the same query again using an ObjectQuery. Because of the projection, again, an unknown entity is returned. Object Services cannot return anonymous types, so the objects returned are System.Common.dbDataRecords, which you can think of as being similar to a row in a DataReader. One major exception with this comparison, however, is that Entity Framework's results can contain shaped data. While returned entities are a breeze to work with, dbDataRecords require extracting the record items one by one:

   Using context = New AWEntities
      Dim eSQL = _
         "SELECT VALUE detail " & _
         "FROM AWEntities.OrderDetail " & _
         "AS detail " & _
         "WHERE detail.Order.TotalDue " & _
         ">1000 " & _
         "AND detail.UnitPriceDiscount > 0"
      Dim query = context.CreateQuery( _
         Of OrderDetail)(eSQL)
      Dim qlist = query.ToList
   End Using
The VALUE keyword indicates the query returns a single value or object. Alternatively, the projection used in the LINQ queries above would look like this in the Entity SQL:

   Dim eSQL = "SELECT " & _
      "detail.Order.SalesOrderID, " & _
      "detail.Order.AccountNumber," & _
      "detail.Order.TotalDue, " & _
      "detail.UnitPrice," & _
      "detail.UnitPriceDiscount, " & _
      "detail.LineTotal " & _
      "FROM AWEntities.Detail as detail "& _
      "WHERE detail.Order.TotalDue > 1000 " & _
      "AND detail.UnitPriceDiscount > 0 "
When using projections, rather than using CreateQuery of a specific type, the call would be:

   Dim query = context.CreateQuery _
      (Of dbDataRecord)(eSQL)
Entity SQL
Entity SQL has its roots in T-SQL, but has modifications made to enable working against an Entity Data Model. At first glance, compared to LINQ, Entity SQL seems as if it goes back to cave-dweller times; you're back to writing strings and hoping that they don't blow up at run time. But it can be useful when LINQ's strong typing might lock you into a corner. LINQ depends on the CLR for its functionality; if you want to express a query that LINQ's functions and providers won't allow you can get stuck. With LINQ to SQL, you can get around this by writing pass-through queries with ExecuteCommand. But in Entity Framework, you can step down to a lower level and use Entity SQL with Object Services. Because Entity SQL is string-based, you can build query strings dynamically in code, and you can use provider-specific query operators and functions in the string.

Here's an example of an Entity SQL query that picks which properties to use in the projection. Given an array of desired properties, you can build the query string dynamically, for example:

   Dim arrayOfFields = New String() _
      {"FirstName", "LastName"}
   Dim strFlds As New Text.StringBuilder
   For Each fld In arrayoffields
      strFlds.Append("c." & fld & ",")
   'trim off last comma
   strFlds.Remove(strFlds.Length - 1, 1)
   Dim queryString = _
      "SELECT " & strFlds.ToString & _
      " FROM AWEntities.Customers AS c" & _
      " WHERE LEFT(c.lastname,1)='S'"
You can't do this in either LINQ to Entities or LINQ to SQL. I have heard that some people have been toying with functional programming to pull off something like this in LINQ, but it's certainly not "out of the box easy."

You can find more on these differences here and in this post that discusses how to parse an EntityDataReader.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date