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 4

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

The EntityClient API
The last of the APIs for Entity Framework, EntityClient, allows you to stream data back to the client without materializing objects. Instead, EntityClient returns results in a DbDataReader. While this may be the least common scenario for many developers using the Entity Framework, it is a very important one for developers who want to leverage the modeling capabilities of the EDM but don't need to use the features of Object Services. EntityClient uses Entity SQL as its query syntax.

Using the same Entity SQL projection query created above, this EntityClient query looks very similar to other ADO.NET provider queries:

   Using. eConn As _
      New EntityClient.EntityConnection( _
      Dim eCmd As _
         New EntityCommand(eSQL, eConn)
      Dim dr = eCmd.ExecuteReader _
      For Each dbrec As DbDataRecord In dr
         Console.WriteLine _
            ("Acct #: {0}, " & _
            "Unit Price Discount: {1}", _
            dbrec.Item("AccountNumber"), _
   End Using
In the preceding code, note that you can use either the index or the returned field name when iterating through the fields.

Both EntityClient and ObjectQuery accept query parameters, just as you use SqlParameter and other dbParameters when building "classic" ADO.NET queries.

A Shaped DataReader
This is not your father's DbDataReader, however. ADO.NET DataReaders contain scalar data. EntityClient can return nested DataReaders, which means that your queries can easily result in nicely shaped data. There's a great example in the Entity Framework docs that iterates through an Entity DataReader from a query that returns shaped data. The payload consists of scalar values, dbDataRecords, and nested dbDataReaders.

Unless you are completely new to .NET, you're probably already familiar with constructing queries in ADO.NET. Suffice it to say that you construct those queries using a string that is either the name of a stored procedure in the database or a dynamic query. You can create connections and commands and execute the queries manually or leverage some of the higher-level tools such as TableAdapters, and various DataSource objects in .NET to automate some of this work.

Change Tracking, Updates, and Concurrency
Because all these data access options work with disconnected data, they support optimistic concurrency, which means that database records do not get locked when they are accessed.

Who Owns the Change Tracking and State Info?
With respect to change tracking, there is a big difference between DataSets and their counterpart LINQ to SQL and Entity Framework objects. While all three provide change tracking, the DataSet is the only one that maintains its own state. Datasets include that state data even when persisted, serialized, or transferred across process boundaries.

LINQ to SQL and Entity objects, however, do not contain their state information. The context that manages the objects maintains the original values and state. This is a challenge for developers when serializing and/or moving objects across tiers. If you want to leverage the simple mechanisms for saving changes (SubmitChanges in LINQ to SQL and SaveChanges in Entity Framework), you need the state of the objects and properties (modified? deleted? added?) to persist the changes to the database. If your updates need to check for concurrent changes to the database (LINQ to SQL and Entity Framework both provide for this capability), you also need access to the original values.

There has been a lot of discussion and debate on the difficulty of handling this and the need for architectural guidance from Microsoft. Version 1 of LINQ to SQL and Entity Framework do not have any easy answers, though there are a number of people (including me) trying to help come up with patterns to use. Still others have just thrown up their hands and are waiting for the next version. Third-party tools such as Ideablade's DevForce for Entity Framework (in beta as of this writing) offer alternatives.

Rick Strahl's blog has some information on tracking changes across tiers in LINQ to SQL, and Beth Massey's blog discusses change tracking across tiers in Entity Framework.

EntityClient Is Read Only
EntityClient streams down read-only data. EntityClient is not able to perform updates.

Updates with Projections
In ADO.NET, if you are using the DataAdapter for updates, you can associate your own SQL command text to the UpdateCommand. That way, even if your strongly typed DataSets do not mirror tables in the database, you can handle updates fairly simply.

In LINQ to SQL and Entity Framework Object Services, updates using SubmitChanges and SaveChanges work only with complete entities. You would have to leverage stored procedures to update differently-shaped data. With Entity Framework, there are a few ways to tackle this including creating entities that specifically match the projection.

Bulk Updates
Whether you are performing saves manually or with the provided tools (ADO.NET's DataAdapter, LINQ to SQL SubmitChanges, and Entity Framework SaveChanges), each row's insert/update/delete command gets sent to the database individually.

ADO.NET 2.0 introduced two features to help with this. The dbDataAdapter.UpdateBatchSize property controls the number of commands to transmit to the database in batches, where they are then executed individually. The SqlBulkCopy class scan stream a SqlDataReader to the server. While this class is designed for inserts, you can get it to stream inserts, updates, and deletes to the database with a few tricks. In ADO.NET 1.0 and 1.1, it is possible to execute SQL Server BULK INSERT statements with the Execute command.

LINQ to SQL and Entity Framework do not support bulk inserts; however, LINQ to SQL's ExecuteCommand does provide for pass-through queries, so while you can't do a bulk insert, you can do other types of bulk operations such as this canonical example:

   context.ExecuteCommand _ 
      ("UPDATE Products SET UnitPrice=" & _
       " UnitPrice + 1.00")
These are important considerations when you need to upload lots of data at one time or make sweeping modifications to the database. It also introduces a scenario where you may want to mix these tools in a single application.

Alex James on the Entity Framework team has tried to attack these problems using Entity Framework in his blog.

Views and Stored Procedures
LINQ to SQL, Entity Data Model and ADO.NET all support views and stored procedures, but with varying levels of support. In LINQ to SQL and the Entity Data Model, database views are simply surfaced as read-only entities. ADO.NET's dbCommands make it easy to use either dynamic SQL or stored procedures with or without parameters.

By default, both LINQ to SQL and Entity Framework providers write their own dynamic queries for reading and writing data. For reads, you can use your own stored procedures (with caveats about simple updates if your stored procedures return anonymous types) and you can override the insert, update, and delete operations with pointers to your own stored procedures. This is easy when the stored procedures parameters and returned structures map exactly to their associated entities, but after you step off that narrow line, things definitely get limited and tricky.

READ Stored Procedures
Both LINQ to SQL and EF easily support stored procedures that perform READS when the return value matches up with an entity in the model. If the stored procedure returns something that is not an entity, the LINQ to SQL designer automatically creates a new class to match the schema of the results. This happens at design time. I would love to see the code that makes this happen! I'll have to continue looking for the right DLL so I can poke around in it with Reflector.

The EDM doesn't support this scenario as easily, because it requires creating an entity that matches the schema of the result set as well as a virtual table representation in the Store layer, and then mapping the entity to the table. Unfortunately, the designer doesn't do this automatically; it requires modifying the XML of the model manually. That's a bit of a pain, so if you have a lot of READ stored procedures to implement in your model, you may be unhappy. But perhaps the next version of the EDM designer will work that magic.

Insert, Update, and Delete Stored Procedures
Both LINQ to SQL and EF support DML stored procedures for inserts, updates, and deletes. If these procedures exist in the database, they can be built into the model. You can use insert, update, and delete procedures whose parameters match exactly to an existing entity to override LINQ to SQL and Entity Framework's default functions.

LINQ to SQL has a little more flexibility because you can map a procedure and then customize it. That way if the procedure's parameters don't match exactly, you do have the ability to modify it. In Entity Framework, you need to use EntityClient for DML stored procedures. Here's an example:

   Using eConn As New EntityConnection _
      Dim eCmd As New EntityCommand _
         ("NWEntities.UpdCompName", eConn)
      eCmd.CommandType = _
      eCmd.Parameters.AddWithValue _
         ("CustomerID", cust.CustomerID)
      eCmd.Parameters.AddWithValue _
         ("CompanyName", cust.CompanyName)
   End Using
Looks pretty familiar, right? This is one of the nice things about working with EntityClient—it uses the same classes as the other client providers.

On-the-fly Stored Procedures
As you saw above, LINQ to SQL's DataContext.ExecuteCommand makes pass-through queries simple to implement. With an Entity Data Model, it is possible to build T-SQL for read-only queries directly into the model in cases where even the most intelligent and creative mapping cannot help you implement the query that you desire. You do this using an SSDL element called DefiningQuery. The result is similar to a read-only view.

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