An EntityConnection is not a database connection. This can be a big point of confusion. Although you can open and close an EntityConnection, this does not mean you are opening and closing a connection to the database. Whether you use EntityClient directly or you let Object Services execute your commands and queries for you, the EntityConnection is just a path to the database connection.

An EntityConnection consists of four parts:

You can define the EntityConnection declaratively in the .config file. Example 16-1 lists the name of the connection string and then the EntityConnection string itself. Within the connection string, you can see the metadata parameter, the provider connection parameter, and the provider name. When the EDM Wizard builds this string for you, it replaces the quotes around the provider connection string with an escaped quote ("), which is the XML encoding for a quote. For readability, you can replace the escaped quotes with single quotes, as in Example 16-1.

<connectionStrings>
<add
 name="BreakAwayEntities"
 connectionString=
   "metadata=res://*/BAModel.csdl|res://*/BAModel.ssdl|
            res://*/BAModel.msl;
    provider=System.Data.SqlClient;
    provider connection string='Data Source=myserver;
                               Initial Catalog=BreakAway;
                               Integrated Security=True;
                               MultipleActiveResultSets=True'" 
    providerName="System.Data.EntityClient"
 />
</connectionStrings>

By default, an ObjectContext will use the connection string from the .config file that matches the EntityContainer name within your model. You have taken advantage of this in almost every code sample so far in the book, which is why you have not yet had to work explicitly with connection strings.

The database connection string that is embedded into the EntityConnection string is passed along to the database provider that eventually makes the actual connection to the database.

EntityConnection is a class within the EntityClient namespace. Earlier in the book, you worked directly with this class when using EntityClient for your queries. In the following code, the name of the connection string in the .config file is passed as a parameter (along with the parameter key name=) in the EntityConnection constructor. The connection in this case will be created from the details provided in the connection string:

VB
Using conn As EntityConnection = New EntityConnection("name=BAEntities")

C#
using (EntityConnecton conn = new EntityConnection("name=BAEntities");

You can use the preceding method to explicitly select a particular connection string from the .config file when instantiating an ObjectContext, as shown in the code that follows.

VB
Dim context= New BAEntities("name=MyOtherConnectionString")

C#
var context = new BAEntities("name=MyOtherConnectionString");

When you use this constructor for an EntityConnection or ObjectContext, the ConnectionString is not read in its entirety right away. In fact, if you inspect the EntityConnection in the debugger after it has been instantiated, you’ll see that although the database connection object has been pulled into the StoreConnection property, the other parameters of the EntityConnectionString are nowhere to be found, as shown in Figure 16-1.

Figure 16-1. The EntityConnection object with no properties for the metadata or provider namespace attributes

The metadata and provider namespace parameters are not displayed as properties of the EntityConnection class, and they will be accessed at the point in the query pipeline where EntityClient needs to read the Entity Data Model (EDM), and then again to determine which provider (e.g., Data.Sql.SqlClient) to which to pass the request for further processing.

If you do want to read the full connection string from the configuration file, you can use one of the .NET methods for reading data from a configuration file, such as System.Configuration.ConfigurationManager.

You can programmatically construct an EntityConnectionString with the EntityConnectionStringBuilder, which inherits from DbConnectionStringBuilder. For example, you may store the location of your metadata files (.csdl, .msl, .ssdl) in a resource file and wish to programmatically change the EntityConnectionString to point to this location. Or you may want to programmatically change the ADO.NET DataProvider (e.g., System.Data.SqlClient) on the fly.

The code in Example 16-2 reads the connection string from the configuration file into a string, creates an EntityConnectionStringBuilder from that string, modifies the Metadata property, and then instantiates an ObjectContext with the newly configured EntityConnectionString.

C:EFModelsModel.csdl|C:EFModelsModel.ssdl|C:EFModelsModel.msl

VB
Dim connstring = ConfigurationManager.ConnectionStrings.Item _
                 ("BAEntities").ConnectionString
Dim estringnew = New EntityConnectionStringBuilder(connstring)
With estringnew
  .Metadata = My.Settings.MetadataFilePath
  Dim context = New BAEntities(estringnew.ToString)
  Dim query = From con In context.Contacts _
              Where con.Addresses.Any(Function(a) a.City = "Seattle")
End With

C#
var connstring = ConfigurationManager
                 .ConnectionStrings["BAEntities"].ConnectionString;
var estringnew = new EntityConnectionStringBuilder(connstring);
estringnew.Metadata = Properties.Settings.Default.MetadataFilePath; 
var context = new BAEntities(estringnew.ToString());
var query =
    from con in context.Contacts
    where con.Addresses.Any((a) => a.City == "Seattle")
    select con;

Unfortunately, the Metadata parameter is a string, so there’s no strongly typed way to construct it. However, you can use one of the common DbConnectionStringBuilder classes, such as SqlConnectionStringBuilder, to programmatically construct the provider connection string (StoreConnection) of the EntityConnectionString.

EntityConnection.Open loads the metadata files (to read the model) if they have not yet been loaded into application memory. This method calls the database provider’s Connection.Open as well. EntityConnection.Close will, in turn, call the database connection’s close method.

When an ObjectContext executes a query internally it creates an EntityConnection, and an EntityCommand then executes the command. As soon as the data has been consumed, whether you call a method such as ToList to read all of the data at once or you iterate through the data and come to the end, the context will close the EntityConnection, which in turn closes the database connection.

Opening and closing connections to the database is something that many developers fret about because we want to make the most efficient use of available resources. You may want to control when the open and close happen.

VB
Using context As New BAEntities
  Dim cons = From con In context.Contacts Where con.FirstName = "Jose"
  For Each c In cons
    If c.AddDate < New Date(2007, 1, 1) Then
      c.Addresses.Load()
    End If
  Next
End Using

C#
using (BAEntities context = new BAEntities())
{
  var cons =
      from con in context.Contacts
      where con.FirstName == "Jose"
      select con;
  foreach (var c in cons)
  {
    if (c.AddDate < new System.DateTime(2007, 1, 1))
    {
      c.Addresses.Load();
    }
  }
}

VB
Using context As New BAEntities
  Dim cons = From con In context.Contacts Where con.FirstName = "Jose"
  Dim conList = cons.ToList
  Dim allCustomers = From con In context.Contacts.OfType(Of Customer)(
  Dim allcustList = allCustomers.ToList
End Using

C#
using (BAEntities context = new BAEntities())
{
  var cons = from con in context.Contacts where con.FirstName == "Jose"
             select con;
  var conList = cons.ToList();
  var allCustomers =  from con in context.Contacts.OfType<Customer>()
                      select con;
  var allcustList = allCustomers.ToList();
}

VB
Using context As New BAEntities
  context.Connection.Open()
  Dim cons = From con In context.Contacts Where con.FirstName = "Jose"
  Dim conList = cons.ToList
  Dim allCustomers = From con In context.Contacts.OfType(Of Customer)
  Dim allcustList = allCustomers.ToList
  context.Connection.Close()
End Using

C#
using (BAEntities context = new BAEntities())
{
  context.Connection.Open();
  var cons = from con in context.Contacts where con.FirstName == "Jose"
             select con;
  var conList = cons.ToList();
  var allCustomers =  from con in context.Contacts.OfType<Customer>()
                      select con;
  var allcustList = allCustomers.ToList();
  context.Connection.Close();
}

As with any data access performed in .NET, it’s important that you dispose the database connection, because it is not a managed resource and the garbage collector will not clean it up. Lingering database connections are a common cause of server resource issues. Again, when you rely on the Entity Framework’s default behavior, the database connection will be properly disposed. Disposing the ObjectContext will automatically close the EntityConnection and will close and dispose the database connection. You can either explicitly dispose the ObjectContext or wait for the garbage collector to do the job. However, in the latter scenario, that means the database connection is still hanging around until that time.

In common usage scenarios with the Entity Framework, the worst offense (holding a connection open) should not be an issue, because as you have seen, the connection will be closed automatically. But if one of the triggers for closing a connection has not been executed—completing the consumption of query results, calling EntityConnection.Close, or disposing the ObjectContext—you could unwittingly be consuming extra resources.

ObjectContext’s Dispose method calls EntityConnection.Dispose if ObjectContext created the connection. In turn, EntityConnection.Dispose will call the Dispose method on the StoreConnection. The code behind ObjectContext.Dispose is shown in Example 16-6 so that you can see just how it works.

VB
Protected Overridable Sub Dispose(ByVal disposing As System.Boolean)
  If disposing Then
    If (Me._createdConnection AndAlso _
                              (Not Me._connection Is Nothing)) Then
        Me._connection.Dispose
    End If
    Me._connection = Nothing
    Me._adapter = Nothing
  End If
End Sub

C#
protected virtual void Dispose(bool disposing)
{
  if (disposing)
  {
    if (this._createdConnection && (this._connection != null))
    {
      this._connection.Dispose();
    }
    this._connection = null;
    this._adapter = null;
  }
}

So, if you are using the defaults with LINQ to Entities or ObjectContext, the connection will be disposed. If you want to be sure the connection is disposed right away, you need to either explicitly make that call or be sure the ObjectContext is explicitly disposed. If you have created the EntityConnection explicitly, you have to either dispose it explicitly or wait for the garbage collector to dispose it; again, this in turn will dispose the database connection.

Spinning up a database connection is expensive in terms of resources. When a connection is closed, it can be left in memory to be reused the next time a connection is required, eliminating the cost of creating a new connection. This is called connection pooling.

Developers often ask whether the Entity Framework does connection pooling. Because connection pooling is controlled by the database provider, the Entity Framework does not explicitly impact or interact with how connection pooling works. Instead, it relies on the provider’s connection pooling. For more information on connection pooling in ADO.NET, a good starting point is the “SQL Server Connection Pooling (ADO.NET)” topic in the MSDN documentation.

Although the default use of DbTransaction takes care of operations on a single instance of a database connection, the TransactionScope class System.Transaction can coordinate operations across a variety of processes that use resource managers. Therefore, within a single transaction you could make calls to a database, to the Message Queue (MSMQ), or even to another database using ADO.NET. If one of those fails, System.Transaction will allow all of them to be rolled back together. System.Transaction leverages the Windows Distributed Transaction Coordinator (DTC) to make this happen, albeit with more overhead than a simple DbTransaction. But what is great about System.Transaction is that it will decide whether your actions need only the individual transaction (such as SQLTransaction), or whether they need to escalate to a DTC so that multiple transactions can be orchestrated. In that way, you don’t needlessly waste resources with the DTC, but you also don’t have to explicitly control it.

The database constraint between Contact and Address in the BreakAway database makes a good test case for demonstrating the implicit transactions in the Entity Framework. An address cannot exist without a contact, yet no cascading delete is defined in the database to delete related addresses when a contact is deleted. Therefore, an attempt to delete a Contact entity without deleting its related addresses in code will cause the database to throw an error when SaveChanges is called. Let’s take advantage of that and write some code to see the transaction in action.

The code in Example 16-7 queries for a particular contact, deletes it from the ObjectContext, and then calls SaveChanges. To add a twist, the code also creates a new payment for a reservation. Remember that when you attach the payment to the reservation in the context, SaveChanges automatically pulls the payment into the context and inserts it into the database.

VB
Using context As New BAEntities
  Dim con = context.Contacts.Where _
             (Function(c) c.ContactID = 5).FirstOrDefault
  context.DeleteObject(con)
  Dim res = context.Reservations.FirstOrDefault
  Dim newPayment = New Payment
  With newPayment
    .Amount = "500"
    .PaymentDate = Now
    .Reservation = res
  End With
  context.SaveChanges()
End Using

C#
using (BAEntities context = new BAEntities())
{
  var con = context.Contacts.Where(c => c.ContactID == 5)
                   .FirstOrDefault();
  context.DeleteObject(con);
  var res = context.Reservations.FirstOrDefault;
  var newPayment = new Payment();
  newPayment.Amount = "500";
  newPayment.PaymentDate = System.DateTime.Now;
  newPayment.Reservation = res;
  context.SaveChanges();
}

The attempt to delete the contact from the database will fail because of the referential constraint. Figure 16-2, a screenshot from SQL Profiler, shows what happens when SaveChanges is called.

Figure 16-2. The Entity Framework automatically forcing a rollback if any of the commands to the database fail

A transaction was created, and because the delete failed, the transaction is rolled back and the insert for the payment is not even bothered with.

On the client side, an exception is thrown containing the error from the database, which offers a very clear description of the problem:

"The DELETE statement conflicted with the REFERENCE constraint 
"FK_Address_Contact". The conflict occurred in database "BreakAway", table
"dbo.Address", column 'ContactID'. The statement has been terminated."

This highlights a good reason to be sure to include exception handling around SaveChanges in cases where any constraints in the database are not constrained in advance in the model or in the application.

In this example, SaveChanges caused two commands to be executed. Even if SaveChanges created only one command, it would still be wrapped in a database transaction.

ObjectContext.SaveChanges(false)

Just as you can override the default behavior with connections, you can also control transaction functionality. If you explicitly create your own transaction, SaveChanges will not create a DbTransaction. You won’t create a System.Common.DbTransaction, though. Instead, when creating your own transactions, you need to use a System.Transaction.TransactionScope object.

You can use a transaction for read and write activities in the database, which means that this will work with both ObjectContext and EntityClient.

Example 16-8 uses an explicit transaction to save a new customer to a database and, if the call to SaveChanges is successful, to add the customer’s name to a completely separate database. The application has references to two different projects with EDMs. If something goes wrong with either database update, the TransactionScope will not be completed and both updates will be rolled back.

VB
Using context As New BreakAwayEntities
  Dim cust As Customer = Customer.CreateCustomer _
   ("George", "Jetson", "A real space cadet",New DateTime(1962,1,1))
  context.AddToContacts(cust)
  Using tran As New TransactionScope
    Try
      context.SaveChanges(False)
      Using altcontext As New altDBEntities
        altcontext.AddToContact(Contact _
         .CreateContact(cust.LastName.Trim & ", " & cust.FirstName))
        altcontext.SaveChanges()
      End Using
      tran.Complete()
      context.AcceptAllChanges()
    Catch ex As Exception
       'throw or handle database or Entity Framework exceptions
        Throw
    End Try
  End Using
End Using

C#
using (var context = new BAEntities())
{
  Customer cust = Customer.CreateCustomer
   ("George", "Jetson", "A real space cadet",new DateTime(1962,1,1));
  context.AddToContacts(cust);
  using (TransactionScope tran = new TransactionScope())
  {
    try
    {
      context.SaveChanges(false);
      using (altDBEntities altcontext = new altDBEntities())
      {
        altcontext.AddToContact(Contact
        .CreateContact(cust.LastName.Trim() + ", " + cust.FirstName));
        altcontext.SaveChanges();
      }
      tran.Complete();
      context.AcceptAllChanges();
    }
    catch
    {
      //throw or handle database of Entity Framework exceptions
      throw;
    }
  }
}

You can watch the transaction being promoted in a few ways. For example, in SQL Profiler, you can see that System.Transaction starts out by using a simple database transaction, but as soon as it hits the call to SaveChanges to a different database, the transaction is promoted (see Figure 16-3).

Figure 16-3. SQL Profiler showing that a database transaction is used at first, but is then promoted when another database connection is made within the scope of a System.Transactions.TransactionScope

You can also add a variety of performance counters into the Windows Performance Monitor that tracks the DTC and you can see whether a transaction was created, completed, or even rolled back.

The last way you can prove this is working is to force one of the updates to fail. You can see the rollback in the Profiler, or even just look in the database to verify that the changes have not been made.

It is also possible to use a transaction on a read-only query using System.Transaction or EntityClient.EntityTransaction. An EntityTransaction is merely a wrapper for the database provider’s transaction, and call EntityConnection.BeginTransaction to create it, as shown in Example 16-9.

VB
Using econ = New EntityConnection("name=BAEntities")
  Dim eTran As EntityTransaction = _
   econ.BeginTransaction(IsolationLevel.ReadUncommitted)
   econ.Open()
   Dim eCmd = econ.CreateCommand
   eCmd.CommandText = _
    "SELECT  con.contactID FROM BreakAwayEntities.Contacts AS con"
   Dim dr = eCmd.ExecuteReader(CommandBehavior.SequentialAccess)
   While dr.Read
      'do something with the data
   End While
   eTran.Commit()
End Using

C#
using (var econ = new EntityConnection("name=BAEntities"))
{
  EntityTransaction eTran = 
   econ.BeginTransaction(IsolationLevel.ReadUncommitted);
  econ.Open();
  var eCmd = econ.CreateCommand();
  eCmd.CommandText = 
   "SELECT  con.contactID FROM BreakAwayEntities.Contacts AS con";
  var dr = eCmd.ExecuteReader(CommandBehavior.SequentialAccess);
  while (dr.Read())
  {
     //do something with the data;
  }
  eTran.Commit();
}

At first glance, it may not make sense to have a transaction on a read, since you can’t roll back a read. The purpose of performing a read within a transaction is to control how to read data in the database that may be involved in another transaction at the same time. Notice the IsolationLevel.ReadUncommitted parameter being passed in. IsolationLevel lets you determine how your query should read data that some other person or process is currently updating. The ReadUncommitted enum says that it is OK for this query to read data that is being modified, even if it has not yet been committed in the other transaction. The other possibilities are Serializable, RepeatableRead, ReadCommitted, Snapshot, Chaos, and Unspecified. You can check the docs to learn more about these IsolationLevels, which are not specific to the Entity Framework.

Although you can use EntityTransaction directly, it is recommended that you use System.Transaction.TransactionScope where you can also determine IsolationLevel. In that case, you would wrap the query (EntityClient, LINQ to Entities, or ObjectQuery) within a TransactionScope, just as in the previous example, which used TransactionScope for SaveChanges.

People often ask about the ability to roll back changes to entities in the context. Unfortunately, Object Services does not have a mechanism to achieve this. If you want to roll all the way back to the server values, you can use ObjectContext.Refresh to reset specific entities or a collection of entities, but you cannot do a thorough refresh of everything in the context. You’ll learn more about refresh in Chapter 17. Alternatively, you can dispose the context, create a new one, and requery the data. But still, this is not the same as rolling back to a previous state of the entities; all you’re doing is getting fresh data from the store.

If you want to persist the state of your entities at any given point in time and then restore them into the context, you’ll need a better understanding of the ObjectStateManager, which we will cover in detail in Chapter 17.

Unfortunately, at this time no pattern is available for pulling this off, but eventually someone from Microsoft or the development community will create and share a pattern to solve this. Hopefully, we’ll see this feature added to version 2 of the Entity Framework.

SQL injection attacks are one of the most worrisome problems for data developers. An injection occurs when an end user is able to append actual query syntax in data entry form fields that can damage your data (e.g., delete table x) or access information by piggybacking on the executed command.

SQL injection can occur when you build queries dynamically in your code. For example:

QueryString="select * from users where username='" & TextBox.Text & "'"

Therefore, it is always recommended that programmers avoid building dynamic queries. Instead, we use parameterized queries or leverage stored procedures from our data access code.

Because we have been trained to have an inherent fear of dynamic queries, on the surface the fact that the Entity Framework (and LINQ to SQL, for that matter) builds queries for us raises a big red flag.

From loc In context.Locations Where loc.LocationName = textBox.Text

SELECT 
[Extent1].[LocationID] AS [LocationID], 
[Extent1].[LocationName] AS [LocationName]
FROM [dbo].[Locations] AS [Extent1]
WHERE [Extent1].[LocationName] = @p__linq__1

@p__linq__1='Norway'

SELECT 
[Extent1].[LocationID] AS [LocationID], 
[Extent1].[LocationName] AS [LocationName]
FROM [dbo].[Locations] AS [Extent1]
WHERE [Extent1].[LocationName] = @p__linq__1

@p__linq__1='a'' OR ''t''=''t'

SELECT VALUE loc FROM BreakAwayEntities.Locations AS loc
WHERE loc.LocationName='" & city & "'"

SELECT
[Extent1].[LocationID] AS [LocationID],
[Extent1].[LocationName] AS [LocationName]
FROM [dbo].[Locations] AS [Extent1]
WHERE [Extent1].[LocationName] = 'Norway'

SELECT
[Extent1].[LocationID] AS [LocationID],
[Extent1].[LocationName] AS [LocationName]
FROM [dbo].[Locations] AS [Extent1]
WHERE ([Extent1].[LocationName] = 'a') OR ('t' = 't')

"a' ; SELECT * FROM LOGINS"

"a' UNION ALL (SELECT value log from entities.logins as log)"

Although your model might limit what parts of your database a user has access to, it does make a connection to the database, providing an open door to users who might not otherwise have access to the database.

As you saw in EntityConnection and Database Connections in the Entity Framework, it is possible to get at the database connection through an EntityConnection; therefore, a developer writing queries against the model could easily execute his own commands by using the existing connection. This would enable him to access data that is not even part of the model.

Consider the code in Example 16-10 where the developer uses the connection from the context to return the employee data from the database.

VB
Using context As New MyEntities
  Dim query = From con In context.Contacts Take 10
  Dim conn As EntityConnection = context.Connection
  Dim dbconn As SqlConnection = conn.StoreConnection
  conn.Open()
  Dim sqlcmd = New SqlCommand("Select * from HR.Employees", dbconn)
  Dim dr As SqlClient.SqlDataReader = sqlcmd.ExecuteReader
  While dr.Read
    Console.WriteLine(dr.Item("SocialSecurityNumber"))
  End While
End Using

C#
using (MyEntities context = new MyEntities())
{
  var query = (from con in context.Contacts
               select con).Take(10);
  EntityConnection conn = context.Connection;
  SqlConnection dbconn = conn.StoreConnection;
  conn.Open();
  var sqlcmd = new SqlCommand("Select * from HR.Employees", dbconn);
  SqlClient.SqlDataReader dr = sqlcmd.ExecuteReader();
  while (dr.Read())
  {
    Console.WriteLine(dr["SocialSecurityNumber"]);
  }
}

Even worse, with the connection string, any type of command against the database can be executed, not just queries.

Although the developer may not necessarily have access to the connection string being used for the EDM queries—for example, the connection string may be encrypted—he can use this connection and any of the permissions associated with the login.

This type of abuse is not particular to the Entity Framework, but it’s important to be aware that the Entity Framework doesn’t prevent it. As with any data access scenario, applying permissions carefully in your database can help you avoid this situation.

Following are some tests to give you a feel for the difference in performance (speed) between the Entity Framework, classic ADO.NET, and LINQ to SQL, because that’s an important comparison as well.

Each test presents the average time it takes to process and return a query of the AdventureWorksLT Customer table 100 times. The tests are designed so that the processes will be comparable. For example, with the DataReader test, the code performs 100 individual queries, opening and closing a connection for each query. In the LINQ to Entities and ObjectContext tests, the sample instantiates a new context and performs 100 queries on that context. With the two Entity Framework queries, the default connection is being used; therefore, the Entity Framework will open a new connection for each query and then close the connection when the results have been iterated through. This is why the DataReader tests open and close the connection each time as well. The fourth test performs the same query using LINQ to SQL, which also opens and closes a connection for each query.

In each test, the loop of 100 queries runs twice. The first time is to “prime the pump” so that any performance advantages provided by repeated queries are evened out between the various tests. The second set of 100 tests is used to gather the timings. In each test, the results are iterated through completely. The time quoted is not the time it took to perform a single query. It is the time it took to perform 100 queries, opening and closing the connection 100 times. It was a nice coincidence that the DataReader test resulted in an even 100 ms, making it easier to compare the other results.

Table 16-1 compares the relative times for the different methods of querying. In the following section, I interpret the results as well as list the code used to generate the results (see Examples 16-11 through 16-15).

C#
private static void DataReaderTest(string connstring)
{
  List<decimal> testresults = new List<decimal>();
  string cmdText = "select CustomerID, NameStyle, Title, FirstName," +
                   "MiddleName, LastName,Suffix,CompanyName, " +
                   "SalesPerson, EmailAddress,Phone,PasswordHash, " +
                   "PasswordSalt, rowguid, ModifiedDate " +
                   "FROM SalesLT.Customer";
  // start the timer
  System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
  for (int i = 0; i < 2; i++)
  {
    testresults.Clear();
    SqlConnection sqlCon = new SqlConnection(connstring);
    for (int j = 0; j < 100; j++)
    {
      sw.Reset();
      sw.Start(); //timing the whole loop of 100 queries
      sqlCon.Open();
      SqlCommand cmd = new SqlCommand(cmdText, sqlCon);
      SqlDataReader reader = cmd.ExecuteReader();
      while (reader.Read())
      {
        object val = reader.GetValue(2);
      }
      reader.Close();
      sqlCon.Close();
    }
    sw.Stop();
    testresults.Add((decimal)sw.ElapsedMilliseconds);
  }
  // return second set of results
  Console.WriteLine("DataReader: {0}ms", testsresults[1])
}

C#
private static void LINQtoEntitiesTest()
{
  List<decimal> testresults = new List<decimal>();
  System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
  for (int i = 0; i < 2; i++)
  {
    testresults.Clear();
    AWLTEntities edmAW = new AWLTEntities();
    for (int j = 0; j < 100; j++)
    {
      sw.Reset();
      sw.Start();
      var customers = from c in edmAW.EFCustomers select c;
      foreach (EFCustomer cust in customers)
      {
        object o = cust;
      }
    }
    sw.Stop();
    testresults.Add((decimal)sw.ElapsedMilliseconds);
  } //end for loop
  //toss first result, calc average of rest
  Console.WriteLine("DataReader: {0}ms", testsresults[1])
}

C#
private static void ESQLQueryTest()
{
  List<decimal> testresults = new List<decimal>();
  System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
  for (int i = 0; i < 2; i++)
  {
    testresults.Clear();
    AWLTEntities AWL2E = new AWLTEntities();
    for (int j = 0; j < 100; j++)
    sw.Reset();
    sw.Start();
    {
      string esql = "SELECT VALUE c from AWLTEntities.EFCustomers AS c";
      ObjectQuery<EFCustomer> customers =
                       AWL2E.CreateQuery<EFCustomer>(esql);
      foreach (EFCustomer cust in customers)
      {
        object c = cust;
      }
    }
    sw.Stop();
    testresults.Add((decimal)sw.ElapsedMilliseconds);
  }
  Console.WriteLine("DataReader: {0}ms", testsresults[1])
}

C#
private static decimal EntityClientTest()

{
  List<decimal> testresults = new List<decimal>();
  System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
  for (int i = 0; i < 2; i++)
  {
    testresults.Clear();
    AWLTEntities AWL2E = new AWLTEntities();
    EntityConnection eConn = new EntityConnection("name=AWLTEntities");
    for (int j = 0; j < 100; j++)
    {
      sw.Reset();
      sw.Start();
      string esql = "SELECT VALUE c from AWLTEntities.EFCustomers AS c";
      EntityCommand eCmd = new EntityCommand(esql, eConn);
      eConn.Open();
      EntityDataReader eReader=
                 eCmd.ExecuteReader(CommandBehavior.SequentialAccess);
      while (eReader.Read())
      {
        object val = eReader.GetValue(2);
      }
      eReader.Close();
      eConn.Close();
    }
    sw.Stop();
    testresults.Add((decimal)sw.ElapsedMilliseconds);
  }
  Console.WriteLine("DataReader: {0}ms", testsresults[1])
}

C#
private static decimal LINQtoSQLTest()
{
  List<decimal> testresults = new List<decimal>();
  System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();

  for (int i = 0; i < iOuterLoop; i++)
  {
    testresults.Clear();
    AWLTDataContext AWL2SContext = new AWLTDataContext();
    for (int j = 0; j < iInnerloop; j++)
    {
      sw.Reset();
      sw.Start();
      var query = from c in AWL2SContext.L2SCustomers select c;
      foreach (L2SCustomer cust in query)
      {
        object c = cust;
      }
    }
    sw.Stop();
    testresults.Add((decimal)sw.ElapsedMilliseconds);
  } //end for loop
  Console.WriteLine("DataReader: {0}ms", testsresults[1])
}

In an early 2008 blog post titled “Exploring the Performance of the ADO.NET Entity Framework—Part 1” (http://blogs.msdn.com/adonet/archive/2008/02/04/exploring-the-performance-of-the-ado-net-entity-framework-part-1.aspx/), Brian Dawson of the Entity Framework team breaks down query time by task. In his tests, 56 percent of the total time for processing a query is devoted to “view generation.” View generation refers to the process of creating the native command from an Entity SQL ObjectQuery or a call to SaveChanges. Fifty-six percent!

Here’s a quick refresher on what’s going on during this process. The Entity SQL is broken down into a command tree comprising Entity SQL operators and functions with entity names, properties, and relationships. This command tree is sent to the data provider, which translates the Entity SQL operators and functions to native operators and functions and uses the EDM to translate the entities and properties to tables and columns. Because the original query might be too complex for the native query, a series of simplifications is also performed on the tree. Finally, this newly created command tree is sent to the database.

This is a lot of work. But it doesn’t necessarily need to happen on the fly at runtime. Given the queries and the EDM, the native queries can be precompiled. You can take advantage of query precompilation in two ways: precompiled views and precompiled LINQ to Entities queries.

The EDM Generator, a command-line tool (EDMGen.exe), allows you to perform many of the same tasks that the EDM Wizard performs, as well as some others.

The EDM Generator has five command-line switches, which you can use to do the following:

C:Program FilesMicrosoft Visual Studio 9.0VC> 
  edmgen /mode:FullGeneration
   /c:"Data Source=127.0.0.1;
       Initial Catalog=AdventureWorksLT;
       Integrated Security=True"
   /p:AWEDMGenTest

C:Program FilesMicrosoft Visual Studio 9.0VC>
edmgen /mode:ViewGeneration 
 /inssdl:c:efmodelsBreakAwayModel.ssdl 
 /incsdl:c:efModelsBreakAwayModel.csdl 
 /inmsl:c:efmodelsBreakAwayModel.msl 
 /p:"D:VS2008projects\_EFBOOK SamplesBreakAwayWCFService
     BreakAwayWCFService.vbproj"
/language:VB

Although the view generation feature lets you create the native SQL for all of the model’s EntitySets and associations, there’s also a way to precompile the actual queries that you create in your application. This happens at runtime. For LINQ to Entities queries, you can explicitly precompile your queries using the CompiledQuery.Compile method. Entity SQL queries, whether called through EntityClient or through ObjectQuery, can be implicitly compiled and stored in a cache based on a setting that enables or disables query plan caching.

Compile(args, ReturnType) (Delegate Query)

VB
Dim custsToDestination = _
From cust In context.Contacts.OfType(Of Customer)() _
Where cust.Reservations.FirstOrDefault.Trip.Destination.DetinationName _
= "Patagonia"

C#
var newCustomersSinceDate =
from cust in context.Contacts.OfType<Customer>()
where cust.Reservations.FirstOrDefault().Trip.Destination.DetinationName
== "Patagonia"
select cust;

VB
Function(ctx As BreakAwayEntities, DestinationString As String) _
  From cust In ctx.Contacts.OfType(Of Customer)() _
  Where cust.Reservations.FirstOrDefault.Trip.Destination.DestinationName _
  = DestinationString

C#
(BreakAwayEntities ctx,String DestinationString) =>
  from cust in ctx.Contacts.OfType<Customer>()
  where cust.Reservations.FirstOrDefault().Trip.Destination.DestinationName 
  == DestinationString select cust

VB
Dim compQuery = CompiledQuery.Compile(Of BreakAwayEntities, String, _
 IQueryable(Of Customer))(*insert lambda expression from Example 16-18*)

C#
var compQuery = CompiledQuery.Compile<BreakAwayEntities, String,
 IQueryable<Customer>> (*insert lambda expression from Example 16-18*)

VB
Dim context As New BreakAwayEntities
Dim loc As String = "Malta"
Dim custs As ObjectQuery(Of Customer) = compQuery.Invoke(context, loc)
Dim custlist = custs.ToList

C#
var context = new BreakAwayEntities();
var loc = "Malta";
IQueryable<Customer> custs = compQuery.Invoke(context, loc);
var custlist = custs.ToList();

VB
Using context = New BAEntities
  Dim cust = context.Contacts.FirstOrDefault
End Using

Using context As New BAEntities
  Dim destination As String = "Malta"
  Dim custs As ObjectQuery(Of Customer) =  _
           compQuery.Invoke(context, destination)
  Dim custlist = custs.ToList
End Using

Using context As New BAEntities
  Dim destination As String = "Bulgaria"
  Dim custs As ObjectQuery(Of Customer) =  _
     compQuery.Invoke(context, destination)
  Dim custlist = custs.ToList
End Using

C#
using (var context = new BAEntities ())
{
  var cust = context.Contacts.FirstOrDefault();
}
using (BreakAwayEntities context = new BAEntities ())
{
  string destination = "Malta";
  ObjectQuery<Customer> custs = compQuery.Invoke(context, destination);
  var custlist = custs.ToList();
}
using (BreakAwayEntities context = new BAEntities ())
{
  string destination = "Bulgaria";
  ObjectQuery<Customer> custs = compQuery.Invoke(context, destination);
  var custlist = custs.ToList();
}

By default, compiled Entity SQL queries are stored in an application domain cache for both EntityClient queries and ObjectQuery queries. As part of the query pipeline, the cache will be checked for a matching Entity SQL query (parameters are taken into account here, as with the precompiled LINQ queries), and if a precompiled version of that query is available, it will be used.

Set the Boolean EntityCommand.EnablePlanCaching to true or false to enable or disable caching for EntityClient. ObjectQuery.EnablePlanCaching is the property for enabling or disabling query plan caching for ObjectQuery queries.

Given the previous advice about avoiding SQL injection attacks with dynamic Entity SQL, Microsoft recommends that if you are building Entity SQL expressions dynamically, you disable query plan caching. The stored queries are case-sensitive, so if you have a query in which you type “select value con …” in one method and “SELECT VALUE con …” in another, they won’t be considered matching queries, and not only will you lose the benefit of the cached query, but the size of the cache will increase as a result of extra queries being stored.

Using tests similar to the previous performance tests, you can see the difference in query processing time when caching is enabled or disabled, as shown in the following code and in Table 16-5:

SELECT VALUE c from AWLTEntities.EFCustomers AS c

Again, in this case the time for the cached query is significantly less than the non-cached query.

Entity SQL querying with EntityClient versus Object Services

Although the difference between querying with and without the cache may not be surprising, the difference between querying with Object Services and EntityClient might be.

When running the test with a query that returns data of a more complex shape, the difference shifts, as you can see in the following code and in Table 16-6:

SELECT cust.CompanyName,cust.SalesOrderHeader,
    (SELECT VALUE order.SalesOrderDetail 
     FROM cust.SalesOrderHeader AS order)
FROM AWLTEntities.EFCustomers AS cust

Table 16-6. Average query times for shaped results

Query plan caching state	Enabled	Disabled
Entity SQL with Object Services	21.28 ms	42.51 ms
Entity SQL with EntityClient	17.05 ms	32.15 ms

Now the EntityClient and Object Services queries are more on par—with the EntityClient being about 15% faster. Because EntityClient does not materialize the objects, you would expect it to have some advantages. But why is the query itself impacting the difference between the two methods of querying?

Although object materialization takes some time, so does the task of shaping the EntityDataReader and then pushing in the results. In the case of the simple query, object materialization is very efficient in creating a Customer entity from data that maps exactly to the entity.

In the second query, you are building data that is shaped like that shown in Figure 16-4.

Figure 16-4. The shaped data returned by the Entity SQL performance test

With the more complexly shaped data, once the EntityDataReader is created the cost of pushing the data into that DataReader is a lot less than the cost of materializing a lot of complexly shaped objects.

Again, the Entity Framework will need to generate commands and transform the entity structure into the database structure; thus, compared to working with ADO.NET, where you would be working directly against the database, there will be a performance hit.

For the following tests, I modified the previous tests to include updates and inserts, and because this is much more intensive and time-consuming than just querying data, there are only 10 iterations of the tests, not 100. Each test queries for the entire set of customers (approximately 450), iterates through those customers, and modifies a single field in each one. Once those modifications are made, 10 new customers are added. Finally, the appropriate update method is called (DataAdapter.Update, DataContext.SubmitChanges, or ObjectContext.SaveChanges).

To be fair, there are two tests for DataSet. The first uses the default Update, which sends one command at a time to the database. The second leverages UpdateBatch and sets the batch to 100 commands at a time. The final times represent the average of performing this entire operation 10 times.

The results are interesting. The Entity Framework is faster than DataAdapter and LINQ to SQL, as you can see in Table 16-7.

You can perform updates with “classic ADO.NET” in a variety of ways, and you may achieve different results relative to the two newer technologies. But this at least gives you an idea that something very smart is happening under the covers of the Entity Framework when SaveChanges is called.

Example 16-22 uses a separate class for managing the ObjectContext and performing the database interaction. The main program then creates a separate thread when it needs the ObjectContext to do something. Delegates and callbacks are used so that it’s possible for entities to be returned from the separate thread.

Notice that every time the ObjectContext is about to be impacted, a lock is placed on it. C# has a handy lock keyword, but with Visual Basic you’ll need to use the Threading.Monitor class to do this.

VB
Imports System
Imports System.Threading
Imports BAGA.BreakAwayModel
Module Module2

' Delegate that defines the signature for the callback method.
Public Delegate Sub contextCallback _
 (ByVal contactList As List(Of Contact))
Private contacts As List(Of Contact)

Public Class MainModule
  Public Shared Sub Main()
    Dim occ As New ObjectContextClass _
             (New contextCallback(AddressOf ResultCallback))
    Dim t As New Thread(AddressOf occ.GetCustomers)
    t.Start()
    t.Join()
    Console.WriteLine("Contacts Retrieved: " & contacts.Count.ToString)
    Console.WriteLine(contacts(0).LastName & contacts(0).ModifiedDate)
    contacts(0).ModifiedDate=DateTime.Now
    Console.WriteLine(contacts(0).LastName & contacts(0).ModifiedDate)
    t = New Thread(AddressOf occ.SaveChanges)
    t.Start()
  End Sub

  Public Shared Sub ResultCallback(ByVal contactList As List(Of Contact))
    contacts = contactList
  End Sub
End Class

Public Class ObjectContextClass
  Private context As BreakAwayEntities

  ' Delegate used to execute the callback method when the task is done.
    Private callback As contextCallback

    ' The callback delegate is passed in to the constructor
    Public Sub New(ByVal callbackDelegate As contextCallback)
      callback = callbackDelegate
    End Sub

  Public Sub GetCustomers()
    If context Is Nothing Then
      context = New BreakAwayEntities
    End If
    'put a lock on the context during this operation
    Threading.Monitor.Enter(context) 
    Dim contactquery = From cust In context.Contacts _
                       Where cust.LastName.StartsWith("S")
    'unlock the context
    Dim conList = contactquery.ToList
    Threading.Monitor.Exit(context)
    If Not callback Is Nothing Then callback(conList)
  End Sub

  Public Sub SaveChanges()
    Threading.Monitor.Enter(context)
    context.SaveChanges()
    Threading.Monitor.Exit(context)
  End Sub

End Class

End Module

C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using BAGA;
internal static class Module2
{

  // Delegate that defines the signature for the callback method.
  //
public delegate void contextCallback(List<Contact> contactList);
private static List<Contact> contacts;

public class MainModule
{
  public static void Main()
  {
    ObjectContextClass occ = 
       new ObjectContextClass(new contextCallback(ResultCallback));
    Thread t = new Thread(occ.GetCustomers);
    t.Start();
    t.Join();
    Console.WriteLine("Retrieved: " + contacts.Count.ToString());
    Console.WriteLine(contacts[0].LastName + contacts[0].ModifiedDate);
    contacts[0].ModifiedDate = DateTime.Now;
    Console.WriteLine(contacts[0].LastName + contacts[0].ModifiedDate);
    t = new Thread(occ.SaveChanges);
    t.Start();
  }

  public static void ResultCallback(List<Contact> contactList)
  {
    contacts = contactList;
  }
}

public class ObjectContextClass
{
  private BAEntities context;

  // Delegate used to execute the callback method when the task is done.
    private contextCallback callback;

    // The callback delegate is passed in to the constructor
    public ObjectContextClass(contextCallback callbackDelegate)
    {
      callback = callbackDelegate;
    }

  public void GetCustomers()
  {
    if (context == null)
    {
      context = new BAEntities();

    }
    //put a lock on the context during @this operation;
    System.Threading.Monitor.Enter(context);
    var contactquery = from c in context.Contacts
                       where c.LastName.StartsWith("S")
                       select c;

    ////unlock the context;
    var conList = contactquery.ToList();
    System.Threading.Monitor.Exit(context);
    if (callback != null)
      callback(conList);
}

  public void SaveChanges()
  {
    System.Threading.Monitor.Enter(context);
    context.SaveChanges();
    System.Threading.Monitor.Exit(context);
  }
}
}

It’s important to call out the locking of the context. Because of the way the ObjectContext manages state and relationships, and because of the merge possibilities when new data is brought in, you need to be very careful so that two separate threads do not affect the context at the same time. You should consider this use as an edge case, and you should be sure that you really understand threading before you start spinning your own threads and working with classes that are not thread-safe.

It’s much safer (though less practical in many cases) to keep individual ObjectContexts on completely separate threads so that you don’t have to worry about this as much.

The BackgroundWorker component, introduced in .NET 2.0, does alleviate some of the complexities of working with multiple threads, but still, the Entity Framework does not have any inherent features that make it easy to use in multithreaded applications. Hopefully, future versions of the Entity Framework will make threading and asynchronous programming simpler to work with.

Example 16-22 used a separate thread to host the ObjectContext. Example 16-23 shows another way to use worker threads to perform some concurrent processing on entities. Because this example only performs reads on the entities, the concerns of Example 16-22 are not present. This example sends entities off to a variety of methods that will merely read information from the entities and possibly send a form letter or email. In this case, the code is writing some text out to the console only to demonstrate the concept.

The query pulls back customers along with their reservation and trip information. Then, based on the reservation status, the Customer entity is sent to a different method to create the email. Because the process is being performed in different threads, the emails can be written concurrently and there is no need in this case to wait for any type of result.

When the text is written out to the console, the example also displays the ID of the thread so that you can verify that different threads are being used.

VB
Imports System.Threading
Imports BAGA

Private Sub MultiThreadTest()
  Dim emailThread = New EmailThreadClass
  Using context As New BreakAwayEntities
    Dim custs = From cust In context.Contacts.OfType(Of Customer) _
        .Include("Reservations.Trip.Location") 
    For Each cust In custs
      If cust.Reservations.Any _
           (Function(r) r.Trip.StartDate > Today.AddDays(6)) Then
        'new thread for upcoming trip emails
         Dim workerThread As Threading.Thread = _
          New Threading.Thread(AddressOf emailThread.UpcomingTripEmails)
         workerThread.Start(cust)
      ElseIf cust.Reservations.Any _
              (Function(r) r.Trip.StartDate > Today _
                And r.Trip.StartDate <= Today.AddDays(6)) Then
        'new thread for very soon trip emails
        Dim workerThread As Threading.Thread =  _
         New Threading.Thread(AddressOf emailThread.NextWeek)
        workerThread.Start(cust)
      Else 'no future trips
        'new thread for no upcmoing trips emails
        Dim workerThread As Threading.Thread = _
         New Threading.Thread(AddressOf emailThread.ComeBackEmails)
        workerThread.Start(cust)
      End If
    Next
  End Using
End Sub

Public Class EmailThreadClass
  Public Sub UpcomingTripEmails(ByVal cust As Customer)
    Dim anytrip = cust.Reservations _
       .Where(Function(r) r.Trip.StartDate > Today.AddDays(6)) _
       .First.Trip
    Console.WriteLine("Thread " & Thread.CurrentThread.ManagedThreadId)
    Console.WriteLine("            Dear " & cust.FirstName.Trim & _
      ", Your trip to " & anytrip.Destination.DestinationName.Trim & _
      " begins on " & anytrip.Trip.StartDate & _
      ". We look forward to seeing you soon.")
    Console.WriteLine()
  End Sub

  Public Sub NextWeek(ByVal cust As Customer)
    Dim anytrip = cust.Reservations _
       .Where(Function(r) r.Trip.StartDate <= Today.AddDays(6)) _
       .First.Trip
    Console.WriteLine("Thread " & Thread.CurrentThread.ManagedThreadId)
    Console.WriteLine("            Dear " & cust.FirstName.Trim & _
      ",  Your trip to " & anytrip.Location.LocationName.Trim & _
      " begins in only a few days. Please let us know if " & _
      " you have any last minute questions.")
    Console.WriteLine()
  End Sub

  Public Sub ComeBackEmails(ByVal cust As Customer)
    Console.WriteLine("Thread " & Thread.CurrentThread.ManagedThreadId)
    Console.WriteLine("            Dear " & cust.FirstName.Trim & _
       ", We haven't seen you in a while. We hope you'll consider" &  _
       "  BreakAway Geek Adventures for your next vacation.")
    Console.WriteLine()
  End Sub
End Class

C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BAGA;
using System.Threading;

namespace Chapter16CS
{
  public class EmailThreads
  {
    public static void Main()
    {
      var emailThread = new EmailThreadClass();
      using (BAEntities context = new BAEntities())
      {
        var custs =
            from cust in context.Contacts.OfType<Customer>()
             .Include("Reservations.Trip.Destination")
            select cust;
        foreach (var cust in custs)
        {
          if (cust.Reservations
              .Any((r) => r.Trip.StartDate > DateTime.Today.AddDays(6)))
          {
            //new thread for upcoming trip emails
            Thread workerThread = 
               new Thread(emailThread.UpcomingTripEmails);
            workerThread.Start(cust);
          }
          else if (cust.Reservations
            .Any(r => r.Trip.StartDate > DateTime.Today 
                 & r.Trip.StartDate <= DateTime.Today.AddDays(6)))
          {
            //new thread for very soon trip emails
            Thread workerThread = new Thread(emailThread.NextWeek);
            workerThread.Start(cust);
          }
          else //no future trips
          {
            //new thread for no upcmoing trips emails
            Thread workerThread = 
               new Thread(emailThread.ComeBackEmails);
            workerThread.Start(cust);
          }
        }
      }
    }
  }

  public class EmailThreadClass
  {
    public void UpcomingTripEmails(object customer)
    {
      var cust = (Customer)customer;
      var anytrip = cust.Reservations
       .Where(r => r.Trip.StartDate > DateTime.Today.AddDays(6))
       .First().Trip;

Console.WriteLine("Thread " + Thread.CurrentThread.ManagedThreadId);
Console.WriteLine("            Dear " + cust.FirstName.Trim() + 
   ", Your trip to " + anytrip.Destination.DestinationName.Trim() +
 " begins on " + anytrip.StartDate + 
  ". We look forward to seeing you soon.");
Console.WriteLine();
    }

    public void NextWeek(object customer)
    {
      var cust = (Customer)customer;
      var anytrip = cust.Reservations
       .Where((r) => r.Trip.StartDate <=DateTime.Today.AddDays(6))
      .First().Trip;

Console.WriteLine("Thread " + Thread.CurrentThread.ManagedThreadId);
Console.WriteLine("            Dear " + cust.FirstName.Trim() + 
  ",  Your trip to " + anytrip.Destination.DestinationName.Trim() + 
  " begins in only a few days. Please let us know if " + 
  " you have any last minute questions.");
Console.WriteLine();
    }

    public void ComeBackEmails(object customer)
    {
      var cust = (Customer)customer;

Console.WriteLine("Thread " + Thread.CurrentThread.ManagedThreadId);
Console.WriteLine("            Dear " + cust.FirstName.Trim() + 
  ", We haven't seen you in a while. We hope you'll consider" +
  "  BreakAway Geek Adventures for your next vacation.");
Console.WriteLine();
    }
  }
}