Microsoft 70-487: Query and manipulate data by using the Entity Framework

Exam Objectives

Query, update, and delete data by using DbContext; build a query that uses deferred execution; implement lazy loading and eager loading; create and run compiled queries; query data by using Entity SQL; perform asynchronous operations using Entity Framework; map a stored procedure

Quick Overview of Training Materials

Exam Ref 70-487 - Chapter 2.1
[MSDN] Query Execution
[MSDN] Entity Framework Loading Related Entities
[MSDN] Compiled Queries (LINQ to Entities)
[MSDN] How to: Execute a Parameterized Entity SQL Query Using EntityCommand
[MSDN] Write SQL Queries for Entities
[MSDN] Async Query & Save (EF6 onwards)
[MSDN] Stored Procedures in the Entity Framework (2011), (video)
[MSDN] Code First Insert/Update/Delete Stored Procedures
[StackOverflow] using stored procedure in entity framework
[StackOverflow] How to call Stored Procedure in Entity Framework 6 (Code-First)?

PluralSight Courses:
Getting Started With EF6, EF in the Enterprise

DbContext Basics

The DbContext class is the CLR or "object" side of the data model representation.  The DbContext is made up of DbSet objects, which are collections of the entities we have defined to represent the data from the database.  Julie Lerman's PluralSight courses on Entity Framework do a great job of walking through many different scenarios, and my code example will lean heavily on what was covered in these classes.

Here is what a context class will looks like:

public class ReturnsContext : DbContext
{
  public DbSet<CustomerReference> Customers { get;set; }
  public DbSet<Order> Orders { get; set; }
  public DbSet<Return> Returns { get; set; }
 
  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
    modelBuilder.Configurations.Add(new LineItemMap());
    modelBuilder.Entity<Shipment>();
    modelBuilder.Ignore<Customer>();
    modelBuilder.Ignore<Category>();
  }
}

This DbContext represents "Returns", and contains sets of Customers, Orders, and Returns.  Entity Framework will map objects of these classes to tables in the database.  What is less obvious, however, is that not only will these objects be mapped to the database, but the graph of other objects that there classes rely on will also be mapped to the database.

Overriding methods of the DbContext class gives you a great deal of control over how EF builds the models and stores information to the database.  Overriding OnModelCreating allows us to use the fluent configuration API.  Here, we are adding a "LineItemMap", which just specifies that the "LineTotal" field is ignored and not mapped to the database.  "Customer" and "Category" are also ignored.  Finally, we are adding "Shipment" to the model even though it is not directly connected to the other DbSet object graphs.

Overriding "SaveChanges" allows for additional logic to be executed when entities are saved.  Julie's code samples for her "Getting Started" course did this to automatically set the "DateModified" and "DateCreated" properties on the entities in her context:

public override int SaveChanges() {
  foreach (var history in this.ChangeTracker.Entries()
    .Where(e => e.Entity is IModificationHistory && (e.State == EntityState.Added ||
            e.State == EntityState.Modified))
     .Select(e => e.Entity as IModificationHistory)
    ) {
    history.DateModified = DateTime.Now;
    if (history.DateCreated == DateTime.MinValue) {
      history.DateCreated = DateTime.Now;
    }
  }
  int result = base.SaveChanges();
  // ... additional logic

Using DbContext for CRUD

For the purposes of demonstrating CRUD operations, I'm going to use a simpler DbContext from the MVC Music Store demo app.  This one doesn't include any fluent configuration or method overrides (which is why it didn't make the cut above lol).  But it's perfect for demonstrating the basics:

public class MusicStoreEntities : DbContext
{
    public DbSet<Album> Albums { get; set; }
    public DbSet<Genre> Genres { get; set; }
    public DbSet<Artist> Artists { get; set; }
    public DbSet<Cart> Carts { get; set; }
    public DbSet<Order> Orders { get; set; }
    public DbSet<OrderDetail> OrderDetails { get; set; }
}

I'll throw in a short snipped of code that includes all the relevant operations for CRUD and then discuss them a little bit:

//Query
int key = 1; //whatever the key is
var artist = db.Artists.Find(key);
var artist1 = db.Artists
    .Where(a => a.Name == "deadmau5").SingleOrDefault();
var allartists = db.Artists.ToList();
var artist2 = (from a in db.Artists
               where a.Name == "Skrillex"
               select artist).FirstOrDefault();
              
 
//Create
//Calling "Add" on the DbSet will create an "Insert" command for 
//the entity and all the entities in its graph.  If any of them are 
//existing entity, this will also mark them as "added" and EF will try
//to insert them into the database again
var album = new Album(){ /* intialize here */ };
db.Albums.Add(album);
db.SaveChanges();
 
//alternative for when you only want to add the root object and not all the 
//connected entities.  Entry() will automatically attach the entity passed
//to it if that entity isn't already being tracked by the context
db.Entry(album).State = EntityState.Added;
db.SaveChanges();
 
 
//Update
//if album is already attached to db context,
//then changes to the entity will automatically be tracked
album.Price = (decimal)0.02;
album.Title = "New Title";
db.SaveChanges(); 
 
//if the album was not already being tracked by context 
//(like if it was created by model binding from a web request)
//it needs to be attached to the context before it will be saved
db.Entry(album).State = EntityState.Modified;
album.Price = (decimal)0.02;
album.Title = "New Title";
db.SaveChanges(); 
 
//Delete
//Like create and update, this can be done two ways depending on your scenario
db.Albums.Remove(album);
db.SaveChanges();
 
//or
db.Entry(album).State = EntityState.Deleted;
db.SaveChanges();

Querying the database is done using LINQ.  This can be done using extension methods (as shown above for "artist1") or with a LINQ expression (as for "artist2").  If you have the key for the entity you need, DbSet has a "Find" method that will get the entity by its key.

For modifications to the database, Julie Lerman describes two different cases:  the connected case, and the disconnected case.  In the connected case (as with a desktop app), the application uses a single context for the life of the application, and there is always a link between the in-memory entities and the context.  In this case, these operations can be accomplished without too much concern for whether the object is "attached" to the context.

In the disconnected case, however, we need to be mindful of whether an entity is attached to the context we are working with, since each HTTP request will get its own context (at least this seems to be the norm, anyway...).  For operations like Delete and Update, that means that the entity will need to either be retrieved from the context first (say, using Find()), or the object representing the entity needs to be attached to the context so it's state will be tracked.  This state can then be changed using the Entry() method on the DbContext, and changed to Added, Modified, or Deleted as appropriate.

Deferred Execution

When querying the context with LINQ, the SQL command is not sent immediately to the database.  Instead, it is deferred until the query is enumerated by a command such as foreach, ToList(), or an aggregation such as Count().  Take this query method:

using (var context = new NinjaContext())
{
    context.Database.Log = Console.WriteLine;
    var ninjas = context.Ninjas
        .Where(n => n.DateOfBirth >= new DateTime(1984, 1, 1)).OrderBy(n => n.Name);
 
    Console.Out.WriteLine("Query created, not executed yet...");
 
    foreach (var ninja in ninjas) {
        Console.Out.WriteLine(ninja.Name);
    }
 
}

This is taking advantage of EF6 logging functionality that makes it easy to stream logs to standard output.  Running the console app yields these results:

The mid-method Console.Out.WriteLine is executed before the query is executed by Entity Framework, which is why the line "Query created, not executed yet..." appears first in the console.

One consequence of deferred execution is that queries can be combined and extended.  So if paging or ordering methods are called (Take(), Skip(), OrderBy()), these are integrated into the query sent to Sql server by Entity Framework.  Following is a modification to the above code to add some additional paging and ordering to the above code, where you can see the results on the final Sql query sent to the database:

using (var context = new NinjaContext())
{
    context.Database.Log = Console.WriteLine;
    var ninjas = context.Ninjas
        .Where(n => n.DateOfBirth >= new DateTime(1984, 1, 1)).OrderBy(n => n.Name);
 
    Console.Out.WriteLine("Query created, not executed yet...");
 
    ninjas = ninjas.Skip(1).Take(2).OrderBy(n => n.Name);
 
    foreach (var ninja in ninjas) {
        Console.Out.WriteLine(ninja.Name);
    }
 
}

Eager and Lazy Loading

By default, when you query the DbContext, you only get back the entities you queried against.  You don't get back the entire object graph with all the related entities.  There are two ways related models can be loaded:

Eager loading is executed when the query for the root object is executed.  With eager loading, you get back all the related entities at the time of the query, regardless of whether they are ever accessed.  Contrast this with lazy loading, where related entities are only loaded when they are accessed in one way or another (similar to what happens with deferred query execution, only with related entities).

To both of these methods in action, I think it is illustrative to demonstrate that EF does not, in fact, return object graphs by default.  This will turn again to the Ninja example code.  This code is requesting one Ninja object, which has two related Equipment entities.  However, since neither lazy nor eager loading was used to load the related entities, the equipment collection is empty:

This sample is part of the demo for "Explicit" loading.  Explicit loading is one way of doing lazy loading.  Instead of the framework determining when to load related data, this is accomplished in the code by calling the Load() method:

private static void SimpleNinjaGraphQueryExplicit()
{
    using (var context = new NinjaContext())
    {
        context.Database.Log = Console.WriteLine;
 
        var ninja = context.Ninjas
            .FirstOrDefault(n => n.Name.StartsWith("Kacy"));
 
        Console.Out.WriteLine("Ninja Retrieved");
 
        context.Entry(ninja).Collection(n => n.EquipmentOwned).Load();
    }
}

Now, the first time I tried to grab my screenshot, it actually failed to act the way I expected.  Instead of showing me an empty collection, it showed a Count of 2.  The reason is that I had the Ninja class set up for implicit lazy loading already, and when I inspected the ninja variable, the EquipmentOwned variable was enumerated, thus triggering the loading of the related entities.  This was easy to see in the console window, since log output was streaming to stdout.  Enabling implicit lazy loading in EF is just a matter of marking the navigation property for the related entity as virtual.  EF will override the properties to add the necessary logic, there is literally nothing else for us to do:

public class Ninja
{
    public Ninja()
    {
        EquipmentOwned = new List<NinjaEquipment>();
    }
 
    public int Id { get; set; }
    public string Name { get; set; }
    public bool ServedInOniwaban { get; set; }
    public Clan Clan { get; set; }
    public int ClanId { get; set; }
    //marking with virtual enables lazy loading
    public virtual List<NinjaEquipment> EquipmentOwned { get; set; }
    public DateTime DateOfBirth { get; set; }
}

Eager loading is done by using the Include() method on the query itself.

private static void SimpleNinjaGraphQueryEager()
{
    using (var context = new NinjaContext())
    {
        context.Database.Log = Console.WriteLine;
 
        var ninja = context.Ninjas.Include(n => n.EquipmentOwned)
            .FirstOrDefault(n => n.Name.StartsWith("Kacy"));
    }
}

When done this way, the login to grab the related objects is actually included in the generated SQL query:

There are trade offs involved with the different approaches to loading related entities.  While lazy loading  can be more efficient if you aren't going to need all of the related entity data for a collection of results, each related entity that is loaded will trigger a round trip to the database.  J. Lerman points out that this can cause a severe slowdown in performance if a control like a grid view is lazy loading individual cell data because it wasn't all included in the main query.  The MSDN article points out that enabling lazy loading with serialization can also have unintended performance impact, as the serialization process will enumerate all the properties recursively.

Create and Run Compiled Queries

Ugh, this part was a pain in the ass, because the overall API and usage patterns with EF have moved on (at least if this StackOverflow thread is to be believed).  Compiled queries require an ObjectContext, but all of the generated entity contexts in new version of EF use DbContext, which isn't supported (and allegedly doesn't need compiled queries because it already caches the query anyway).

I still wanted to demonstrate the concept, so I burrowed the idea presented in the MSDN article on compiled queries and applied it to the Ninja example code.  The first thing I needed was an ObjectContext connected to the same database as my regular context, so I created a new project, and in that project created a new "ADO.NET Entity Data Model", selected "EF Designer from database", pointed it at the right db, and voila, I had the edmx file that was 90% there.  All that was required was to tweak the context class.  The context needs to target the same version of EF that is installed in the console application (I tried mixing 5 and 6 and it took a dump...).  Here is the modified context:

namespace NinjaDomain.OldStyleContext
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    using System.Data.Entity.Core.Objects;
 
    
    public partial class NinjaObjectContext : ObjectContext //changed from DbContext
    {
        public NinjaObjectContext()
            : base("name=NinjaObjectContext")
        {
            //initialize ObjectSets
            this.Clans = this.CreateObjectSet<Clan>();
            this.NinjaEquipments = this.CreateObjectSet<NinjaEquipment>();
            this.Ninjas = this.CreateObjectSet<Ninja>();
        }
    
        //Object Context doesn't use this
        //protected override void OnModelCreating(DbModelBuilder modelBuilder)
        //{
        //    throw new UnintentionalCodeFirstException();
        //}
 
        //Use ObjectSet instead of DbSet
        public ObjectSet<Clan> Clans { get; set; }
        public ObjectSet<NinjaEquipment> NinjaEquipments { get; set; }
        public ObjectSet<Ninja> Ninjas { get; set; }
    }
}

[UPDATE] I found out later that you can get an ObjectContext from you DbContext through the use of an adapter... whoops. I demonstrate this in the next section, though I'm not sure it would have helped me here... [END UPDATE]

Once the preliminary step of creating an ObjectContext is done, we can construct a compiled query.  In my ConsoleApp, I've created the compiled query and a static method to use it, which will be called from Main():

static readonly Func<nob.NinjaObjectContext, DateTime, IQueryable<nob.Ninja>> s_compiledNinjaQuery =
    CompiledQuery.Compile<nob.NinjaObjectContext, DateTime, IQueryable<nob.Ninja>>(
            (ctx, date) => from ninja in ctx.Ninjas
                            where ninja.DateOfBirth < date
                            select ninja);
 
private static void SimpleNinjaCompiledQuery() 
{
    using (var context = new nob.NinjaObjectContext())
    {
        var oldninjas = s_compiledNinjaQuery.Invoke(context, new DateTime(1982, 1, 1));
 
        foreach (nob.Ninja ninja in oldninjas) 
        {
            Console.Out.WriteLine(ninja.Name);
        }
    }
}

In my using statements, I had to include the "old" style data context with an alias because the namespace was clashing with the POCO's defined for the code first demos.  Also notice that the logging isn't being pushed to the console out, since this is a feature only available on newer versions of the DbContext.  Because nothing else is output, the console is pretty boring for this one:

Eyup... that's it...

Now, supposedly compiled queries are supposed to be faster, but I wanted to see for myself, so I wrote a quick test function and ran it:

using (var context = new nob.NinjaObjectContext())
{
    var start = DateTime.Now;
    for (var i = 0; i < 10000; i++)
    {
        var result1 = s_compiledNinjaQuery.Invoke(context, new DateTime(1982, 1, 1));
    }
    double ms = (DateTime.Now - start).TotalMilliseconds;
    Console.Out.WriteLine("Compiled query : " + ms.ToString() + "ms");
}
 
using (var ctx = new nob.NinjaObjectContext())
{
    var start = DateTime.Now;
    var date = new DateTime(1982, 1, 1);
    for (var i = 0; i < 10000; i++)
    {
        var result2 = from ninja in ctx.Ninjas
                      where ninja.DateOfBirth < date
                      select ninja;
    }
    double ms = (DateTime.Now - start).TotalMilliseconds;
    Console.Out.WriteLine("Non-Compiled query : " + ms.ToString() + "ms");
}

While it certainly isn't going to set the world on fire, the compiled version does enjoy about a 33% performance gain.  Out of curiosity, I added a timed call to the DbContext version in the mix as well, and it was slower than either of the ObjectContext versions!  The code is exactly the same as "Non-Compiled" version above, just substituting a NinjaContext for the nob.NinjaObjectContext.

Queries with Entity SQL

According to Microsoft:

Entity SQL is a SQL-like language that enables you to query conceptual models in the Entity Framework. Conceptual models represent data as entities and relationships, and Entity SQL allows you to query those entities and relationships in a format that is familiar to those who have used SQL. [MSDN source]

Basically, Entity SQL lets you write pseudo-SQL that is translated to a provider specific query that is run against the database, similar to what happens for Linq queries.  I think it's important that this is not confused with the ability to run raw sql against the database, which is possible using the DbSet.SqlQuery() method.

There are a couple ways to execute Entity SQL.  The first is very similar to how queries are executed in basic ADO.NET:  a connection is opened to the database using the EF connection string, an EntityCommand (a subtype of DbCommand) is created, the text of that command is set to the Entity SQL we wish to execute, parameters are added, and an EntityReader is executed.  Like other DataReaders, this is a forward-only reader, and it does not hydrate strongly typed objects.

To get a strongly typed result set, you have to create an Object Context.  It is possible to do this using an Object Context adapter (which I've demonstrated in the below code).  This has the added benefit of allowing the logging to work, whoohoo.  The Object Context can create a query which takes the Entity SQL to be executed and an optional array of Object Parameters.

Below I demonstrate the raw SQL query, the DataReader (which has no way to add logging that I could see...), and the Object Query.  The Exam Ref and Stack Overflow both seem to think that using Entity SQL in practice is pretty rare, but it's in the exam objectives, so what can a body do, ey?  Notice how the raw SQL is executed exactly as is, while the Entity SQL is translated to a query that looks very much like the output we saw in the Linq examples.

private static void SimpleNinjaQueryRawSQL()
{
    using (var context = new NinjaContext())
    {
        context.Database.Log = Console.WriteLine;
 
        var ninjas = context.Ninjas
            .SqlQuery("SELECT * FROM dbo.Ninjas WHERE DateOfBirth > {0}", 
                new object[]{new DateTime(1984,1,1)}).ToList();
 
        foreach (Ninja ninja in ninjas)
        {
            Console.Out.WriteLine(ninja.Name + " " + ninja.DateOfBirth);
        }
    }
}
 
private static void SimpleNinjaQueryEntitySQL_Reader()
{
    using (EntityConnection conn = new EntityConnection("name=NinjaObjectContext"))
    {
        //context.Database.Log = Console.WriteLine;
        conn.Open();
        var cmd = conn.CreateCommand();
        cmd.CommandText = @"SELECT VALUE n 
                            FROM NinjaObjectContext.Ninjas AS n
                            WHERE n.DateOfBirth > @dob";
        cmd.Parameters.AddWithValue("dob", new DateTime(1984, 1, 1));
 
 
        using (EntityDataReader dr = cmd.ExecuteReader(
            System.Data.CommandBehavior.SequentialAccess)) 
        {
            while (dr.Read()) 
            {
                Console.Out.WriteLine(dr.GetString(1) + " " + dr.GetDateTime(4));
            }
        }
    }
}
 
private static void SimpleNinjaQueryEntitySQL_ObjCtxQuery()
{
    using (var context = new NinjaContext())
    {
        context.Database.Log = Console.WriteLine;
 
        var adapter = (IObjectContextAdapter)context;
        var objctx = adapter.ObjectContext;
        var param = new ObjectParameter("dob", new DateTime(1984, 1, 1));
        ObjectQuery<Ninja> ninjas = objctx.CreateQuery<Ninja>(
                @"SELECT VALUE n 
                  FROM NinjaContext.Ninjas AS n
                  WHERE n.DateOfBirth > @dob",
                new ObjectParameter[] { param });
 
        foreach (Ninja ninja in ninjas)
        {
            Console.Out.WriteLine(ninja.Name + " " + ninja.DateOfBirth);
        }
    }
}

Asynchronous Operations with EF

EF6 introduced asynchronous capabilities using the async and await keywords introduced in .NET 4.5.  Asynchronous saves are accomplished using the SaveChangesAsync() method on the DbContext, and async queries are done using the ToListAsync() extension method (part of the System.Data.Entity namespace).

There really isn't a lot to explain, conceptually, about these methods.  I adapted the demo code from the MSDN article to use the Ninja data context and models, and it was really pretty simple.  One "gotcha" I ran into, however, was caused by the NinjaContext class overriding SaveChanges().  The overridden method updates the DateCreated and DateModified fields automatically, but SaveChangesAsync() is a different method, so I got a couple errors (basically these weird exceptions that I tracked back to uninitialized DateTime fields) before I figured out that I needed another method override.

The code isn't that interesting (seriously, it's almost exactly the same as calling SaveChanges and ToList, just maybe with an "await"...).  But I will post a screenshot of the results.  You can see that program execution was not blocked by the asynchronous call until task.wait() was called in the second code block:

public static void SyncOperations() 
{
    PerformDatabaseOperations();
 
    Console.WriteLine();
    Console.WriteLine("Quote of the day");
    Console.WriteLine(" Don't worry about the world coming to an end today... ");
    Console.WriteLine(" It's already tomorrow in Australia.");
 
    Console.WriteLine();
    Console.WriteLine("Press any key to continue...");
    Console.ReadKey();
    Console.WriteLine();
}
 
public static void AsyncOperations() 
{
    var task = PerformDatabaseOperationsAsync();
 
    Console.WriteLine("Quote of the day");
    Console.WriteLine(" Don't worry about the world coming to an end today... ");
    Console.WriteLine(" It's already tomorrow in Australia.");
 
    task.Wait();
 
    Console.WriteLine();
    Console.WriteLine("Press any key to exit...");
    Console.ReadKey();
}

Map a Stored Procedure

Using stored procedures is supported in Entity Framework, and can be done a couple different ways. When using a model or database first approach, stored procedures can be selected from the Create or Update wizards.  Ultimately these are added to the context class.  Sprocs for insert, update, and delete can also be mapped to the individual models:

It is also possible to do this mapping when using Code First.  There is an MSDN article that goes into depth with this, and has a variety of examples.  Calling the MapToStoredProcedures() method will "opt in" to using stored procs for insert, update, and delete.  This method uses a number of convention based defaults that will preclude a lot of configuration if followed, but it is also possible to override these defaults.  This snippet of code shows what it might look like to configure the InsertNinja sproc in my demo code:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Types().
        Configure(c => c.Ignore("IsDirty"));
 
    modelBuilder
        .Entity<Ninja>()
        .MapToStoredProcedures(s =>
            s.Insert(i => i.HasName("InsertNinja")
             .Parameter(n => n.Name, "Name")
             .Parameter(n => n.ServedInOniwaban, "ServedInOniwaban")
             .Parameter(n => n.ClanId, "ClanId")
             .Parameter(n => n.DateOfBirth, "DateOfBirth")
             .Parameter(n => n.DateModified, "DateModified")
             .Parameter(n => n.DateCreated, "DateCreated")
        ));
 
    base.OnModelCreating(modelBuilder);
}

While the above can be used for insert, delete, and update sprocs, using query sprocs is actually much easier.  This can be done using the SqlQuery() method (like everything else, more than one way to do it too).  The results of the sproc will be mapped to the appropriate fields and a collection of strongly typed entities are returned.  Here are three ways to do basically the same query using an imagined "GetNinjasByX" sproc:

var parameters = new object[1];
//add all your parameters in here
context.Ninjas.SqlQuery("GetNinjasByX", parameters);
context.Database.SqlQuery<Ninja>("GetNinjasByX", parameters);
context.Database.SqlQuery(typeof(Ninja), "GetNinjasByX", parameters);