.NET v1 provided a set of data access services called ADO.NET.^[34] In more recent years, ADO.NET seems to have grown into an umbrella term—as new data access features have been added, most (but not all) appear in the ADO.NET section of the documentation. But to understand the layers, it’s worth starting with the two parts that were in the first version: interfaces for querying and updating databases, and classes that support disconnected use of data.

string sqlConnectionString = @"Data Source=.sqlexpress;" +
    "Initial Catalog=AdventureWorksLT2008;Integrated Security=True";
string state = "California";

using (DbConnection conn = new SqlConnection(sqlConnectionString))
using (DbCommand cmd = conn.CreateCommand())
{
    cmd.CommandText =
        "SELECT AddressLine1, AddressLine2, City FROM SalesLT.Address WHERE " +
        "StateProvince=@state";
    DbParameter stateParam = cmd.CreateParameter();
    stateParam.ParameterName = "@state";
    stateParam.Value = state;
    cmd.Parameters.Add(stateParam);

    conn.Open();
    using (DbDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            string addressLine1 = reader.GetString(0);
            // AddressLine2 is nullable, so we need to be prepared to get
            // back either a string or a DBNull
            string addressLine2 = reader.GetValue(1) as string;
            string city = reader.GetString(2);

            Console.WriteLine(addressLine1);
            Console.WriteLine(addressLine2);
            Console.WriteLine(city);
        }
    }
}

string state = "California";
using (var context = new AdventureWorksLT2008Entities())
{
    var addresses = from address in context.Addresses
                    where address.StateProvince == state
                    select address;

    foreach (var address in addresses)
    {
        Console.WriteLine(address.AddressLine1);
        Console.WriteLine(address.AddressLine2);
        Console.WriteLine(address.City);
    }
}

As we saw in Chapter 8, LINQ lets you perform tasks with collections of data including filtering, sorting, and grouping. In that chapter, we were working only with objects, but these are exactly the jobs that databases are good at. Moreover, one of the motivations behind LINQ’s design was to make it easier to use databases from code. As you can see in Example 14-2, LINQ blends data access seamlessly into C# code—this database example looks very similar to the object examples we saw in the earlier chapter.

Example 14-2 uses LINQ to Entities—a LINQ provider for the Entity Framework. The Entity Framework didn’t appear until Service Pack 1 of Visual Studio 2008, and there’s an older database LINQ provider called LINQ to SQL that appeared in the first Visual Studio 2008 release.

LINQ to SQL works only with SQL Server and SQL Server Compact 3.5, and has a fairly narrow goal. It aims to reduce the overhead involved in writing data access code by providing a convenient C# syntax for working with the data in a SQL Server database.

The Entity Framework is similar, but it adds a couple of additional features. First, it is designed to support multiple database vendors—it has an open provider model, enabling support to be written for any database, and you can get providers for most popular databases. Second, the Entity Framework allows the .NET representation to have a different structure from your database schema if necessary. You can define a conceptual model whose entities do not necessarily correspond directly to rows of particular tables—an entity might include data that spans multiple tables in the database itself. This entity can then be represented as a single object.

Of course, it’s possible to have your conceptual model correspond exactly to your database model—you’re free to create a straightforward mapping where one entity represents one row in one table. Used in this way the Entity Framework, in conjunction with LINQ to Entities, makes LINQ to SQL look redundant. So why do we have both?

The main reason LINQ to SQL exists is that it was ready when Visual Studio 2008 shipped, whereas Microsoft hadn’t finished the Entity Framework at that point. LINQ was a major part of that release, and since one of the main motivations behind LINQ was data access, shipping without a LINQ-based data access feature would have been a bit of a letdown. LINQ to SQL was developed by a different team (it came from the LINQ team, and not the data access group), and it was ready earlier, due no doubt in part to its less ambitious goals.

Microsoft has stated that while both technologies are fully supported, the Entity Framework is where the majority of its efforts will now be focused. Visual Studio 2010 adds a few new LINQ to SQL features, but LINQ to Entities will see more development in the long run.

That’s why this chapter’s focus is the Entity Framework (although a lot of the concepts here apply equally to both technologies). That being said, both authors really like LINQ to SQL. In scenarios where we’re using SQL Server and where we don’t need the conceptual model and mapping features of the Entity Framework, we’re both more inclined to use LINQ to SQL because of its simplicity and because we’ve already learned how to use it. But if you learn only one data access technology for .NET, the Entity Framework looks like the better choice for the long term.

By the time Microsoft shipped the Entity Framework, various third-party options for mapping relational data into object models had been around for a while. We’re not going to talk about them in this book, but it’s useful to be aware that the Entity Framework isn’t the only game in town.

Perhaps the best known alternative is NHibernate. This is a .NET version of Hibernate, a popular Java ORM (Object Relational Mapper). NHibernate had already been around for a few years by the time the Entity Framework emerged (and its Java progenitor is considerably older). So in many respects it’s a more mature and more fully featured ORM than the Entity Framework. On the other hand, NHibernate predates LINQ (and Java currently has nothing resembling LINQ), so at the time of this writing, its LINQ support is somewhat limited.

Many other ORMs are available for .NET, some free and some commercial. They are too numerous to mention here, as a quick web search will confirm.

Most communication with databases happens over specialized, vendor-specific protocols. Firewalls are usually configured not to let such protocols through, and with good reason: from a security perspective, making your database directly accessible on the Internet tends to look like a very bad idea. Nonetheless, some people want to do this, and there are scenarios in which it’s not the terrible idea it might first seem, particularly if you can exercise sufficient control over what gets exposed.

With WCF Data Services, you can present a relational data store over HTTP and XML or JSON. You can be selective about what data you expose and to whom. Moreover, the model you present doesn’t necessarily have to be the same as your underlying database structure. In fact, there doesn’t have to be a database involved at all—there’s a provider model that enables you to present any data through this mechanism, as long as you can find a way to make it look like relational data.

You will normally use WCF Data Services in conjunction with the Entity Framework—you can define the entities you’d like to present over HTTP, and use the framework’s mapping services to bridge between that and the underlying data store. So we’ll be looking at these services in more detail later in the chapter, once we’ve finished exploring the Entity Framework.

The focus of WCF Data Services is different than for the other data access features we’ve discussed so far—it’s mainly about presenting data on the network, where everything else has been about consuming data. However, there’s also a client-side component that provides LINQ-based querying for such services. While it’s part of the WCF Data Services technology, it’s optional—you’re not obliged to use it on the client. And this client doesn’t strictly require WCF Data Services on the server—the client-side parts could be used against any service that exposes data in the same way.

Silverlight uses a seriously trimmed down version of the .NET Framework to keep its download size and install time tolerably small. It doesn’t have much data access support. In fact, size is not the only reason—it wouldn’t normally make sense for a Silverlight client application to attempt to connect directly to a database, because Silverlight is a client-side web technology and most system administrators work to ensure that their databases are not accessible via their native protocols over the Internet.

Of course, a direct connection to a database server might be an option in an intranet scenario, but it’s not supported. Silverlight offers LINQ, but neither the LINQ to SQL nor the LINQ to Entity Framework providers are available, because the underlying database access mechanisms that these providers use are missing. The only supported database access mechanism in Silverlight is the WCF Data Services client.

The full .NET Framework is designed to work with a wide range of databases. The simple ADO.NET data access we started with uses interfaces to allow database vendors to supply their own database-specific implementations. Likewise, the Entity Framework is database-agnostic—it has an open provider model designed to allow support for any relational database to be added. Of course, Microsoft ships a provider for its own database, SQL Server, but other suppliers offer providers for various databases, including Oracle, MySQL, PostgreSQL, SQLite, Sybase, and DB2.

In this book, we will use SQL Server. The examples work with SQL Server, which is available for free. (Some editions of Visual Studio will automatically install SQL Server 2008 Express for you by default.) The Express edition of SQL Server is the same database engine as the “real” versions, but with some limits on database size and with some of the more advanced features missing. Despite being a trimmed down version, it’s easily capable of supporting substantial websites. It can also be used on client applications written with WPF or Windows Forms, to support client-side data stores or caching, although it can complicate the installation process for such an application—installing a SQL Server instance is not a trivial task.

Visual Studio puts the whole Entity Data Model definition in an .edmx file, which is just XML. The wizards and editor windows we’ve seen so far are just convenient views into that XML. If you look directly at the XML in the .edmx, you’ll see it contains sections corresponding to the three parts of the model—storage schema, conceptual schema, and mappings. But the whole point of this exercise was to make it easier to use data from code. So Visual Studio generates code based on the contents of any .edmx files in your project.

For each entity type you define, a corresponding .NET class will be generated. These classes provide normal properties for each property in the entity type’s definition. So when you create and edit entity types, you are in effect defining .NET types that you can use from your C# code.

Figure 14-3. EDM in the Model Browser

Figure 14-4. The EDM Mapping Details window

Visual Studio also generates one extra class representing something called the object context. You use this to obtain entity objects representing data already in the database and it’s also where you go to add new data. And as we’ll see later, this object provides other services for managing the data access operations. This type derives from ObjectContext, and sometimes it’s just referred to as the context. Example 14-3 uses this generated context type to retrieve rows from the SalesOrderHeader table for a particular date.

using (var dbContext = new AdventureWorksLT2008Entities())
{
    DateTime orderDate = new DateTime(2004, 6, 1);
    var orders = from order in dbContext.SalesOrderHeaders
                 where order.OrderDate == orderDate
                 select order;

    foreach (SalesOrderHeader order in orders)
    {
        Console.WriteLine(order.TotalDue);
    }
}

Notice that this example wraps the context in a using statement—the object context is a disposable resource because it does a lot of work behind the scenes, and it needs to tidy up once you no longer need the state it builds up. So it’s important that you dispose it when you’re done with it.

The object context’s type here is AdventureWorksLT2008Entities. By default, Visual Studio will just append the word Entities to your database connection name. You can change this by selecting the EntityContainer item in the Model Browser—you can see this in the middle of Figure 14-3—and then use the Properties panel to choose its name. But we’ll keep the default name in the examples.

Notice that the LINQ query in Example 14-3 uses the context’s SalesOrderHeaders property as the query source. That’s not quite the same as the table name—the wizard has added an s. By default, the Entity Framework wizard will attempt to pluralize and depluralize words as appropriate—in general, it gives entity types singular names while properties that return collections of entities are plural. (The names in our conceptual model can differ slightly from our storage model thanks to the Entity Data Model’s mapping.) If you don’t like this plural handling, there’s a checkbox to turn it off when you import tables with the wizard.

Example 14-3 also uses the SalesOrderHeader class generated for the entity type of the same name. The order range variable in the LINQ query is of this type, as is the order iteration variable in the loop.

It’s this generated entity class that enables us to refer to database columns using normal C# syntax. The LINQ query’s where clause uses that entity class’s OrderDate property to build a query that uses the OrderDate column of the corresponding database table. Likewise, the loop uses normal C# property syntax to retrieve TotalDue, which represents the column of the same name in the database.

If this seems rather uneventful, well, that’s the idea. Compare this to the much more fiddly code in Example 14-1—by mapping database columns to properties, the Entity Framework reduces the amount of friction involved in writing data access code.

Let’s change things a bit, so the mapping has something to do. Most of the column names in this example database happen to fit the usual .NET conventions for property names, but there’s an exception: the rowguid column in SalesOrderHeader is not capitalized in the usual way. (This column exists to support SQL Server replication, so it’s fairly unusual to want to use it from code, but in this example it’s the only column with a funny-looking name.) If you change this name to RowGuid in the designer (either by double-clicking on the property or by using the Properties panel) Visual Studio will update the mapping, and the Mapping Details panel will show that the rowguid column in the table is mapped to the RowGuid property of the entity. (If you’d prefer a less subtle change, you could rename the Customer entity’s ModifiedDate to, say, LastChanged. The mapping lets you use any names you like.)

Changing the names of a few columns isn’t very exciting. (And with this particular example database it’s not even very useful, although if you’re dealing with more idiosyncratic naming schemes, renaming becomes more important.) So let’s look at one of the more interesting mapping features: the way in which the EF handles relationships between entities.

Databases usually have relationships between tables. In the Adventure Works example, the Customer table has a foreign key relationship with the SalesOrderHeader table. Both tables have a CustomerID column. This is the primary key of the Customer table, and the database schema includes a constraint enforcing that the CustomerID column in the SalesOrderHeader can contain only a value for which a corresponding Customer row exists. Or to put it more simply, each SalesOrderHeader row must be related to a specific Customer row.

Visual Studio’s EDM wizard looks for foreign key constraints in the database schema to discover the relationships between tables. In the EDM, it turns these into associations. (The distinction between a relationship and an association is somewhat subtle. An association is a named item in the Entity Data Model representing a particular relationship. The main reason this distinction exists is that relationships are a slightly more general concept—associations are capable of modeling only certain kinds of relationships.) Just as tables added with the wizard end up appearing in all three parts of the EDM—a table will appear in the store schema, a corresponding entity will be added to the conceptual schema, and there will be a mapping between the two—a similar process occurs for associations. If a foreign key constraint indicates that there’s a relationship between two database tables added through the wizard, Visual Studio will add an association to the EDM’s store schema and also to the conceptual schema, and it will set up a suitable mapping between these two associations. And on top of this, it will add navigation properties to the related entities in the conceptual model.

using (var dbContext = new AdventureWorksLT2008Entities())
{
    var customerOrderCounts = from cust in dbContext.Customers
                              select new
                              {
                                  cust.CustomerID,
                                  OrderCount = cust.SalesOrderHeaders.Count
                              };
    foreach (var customerInfo in customerOrderCounts)
    {
        Console.WriteLine("Customer {0} has {1} orders",
            customerInfo.CustomerID, customerInfo.OrderCount);
    }
}

var info = from cust in dbContext.Customers
           select new
           {
               cust.CustomerID,
               Orders = from order in cust.SalesOrderHeaders
                        where order.Status == 5
                        select new
                        {
                            order.TotalDue,
                            ItemCount = order.SalesOrderDetails.Count
                        }
           };

Customer myCustomer = dbContext.Customers.Single(
    cust => cust.CustomerID == 29531);
Console.WriteLine(myCustomer.SalesOrderHeaders.Count);

Customer myCustomer = dbContext.Customers.Single(
    cust => cust.CustomerID == 29531);
myCustomer.SalesOrderHeaders.Load();
Console.WriteLine(myCustomer.SalesOrderHeaders.Count);

dbContext.ContextOptions.LazyLoadingEnabled = false;

var customersWithOrderDetails = dbContext.Customers.
    Include("SalesOrderHeaders.SalesOrderDetails");
Customer myCustomer = customersWithOrderDetails.Single(
    cust => cust.CustomerID == 29531);
Console.WriteLine(myCustomer.SalesOrderHeaders.Count);

Inheritance presents a challenge for an ORM, because the typical object-oriented notions of inheritance don’t have any direct parallel in the relational model. Various solutions exist because there isn’t one really good way to do this. The Entity Framework supports mappings for a couple of the popular approaches for attempting to bridge this chasm.

While there are several approaches to mapping (which we’ll get to shortly), the conceptual model’s handling of inheritance works the same way in all cases, and is very similar to inheritance in C#. Any entity type can optionally specify one other entity type as its base type. Entities with a base type inherit all the properties from that base. An entity cannot specify more than one base type, but you are allowed to derive from an entity that derives from another entity (i.e., you can have an inheritance chain). And the corresponding generated entity classes that you use from C# will represent these inheritance relationships with normal class inheritance.

You will need to define mappings for your base entity type in the usual way. All the derived types will inherit these mappings. The question is: how do we map features unique to individual derived types?

The first mapping approach involves mapping all entity types sharing a particular base entity type to a single table in the database. The entity type the EF chooses to represent a particular row is chosen based on a discriminator column—in the mapping you simply provide a list that says, for example, if the discriminator column contains 1, the entity type is Employee, and if it’s 2, the type is Manager, while if it’s 3, the type is Director, and so on. These derived types will presumably have additional properties distinguishing them from one another, and these would map to nullable columns in the table. They will need to be nullable, because these columns will have values only when you’re using the derived types that support them—non-nullable database columns need to be mapped to properties in the base entity type if you’re using this mapping style.

The second mapping approach uses a separate table for each derived type. Derived types still inherit the base mappings, so in this scenario, derived entity types will be involved with two or more tables: the table unique to the derived type, along with any tables used by the base type. This approach requires all the tables involved to use the same primary key.

None of these mapping features would be much use without some way to retrieve data from the database, so we’ll now look at how to execute queries in the Entity Framework.

The LINQ provider for the Entity Framework, LINQ to Entities, supports all of the standard LINQ operators we saw in Chapter 8, but it works a little differently. The idea of deferred execution is still present, and it’s even more important. The point at which you cause the LINQ query to execute—the instant at which your code first starts trying to use the results—is the point at which the EF will need to send a request to the database. So looking at the code from Example 14-3, the statement shown in Example 14-9 does not get anything from the database.

var orders = from order in dbContext.SalesOrderHeaders
             where order.OrderDate == orderDate
             select order;

As always with LINQ, a query expression only defines a query—orders is an object that knows what it’s supposed to return if anything happens to enumerate it. So it’s the foreach loop in Example 14-3 that kicks off the actual request.

The way the EF processes the request is different from how LINQ to Objects works. LINQ to Objects works by forming a chain of operators that work sequentially—the source collection might pass through the Where operator, followed by, say, an OrderBy or a Group operator. The Where operator in LINQ to Objects works by walking through every single item in the source, discarding the ones that don’t meet the filter criteria, and the ones that do meet the criteria get passed on to the next item in the chain.

We really don’t want data access code to work that way, and as mentioned earlier, the EF lets the database do the filtering, which is far more efficient than fetching an entire table and then filtering the items in code. We’ll now verify that it really works this way by using the SQL Profiler tool to examine what the EF does for us.

By single-stepping through the code in Visual Studio while running the SQL Profiler, we can see that nothing appears in the profiler until we start to execute the foreach loop, at which point the profiler shows an Audit Login message, indicating that our program has opened a connection to the database. This is followed by a RPC:Completed message, indicating that SQL Server processed a request. When we select this message, the profiler shows the SQL that the EF just ran for us:

exec sp_executesql N'SELECT
[Extent1].[SalesOrderID] AS [SalesOrderID],
[Extent1].[RevisionNumber] AS [RevisionNumber],
[Extent1].[OrderDate] AS [OrderDate],
[Extent1].[DueDate] AS [DueDate],
[Extent1].[ShipDate] AS [ShipDate],
[Extent1].[Status] AS [Status],
[Extent1].[OnlineOrderFlag] AS [OnlineOrderFlag],
[Extent1].[SalesOrderNumber] AS [SalesOrderNumber],
[Extent1].[PurchaseOrderNumber] AS [PurchaseOrderNumber],
[Extent1].[AccountNumber] AS [AccountNumber],
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[ShipToAddressID] AS [ShipToAddressID],
[Extent1].[BillToAddressID] AS [BillToAddressID],
[Extent1].[ShipMethod] AS [ShipMethod],
[Extent1].[CreditCardApprovalCode] AS [CreditCardApprovalCode],
[Extent1].[SubTotal] AS [SubTotal],
[Extent1].[TaxAmt] AS [TaxAmt],
[Extent1].[Freight] AS [Freight],
[Extent1].[TotalDue] AS [TotalDue],
[Extent1].[Comment] AS [Comment],
[Extent1].[rowguid] AS [rowguid],
[Extent1].[ModifiedDate] AS [ModifiedDate]
FROM [SalesLT].[SalesOrderHeader] AS [Extent1]
WHERE [Extent1].[OrderDate] = @p__linq__0',
N'@p__linq__0 datetime',@p__linq__0='2004-06-01 00:00:00'

It might be quite long, but structurally that’s a pretty simple SELECT statement. The only reason it’s so large is that it explicitly requests every column required by the entity (and it has specified each column in a fairly verbose manner). The interesting part is in the last two lines. The penultimate line is a parameterized WHERE clause comparing the OrderDate to a named argument. This is what became of our LINQ query’s where clause. And the final line provides a value for that named argument.

Note that you’re free to chain operators together in LINQ to Entities just as you can in LINQ to Objects. For example, we could build on the orders query from Example 14-3:

var orderedOrders = orders.OrderBy(order => order.OrderDate);

Or, if you’d prefer to carry on using LINQ syntax, Example 14-10 is equivalent.

var orderedOrders = from order in orders
                    orderby order.OrderDate
                    select order;

This doesn’t execute the orders query. It just means we have two queries now—the orders query that just filters, and then the orderedOrders query that filters and then sorts. You could think of this chained query as shorthand for Example 14-11, which explicitly combines the clauses from Example 14-9 and Example 14-10 into one query.

var orderedOrders = from order in dbContext.SalesOrderHeaders
                    where order.OrderDate == orderDate
                    orderby order.OrderDate
                    select order;

Regardless of the various equivalent ways we would build the second query, the result of executing it (e.g., by iterating over it in a foreach loop) is, as you’d expect, a SQL query that includes an ORDER BY clause as well as a WHERE clause. (And as it happens, that’s not hugely useful because in this example database, all the orders have the exact same date. With slightly more realistic data, this would have the expected effect, though.)

So LINQ to Entities queries work in a fundamentally different way from the LINQ to Objects queries we saw previously. In LINQ to Objects, the expression in a where clause is simply a delegate in disguise—it’s a method that the Where operator calls for each item in turn to work out whether to include that in the results. But with LINQ to Entities (and LINQ to SQL for that matter) the LINQ query’s where clause has been translated into T-SQL and sent to the database—the expression we wrote in C# ends up running in a different language, probably on a different machine. If you want to understand how these kinds of queries are able to work so differently for different providers, see the sidebar on the next page.

This translation is obviously very useful for shifting the work to the database, but it brings some limitations. If you try to add arbitrary method calls into the middle of a LINQ query, it’ll fail in LINQ to Entities. For example, suppose we have the following helper:

static DateTime NextDay(DateTime dt)
{
    return dt + TimeSpan.FromDays(1);
}

We could try to use this in a LINQ query:

var orders = from order in dbContext.SalesOrderHeaders
             where order.OrderDate == NextDay(orderDate)
             select order;

With LINQ to Objects this would work just fine—it’s all just C# code, and you can use any valid Boolean expression in a where clause, including expressions that invoke methods. But with LINQ to Entities, although this will compile, the EF will throw a NotSupportedException at the point at which you try to execute the query. Its error message will read:

LINQ to Entities does not recognize the method 'System.DateTime
 NextDay(System.DateTime)' method, and this method cannot be translated into
 a store expression.

LINQ to Entities queries are limited to the things that the EF knows how to turn into database queries, and since it doesn’t know anything about this NextDay method you’ve written, it can’t work out how to do that. Of course, when you bear in mind that a LINQ to Entities query executes on the database, it’s hardly surprising that you can’t invoke arbitrary methods in your application from the middle of a query. But the EF integrates some database features into your code so seamlessly that it’s sometimes easy to forget where the boundary between your application and the database lies.

public static IEnumerable<TSource> Where<TSource>(
    this IEnumerable<TSource> source,
    Func<TSource, bool> predicate)

public static IQueryable<TSource> Where<TSource>(
    this IQueryable<TSource> source,
    Expression<Func<TSource, bool>> predicate)

While LINQ to Entities is a very convenient way to build queries, it’s just a layer on top of the Entity Framework’s underlying query system, which has its own query language.

The Entity Framework defines a query language for making queries against the conceptual model—rather than running queries against a database, as you do in normal SQL dialects, you can run queries that work directly against the entities in your model, as the name Entity SQL (or ESQL) suggests.

Why do we need a second way of making queries when we already have LINQ to Entities? Well, from a historical perspective that question has things back to front: during the Entity Framework’s development, ESQL was around long before LINQ to Entities. But since LINQ to Entities made it into the first version of the EF, it’s still reasonable to ask why we have both, what ESQL is for, and when it might look like a better choice than LINQ.

ESQL’s main benefit is that it’s sometimes useful to be able to represent a query as text. In fact, the Entity Data Model itself exploits this—there are some advanced scenarios in which ESQL queries can be embedded in the .edmx file. LINQ wouldn’t be an option here because .edmx is just XML; to use LINQ requires a language that supports LINQ.^[37] If you wanted to store custom queries in a configuration file, you really wouldn’t want to have to run the C# compiler at runtime to interpret the queries. And with ESQL you don’t need to—you can represent a query as a string and the EF can execute that for you at runtime.

Another feature of a string-based query language is that it’s relatively easy to compose queries at runtime. With a LINQ query expression, the structure is fixed at compile time and you only really get to tweak individual arguments, much like a fixed SQL query with a few named arguments. (Technically, it is actually possible to build LINQ queries dynamically. After all, LINQ operators are chained together with simple function calls. However, dynamic composition of Expression<T> trees turns out to be surprisingly difficult. It’s not a scenario C# attempts to help you with—you end up having to construct the expression trees without the compiler’s assistance. This is not for the fainthearted.)

Of course, string-based queries have a massive downside compared to LINQ: the C# compiler cannot offer any help as it doesn’t understand ESQL. If your ESQL strings are badly formed, you only get to find that out at runtime. And even if your ESQL is syntactically correct, C# does not understand the relationship between it and your code—whereas with a LINQ to Entities query C# can detect things such as type mismatches, it won’t spot when your ESQL gets out of sync with the code that uses the results.

Besides the inherent benefits and disadvantages of a string-based query, there’s also the fact that ESQL is, in effect, the native query language for the EF. This means there are a few EF features that can be accessed only through ESQL, although they’re all somewhat arcane. For example, an ESQL query can navigate associations between entities even if you’ve neglected to define navigation properties to represent those associations.

Example 14-12 shows a simple example that illustrates the basic use of ESQL.

using (var dbContext = new AdventureWorksLT2008Entities())
{
    DateTime orderDate = new DateTime(2004, 6, 1);
    var query = dbContext.CreateQuery<SalesOrderHeader>("SELECT VALUE o " +
            "FROM AdventureWorksLT2008Entities.SalesOrderHeaders AS o " +
            "WHERE o.OrderDate = @orderDate",
        new ObjectParameter("orderDate", orderDate));

    foreach (var order in query)
    {
        Console.WriteLine(order.TotalDue);
    }
}

This has the same effect as Example 14-3, but using ESQL in place of a LINQ query. While this looks similar to a typical SQL query, the VALUE keyword is specific to ESQL. We use this to indicate that we don’t want the usual column-like behavior of SQL. You can write a more traditional-looking query in ESQL, such as:

SELECT o.TotalDue, o.OrderDate
     FROM AdventureWorksLT2008Entities.SalesOrderHeaders AS o
     WHERE o.OrderDate = @orderDate

This asks for specific columns from the entity rather than the whole entity. This is legal ESQL, but it would fail at runtime in the context of Example 14-12. That example creates the query with a call to CreateQuery<SalesOrderHeader> on the object context. The generic type argument to CreateQuery—SalesOrderHeader here—indicates the type of result we’re expecting from the query, but this modified query clearly returns something other than a SalesOrderHeader. It returns a couple of columns from each matching entity. When you build a query like this, you get back objects that implement IDataRecord—a general-purpose interface used across all of ADO.NET to represent a record (such as a table row) whose columns might not be known until runtime. (This is one of the interfaces listed in Table 14-1.) So you’d need to use CreateQuery<IDataRecord> to create such a query, and a suitably modified loop to extract the results:

var query = dbContext.CreateQuery<IDataRecord>(
    "SELECT o.TotalDue, o.OrderDate " +
        "FROM AdventureWorksLT2008Entities.SalesOrderHeaders AS o " +
        "WHERE o.OrderDate = @orderDate",
    new ObjectParameter("orderDate", orderDate));

foreach (var order in query)
{
    Console.WriteLine(order["TotalDue"]);
}

Even if you ask for the whole entity as a single column in the SELECT clause, for example:

SELECT o
     FROM AdventureWorksLT2008Entities.SalesOrderHeaders AS o
     WHERE o.OrderDate = @orderDate

the query will still return IDataRecord objects, not entities. Each data record returned by this query would have a single column called o that contains a SalesOrderHeader entity. To get to the entity you’d need to unwrap it inside your loop:

foreach (var row in query)
{
    SalesOrderHeader o = (SalesOrderHeader) row["o"];
    Console.WriteLine(o.TotalDue);
}

The VALUE keyword is just a shortcut that tells ESQL to omit the IDataRecord wrapper, and to return a sequence of unwrapped entities. This enables Example 14-12 to assume that it will get SalesOrderHeader entities back from the query.

LINQ to Entities and ESQL are not mutually exclusive. You are free to use an ESQL query as the source for a LINQ query. Here’s a contrived example:

var orders = dbContext.CreateQuery<SalesOrderHeader>("SELECT VALUE o " +
        "FROM AdventureWorksLT2008Entities.SalesOrderHeaders AS o " +
        "WHERE o.OrderDate = @orderDate",
    new ObjectParameter("orderDate", orderDate));

var orderedOrders = from order in orders
                    orderby order.DueDate
                    select order;

This might be useful if you wanted to store ESQL queries in some sort of configuration mechanism to allow the exact query to be changed, but to do further processing of the results of that query with LINQ.

Yet another feature enabled by ESQL is that it lets code built around the v1 ADO.NET mechanisms shown in Example 14-1 work with the EF. The System.Data.EntityClient namespace defines concrete types that derive from the abstract base classes listed in Table 14-1: EntityConnection derives from DbConnection, EntityCommand derives from DbCommand, and so on. As far as code written to use these abstract base classes is concerned, the Entity Framework ends up looking like just another database with another funky variety of SQL. As long as your ESQL selects only column values and not whole entities, queries will only ever return the same basic data types other providers would, so the behavior will look much like any other ADO.NET v1 provider.

To execute database queries, it’s necessary to connect to a database, so the object context needs connection information. This information typically lives in the App.config file—when you first run the EDM wizard, it will add a configuration file if your application does not already have one, and then it adds a connection string. Example 14-13 shows a configuration file containing a typical Entity Framework connection string. (This has been split over multiple lines to fit—normally the connectionString attribute is all on one line.)

<configuration>
  <connectionStrings>
    <add name="AdventureWorksLT2008Entities"
         connectionString="metadata=res://*/AdventureWorksModel.csdl|
  res://*/AdventureWorksModel.ssdl|res://*/AdventureWorksModel.msl;
  provider=System.Data.SqlClient;provider connection string=
  &quot;Data Source=.sqlexpress;Initial Catalog=AdventureWorksLT2008;
  Integrated Security=True;MultipleActiveResultSets=True&quot;"
         providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

This is a rather more complex connection string than the one we saw back in Example 14-1, because the Entity Framework needs three things in its connection string: information on where to find the EDM definition, the type of underlying database provider to use, and the connection string to pass to that underlying provider. This last part—an ordinary SQL Server connection string, enclosed in " character entities—is highlighted in Example 14-13 in bold.

The three URIs in the metadata section of the connectionString—the ones beginning with res://—point to the three parts of the EDM: the conceptual schema, the storage schema, and the mappings. Visual Studio extracts these from the .edmx file and embeds them as three XML resource streams in the compiled program. Without these, the EF wouldn’t know what the conceptual and storage schemas are supposed to look like, or how to map between them.

After the EDM metadata resources, you can see a provider property, which in Example 14-13 indicates that the underlying database connection is to be provided by the SQL Server client. The EF passes the provider connection string on to that provider.

You don’t have to use the App.config to configure the connection. The object context offers a constructor overload that accepts a connection string. The configuration file is useful—it’s where the object context’s no-parameters constructor we’ve been using in the examples gets its connection information from—but what if you want to let just the underlying database connection string be configurable, while keeping the parts of the connection string identifying the EDM resources fixed? Example 14-14 shows how you could achieve this. It retrieves the configured values for these two pieces and uses the EntityConnectionStringBuilder helper to combine this with the EDM resource locations, forming a complete EF connection string.

using System.Configuration;
using System.Data.EntityClient;

...


// Retrieve the connection string for the underlying database provider.
ConnectionStringSettings dbConnectionInfo =
    ConfigurationManager.ConnectionStrings["AdventureWorksSql"];

var csb = new EntityConnectionStringBuilder();
csb.Provider = dbConnectionInfo.ProviderName;
csb.ProviderConnectionString = dbConnectionInfo.ConnectionString;
csb.Metadata = "res://*/AdventureWorksModel.csdl|" +
    "res://*/AdventureWorksModel.ssdl|res://*/AdventureWorksModel.msl";


using (var dbContext = new AdventureWorksLT2008Entities(csb.ConnectionString))
{
   ...
}

This code uses the ConfigurationManager in the System.Configuration namespace, which provides a ConnectionStrings property. (This is in a part of the .NET Framework class library that’s not referenced by default in a .NET console application, so we need to add a reference to the System.Configuration component for this to work.) This provides access to any connection strings in your App.config file; it’s the same mechanism the EF uses to find its default connection string. Now that Example 14-14 is providing the EDM resources in code, our configuration file only needs the SQL Server part of the connection string, as shown in Example 14-15 (with a long line split across multiple lines to fit). So when the application is deployed, we have the flexibility to configure which database gets used, but we have removed any risk that such a configuration change might accidentally break the references to the EDM resources.

<configuration>
  <connectionStrings>
    <add name="AdventureWorksSql" providerName="System.Data.SqlClient"
         connectionString="Data Source=.sqlexpress;
                Initial Catalog=AdventureWorksLT2008;
                Integrated Security=True;MultipleActiveResultSets=True"
    />

  </connectionStrings>
</configuration>

Besides being able to change the connection information, what else can we do with the connection? We could choose to open the connection manually—we might want to verify that our code can successfully connect to the database. But in practice, we don’t usually do that—the EF will connect automatically when we need to. The main reason for connecting manually would be if you wanted to keep the connection open across multiple requests—if the EF opens a connection for you it will close it again. In any case, we need to be prepared for exceptions anytime we access the database—being able to connect successfully is no guarantee that someone won’t trip over a network cable at some point between us manually opening the connection and attempting to execute a query. So in practice, the connection string is often the only aspect of the connection we need to take control of.

So far, all of our examples have just fetched existing data from the database. Most real applications will also need to be able to add, change, and remove data. So as you’d expect, the Entity Framework supports the full range of so-called CRUD (Create, Read, Update, and Delete) operations. This involves the object context, because it is responsible for tracking changes and coordinating updates.

Updates—modifications to existing records—are pretty straightforward. Entities’ properties are modifiable, so you can simply assign new values. However, the EF does not attempt to update the database immediately. You might want to change multiple properties, in which case it would be inefficient to make a request to the database for each property in turn, and that might not even work—integrity constraints in the database may mean that certain changes need to be made in concert. So the EF just remembers what changes you have made, and attempts to apply those changes back to the database only when you call the object context’s SaveChanges method. Example 14-16 does this. In fact, most of the code here just fetches a specific entity—the most recent order of a particular customer—and only the last couple of statements modify that order.

using (var dbContext = new AdventureWorksLT2008Entities())
{
    var orderQuery = from customer in dbContext.Customers
                     where customer.CustomerID == 29531
                     from order in customer.SalesOrderHeaders
                     orderby order.OrderDate descending
                     select order;

    SalesOrderHeader latestOrder = orderQuery.First();

    latestOrder.Comment = "Call customer when goods ready to ship";

    dbContext.SaveChanges();
}

To add a brand-new entity, you need to create a new entity object of the corresponding class, and then tell the object context you’ve done so—for each entity type, the context provides a corresponding method for adding entities. In our example, the context has AddToCustomers, AddToSalesOrderHeaders, and AddToSalesOrderDetails methods. You will need to make sure you satisfy the database’s constraints, which means that the code in Example 14-17 will not be enough.

SalesOrderDetail detail = new SalesOrderDetail();
dbContext.AddToSalesOrderDetails(detail);

// Will throw an exception!
dbContext.SaveChanges();

The Entity Framework will throw an UpdateException when Example 14-17 calls SaveChanges because the entity is missing all sorts of information. The example database’s schema includes a number of integrity constraints, and will refuse to allow a new row to be added to the SalesOrderDetail table unless it meets all the requirements. Example 14-18 sets the bare minimum number of properties to keep the database happy. (This is probably not good enough for real code, though—we’ve not specified any price information, and the numeric price fields will have default values of 0; while this doesn’t upset the database, it might not please the accountants.)

// ...where latestOrder is a SalesOrderHeader fetched with code like
// that in Example 14-16.

SalesOrderDetail detail = new SalesOrderDetail();
detail.SalesOrderHeader = latestOrder;
detail.ModifiedDate = DateTime.Now;
detail.OrderQty = 1;
detail.ProductID = 680;     // HL Road Frame - Black, 58

dbContext.AddToSalesOrderDetails(detail);

dbContext.SaveChanges();

Several of the constraints involve relationships. A SalesOrderDetail row must be related to a particular row in the Product table, because that’s how we know what products the customer has ordered. We’ve not defined an entity type corresponding to the Product table, so Example 14-18 just plugs in the relevant foreign key value directly.

The database also requires that each SalesOrderDetail row be related to exactly one SalesOrderHeader row—remember that this was one of the one-to-many relationships we saw earlier. (The header has a multiplicity of one, and the detail has a multiplicity of many.) The constraint in the database requires the SalesOrderID foreign key column in each SalesOrderDetail row to correspond to the key for an existing SalesOrderHeader row. But unlike the ProductID column, we don’t set the corresponding property directly on the entity. Instead, the second line of Example 14-18 sets the new entity’s SalesOrderHeader property, which as you may recall is a navigation property.

When adding new entities that must be related to other entities, you normally indicate the relationships with the corresponding navigation properties. In this example, you could add the new SalesOrderDetail object to a SalesOrderHeader object’s SalesOrderDetails navigation property—since a header may have many related details, the property is a collection and offers an Add method. Or you can work with the other end of the relationship as Example 14-18 does. This is the usual way to deal with the relationships of a newly created entity—setting foreign key properties directly as we did for the other relationships here is somewhat unusual. We did that only because our EDM does not include all of the relevant entities—we represent only three of the tables because a complete model for this particular example would have been too big to fit legibly onto a single page. There may also be situations where you know that in your particular application, the key values required will never change, and you might choose to cache those key values to avoid the overhead of involving additional entities.

We’ve seen how to update existing data and add new data. This leaves deletion. It’s pretty straightforward: if you have an entity object, you can pass it to the context’s DeleteObject method, and the next time you call SaveChanges, the EF will attempt to delete the relevant row, as shown in Example 14-19.

dbContext.DeleteObject(detail);

dbContext.SaveChanges();

As with any kind of change to the database, this will succeed only if it does not violate any of the database’s integrity constraints. For example, deleting an entity at the one end of a one-to-many relationship may fail if the database contains one or more rows at the many end that are related to the item you’re trying to delete. (Alternatively, the database might automatically delete all related items—SQL Server allows a constraint to require cascading deletes. This takes a different approach to enforcing the constraint—rather than rejecting the attempt to delete the parent item, it deletes all the children automatically.)

Example 14-18 adds new information that relates to information already in the database—it adds a new detail to an existing order. This is a very common thing to do, but it raises a challenge: what if code elsewhere in the system was working on the same data? Perhaps some other computer has deleted the order you were trying to add detail for. The EF supports a couple of common ways of managing this sort of hazard: transactions and optimistic concurrency.

Transactions are an extremely useful mechanism for dealing with concurrency hazards efficiently, while keeping data access code reasonably simple. Transactions provide the illusion that each individual database client has exclusive access to the entire database for as long as it needs to do a particular job—it has to be an illusion because if clients really took it in turns, scalability would be severely limited. So transactions perform the neat trick of letting work proceed in parallel except for when that would cause a problem—as long as all the transactions currently in progress are working on independent data they can all proceed simultaneously, and clients have to wait their turn only if they’re trying to use data already involved (directly, or indirectly) in some other transaction in progress.^[38]

The classic example of the kind of problem transactions are designed to avoid is that of updating the balance of a bank account. Consider what needs to happen to your account when you withdraw money from an ATM—the bank will want to make sure that your account is debited with the amount of money withdrawn. This will involve subtracting that amount from the current balance, so there will be at least two operations: discovering the current balance, and then updating it to the new value. (Actually it’ll be a whole lot more complex than that—there will be withdrawal limit checks, fraud detection, audit trails, and more. But the simplified example is enough to illustrate how transactions can be useful.) But what happens if some other transaction occurs at the same time? Maybe you happen to be making a withdrawal at the same time as the bank processes an electronic transfer of funds.

If that happens, a problem can arise. Suppose the ATM transaction and the electronic transfer both read the current balance—perhaps they both discover a balance of $1,234. Next, if the transfer is moving $1,000 from your account to somewhere else, it will write back a new balance of $234—the original balance minus the amount just deducted. But there’s the ATM transfer—suppose you withdraw $200. It will write back a new balance of $1,034. You just withdrew $200 and paid $1,000 to another account, but your account only has $200 less in it than before rather than $1,200—that’s great for you, but your bank will be less happy. (In fact, your bank probably has all sorts of checks and balances to try to minimize opportunities such as this for money to magically come into existence. So they’d probably notice such an error even if they weren’t using transactions.) In fact, neither you nor your bank really wants this to happen, not least because it’s easy enough to imagine similar examples where you lose money.

This problem of concurrent changes to shared data crops up in all sorts of forms. You don’t even need to be modifying data to observe a problem: code that only ever reads can still see weird results. For example, you might want to count your money, in which case looking at the balances of all your accounts would be necessary—that’s a read-only operation. But what if some other code was in the middle of transferring money between two of your accounts? Your read-only code could be messed up by other code modifying the data.

A simple way to avoid this is to do one thing at a time—as long as each task completes before the next begins, you’ll never see this sort of problem. But that turns out to be impractical if you’re dealing with a large volume of work. And that’s why we have transactions—they are designed to make it look like things are happening one task at a time, but under the covers they allow tasks to proceed concurrently as long as they’re working on unrelated information. So with transactions, the fact that some other bank customer is in the process of performing a funds transfer will not stop you from using an ATM. But if a transfer is taking place on one of your accounts at the same time that you are trying to withdraw money, transactions would ensure that these two operations take it in turns.

So code that uses transactions effectively gets exclusive access to whatever data it is working with right now, without slowing down anything it’s not using. This means you get the best of both worlds: you can write code as though it’s the only code running right now, but you get good throughput.

How do we exploit transactions in C#? Example 14-20 shows the simplest approach: if you create a TransactionScope object, the EF will automatically enlist any database operations in the same transaction. The TransactionScope class is defined in the System.Transactions namespace in the System.Transactions DLL (another class library DLL for which we need to add a reference, as it’s not in the default set).

using (var dbContext = new AdventureWorksLT2008Entities())
{
    using (var txScope = new TransactionScope())
    {
        var customersWithOrders = from cust in dbContext.Customers
                                  where cust.SalesOrderHeaders.Count > 0
                                  select cust;

        foreach (var customer in customersWithOrders)
        {
            Console.WriteLine("Customer {0} has {1} orders",
                customer.CustomerID, customer.SalesOrderHeaders.Count);
        }

        txScope.Complete();
    }
}

For as long as the TransactionScope is active (i.e., until it is disposed at the end of the using block), all the requests to the database this code makes will be part of the same transaction, and so the results should be consistent—any other database client that tries to modify the state we’re looking at will be made to wait (or we’ll be made to wait for them) in order to guarantee consistency. The call to Complete at the end indicates that we have finished all the work in the transaction, and are happy for it to commit—without this, the transaction would be aborted at the end of the scope’s using block. For a transaction that modifies data, failure to call Complete will lose any changes. Since the transaction in Example 14-20 only reads data, this might not cause any visible problems, but it’s difficult to be certain. If a TransactionScope was already active on this thread (e.g., a function farther up the call stack started one) our TransactionScope could join in with the same transaction, at which point failure to call Complete on our scope would end up aborting the whole thing, possibly losing data. The documentation recommends calling Complete for all transactions except those you want to abort, so it’s a good practice always to call it.

TransactionScope represents an implicit transaction—any data access performed inside its using block will automatically be enlisted on the transaction. That’s why Example 14-20 never appears to use the TransactionScope it creates—it’s enough for it to exist. (The transaction system keeps track of which threads have active implicit transactions.) You can also work with transactions explicitly—the object context provides a Connection property, which in turn offers explicit BeginTransaction and EnlistTransaction methods. You can use these in advanced scenarios where you might need to control database-specific aspects of the transaction that an implicit transaction cannot reach.

Besides enabling isolation of multiple concurrent operations, transactions provide another very useful property: atomicity. This means that the operations within a single transaction succeed or fail as one: all succeed, or none of them succeed—a transaction is indivisible in that it cannot complete partially. The database stores updates performed within a transaction provisionally until the transaction completes—if it succeeds, the updates are permanently committed, but if it fails, they are rolled back and it’s as though the updates never occurred. The EF uses transactions automatically when you call SaveChanges—if you have not supplied a transaction, it will create one just to write the updates. (If you have supplied one, it’ll just use yours.) This means that SaveChanges will always either succeed completely, or have no effect at all, whether or not you provide a transaction.

Transactions are not the only way to solve problems of concurrent access to shared data. They are bad at handling long-running operations. For example, consider a system for booking seats on a plane or in a theater. End users want to see what seats are available, and will then take some time—minutes probably—to decide what to do. It would be a terrible idea to use a transaction to handle this sort of scenario, because you’d effectively have to lock out all other users looking to book into the same flight or show until the current user makes a decision. (It would have this effect because in order to show available seats, the transaction would have had to inspect the state of every seat, and could potentially change the state of any one of those seats. So all those seats are, in effect, owned by that transaction until it’s done.)

Let’s just think that through. What if every person who flies on a particular flight takes two minutes to make all the necessary decisions to complete his booking? (Hours of queuing in airports and observing fellow passengers lead us to suspect that this is a hopelessly optimistic estimate. If you know of an airline whose passengers are that competent, please let us know—we’d like to spend less time queuing.) The Airbus A380 aircraft has FAA and EASA approval to carry 853 passengers, which suggests that even with our uncommonly decisive passengers, that’s still a total of more than 28 hours of decision making for each flight. That sounds like it could be a problem for a daily flight.^[39] So there’s no practical way of avoiding having to tell the odd passenger that, sorry, in between showing him the seat map and choosing the seat, someone else got in there first. In other words, we are going to have to accept that sometimes data will change under our feet, and that we just have to deal with it when it happens. This requires a slightly different approach than transactions.

Optimistic concurrency describes an approach to concurrency where instead of enforcing isolation, which is how transactions usually work, we just make the cheerful assumption that nothing’s going to go wrong. And then, crucially, we verify that assumption just before making any changes.

For example, an airline booking system that shows a map of available seats in an aircraft on a web page would make the optimistic assumption that the seat the user selects will probably not be selected by any other user in between the moment at which the application showed the available seats and the point at which the user picks a seat. The advantage of making this assumption is that there’s no need for the system to lock anyone else out—any number of users can all be looking at the seat map at once, and they can all take as long as they like.

Occasionally, multiple users will pick the same seat at around the same time. Most of the time this won’t happen, but the occasional clash is inevitable. We just have to make sure we notice. So when the user gets back to us and says that he wants seat 7K, the application then has to go back to the database to see if that seat is in fact still free. If it is, the application’s optimism has been vindicated, and the booking can proceed. If not, we just have to apologize to the user (or chastise him for his slowness, depending on the prevailing attitude to customer service in your organization), show him an updated seat map so that he can see which seats have been claimed while he was dithering, and ask him to make a new choice. This will happen only a small fraction of the time, and so it turns out to be a reasonable solution to the problem—certainly better than a system that is incapable of taking enough bookings to fill the plane in the time available.

Sometimes optimistic concurrency is implemented in an application-specific way. The example just described relies on an understanding of what the various entities involved mean, and would require us to write code that explicitly performs the check described. But slightly more general solutions are available—they are typically less efficient, but they can require less code. The EF offers some of these ignorant-but-effective approaches to optimistic concurrency.

The default EF behavior seems, at a first glance, to be ignorant and broken—not only does it optimistically assume that nothing will go wrong, but it doesn’t even do anything to check that assumption. We might call this blind optimism—we don’t even get to discover when our optimism turned out to be unfounded. While that sounds bad, it’s actually the right thing to do if you’re using transactions—transactions enforce isolation and so additional checks would be a waste of time. But if you’re not using transactions, this default behavior is not good enough for code that wants to change or add data—you’ll risk compromising the integrity of your application’s state.

To get the EF to check that updates are likely to be sound, you can tell it to check that certain entity properties have not changed since the entity was populated from the database. For example, in the SalesOrderDetail entity, if you select the ModifiedDate property in the EDM designer, you could go to the Properties panel and set its Concurrency Mode to Fixed (its default being None). This will cause the EF to check that this particular column’s value is the same as it was when the entity was fetched whenever you update it. And as long as all the code that modifies this particular table remembers to update the ModifiedDate, you’ll be able to detect when things have changed.

If any of the columns with a Concurrency Mode of Fixed change between reading an entity’s value and attempting to update it, the EF will detect this when you call SaveChanges and will throw an OptimisticConcurrencyException, instead of completing the update.

How you deal with an optimistic concurrency failure is up to your application—you might simply be able to retry the work, or you may have to get the user involved. It will depend on the nature of the data you’re trying to update.

The object context provides a Refresh method that you can call to bring entities back into sync with the current state of the rows they represent in the database. You could call this after catching an OptimisticConcurrencyException as the first step in your code that recovers from a problem. (You’re not actually required to wait until you get a concurrency exception—you’re free to call Refresh at any time.) The first argument to Refresh tells it what you’d like to happen if the database and entity are out of sync. Passing RefreshMode.StoreWins tells the EF that you want the entity to reflect what’s currently in the database, even if that means discarding updates previously made in memory to the entity. Or you can pass RefreshMode.ClientWins, in which case any changes in the entity remain present in memory. The changes will not be written back to the database until you next call SaveChanges. So the significance of calling Refresh in ClientWins mode is that you have, in effect, acknowledged changes to the underlying database—if changes in the database were previously causing SaveChanges to throw an OptimisticConcurrencyException, calling SaveChanges again after the Refresh will not throw again (unless the database changes again in between the call to Refresh and the second SaveChanges).

If you ask the context object for the same entity twice, it will return you the same object both times—it remembers the identity of the entities it has returned. Even if you use different queries, it will not attempt to load fresh data for any entities already loaded unless you explicitly pass them to the Refresh method.

This raises the question of how long you should keep an object context around. The more entities you ask it for, the more objects it’ll hang on to. Even when your code has finished using a particular entity object, the .NET Framework’s garbage collector won’t be able to reclaim the memory it uses for as long as the object context remains alive, because the object context keeps hold of the entity in case it needs to return it again in a later query.

There are other lifetime issues to bear in mind. In some situations, an object context may hold database connections open. And also, if you have a long-lived object context, you may need to add calls to Refresh to ensure that you have fresh data, which you wouldn’t have to do with a newly created object context. So all the signs suggest that you don’t want to keep the object context around for too long.

How long is too long? In a web application, if you create an object context while handling a request (e.g., for a particular page) you would normally want to Dispose it before the end of that request—keeping an object context alive across multiple requests is typically a bad idea. In a Windows application (WPF or Windows Forms), it might make sense to keep an object context alive a little longer, because you might want to keep entities around while a form for editing the data in them is open. (If you want to apply updates, you normally use the same object context you used when fetching the entities in the first place, although it’s possible to detach an entity from one context and attach it later to a different one.) In general, though, a good rule of thumb is to keep the object context alive for no longer than is necessary.