Entity Framework 4.1: Bypassing EF query mapping (8)

This is part of a series of blog post about Entity Framework 4.1.  The past blog entries are:

• Basics (1)
• Override conventions (2)
• Deep Fetch vs Lazy Load (3)
• Complex Types (4)
• Many to Many Relationships (5)
• Optimistic Concurrency (6)
• Inheritance (7)

In this final article, I’ll cover some ways to bypass EF query mapping.

As all good framework, EF recognize it’s not perfect and won’t ever cover all the angles and it does support opening to the underlying Framework, ADO.NET, by allowing you to call the database directly.

There are three (3) APIs opening the hood to ad-hoc:

• DbContext.Database.ExecuteSqlCommand
• DbContext.Database.SqlQuery
• DbSet.SqlQuery

The first API isn’t very interesting, as it looks identical to its ADO.NET counterpart:

public int ExecuteSqlCommand(string sql, params object[] parameters);

The second one is more interesting:

public IEnumerable<TElement> SqlQuery<TElement>(string sql, params object[] parameters);

We can use this method to send SQL command (either a stored procedure call or ad-hoc SQL) directly to the database.  It delivers a little more than ADO.NET in that it’s able to convert the data-readers directly in entities.

TElement could be any class.  An important characteristic is that EF doesn’t track returned object, even if they are entity types.  This contrasts with the third API, part of DbSet, which tracks returned objects.

Let’s try DbContext.Database.SqlQuery:

public IEnumerable<SprocReport> GetEntityList()
{
    return Database.SqlQuery<SprocReport>("SELECT LegalEntityBaseID, EntityName FROM dbo.LegalEntity");
}

It’s good practice to encapsulate those calls inside the DbContext derived class.  Here the class we use is SprocReport:

public class SprocReport
{
    public int LegalEntityBaseID { get; set; }
    public string EntityName { get; set; }
}

This class isn’t an entity and its property are mapped directly:  you have no control on the mapping.  Even if you use a complex type and override some mapping, those overrides won’t be honoured.

Now if you use DbSet.SqlQuery, the returned entities will be tracked by the change tracker and therefore, if you make modifications to those entities, they will be caught when DbContext.SaveChanges is called.  On the other hand, no column mapping overrides would be honoured there either.

Another way we could bypass EF query-mapping management would be by querying the store using Entity SQL.  Remember the Entity Framework maps an entity model to a physical model and LINQ queries are transformed in queries to the entity model (using eSQL syntax) before being transformed to native queries to the underlying data store (e.g. TSQL).

For instance, we could create an entity set without exposing it:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<SimpleEntry>().HasEntitySetName("MyEntry");
    modelBuilder.Entity<SimpleEntry>().ToTable("MyEntry", "man");
    modelBuilder.Entity<SimpleEntry>()
        .Property(s => s.ID)
        .HasColumnName("SimpleEntryID");
    modelBuilder.Entity<SimpleEntry>()
        .Property(s => s.Name)
        .HasColumnName("SimpleEntryName");

    }

We could then expose a query:

public IEnumerable<SimpleEntry> GetSimpleEntries()
{
    IObjectContextAdapter adapter = this;
    var entries = adapter.ObjectContext.CreateQuery<SimpleEntry>("SELECT VALUE MyEntry FROM MyEntry");

    return entries;
}

Here we fetch the underlying ObjectContext in order to query it.  There advantage of this approach versus querying SQL directly is that we can query the resulting query further using LINQ and the final query sent to SQL will be the combined query.  Therefore we can expose a query returning everything and simply query it with LINQ afterwards to obtain efficient query without ever materializing the entire table on the consumer side.

To convince ourselves that what I just said is true, let’s try it:

public IEnumerable<SimpleEntry> GetSimpleEntries()
{
    IObjectContextAdapter adapter = this;
    var entries = adapter.ObjectContext.CreateQuery<SimpleEntry>("SELECT VALUE MyEntry FROM MyEntry");
    var final = from e in entries
                where e.Name == "Mark"
                select e;
    var f = (System.Data.Objects.ObjectQuery<SimpleEntry>)final;
    var s = f.ToTraceString();

    return entries;
}

If you debug this and look at the value of s, you’ll obtain:

SELECT
[Extent1].[SimpleEntryID] AS [SimpleEntryID],
[Extent1].[SimpleEntryName] AS [SimpleEntryName]
FROM [man].[MyEntry] AS [Extent1]
WHERE N’Mark’ = [Extent1].[SimpleEntryName]

This is your typical EF rendered TSQL but you can notice that the filter applied by the LINQ query got into the SQL query.

Now if you want to intercept INSERT, UPDATE & DELETE on those tracked entities, you’re a bit on your own.  You could override the DbContext.SaveChanges, pick entities with altered states, perform you db-logic to save the changes and switch the state of those entities back to Unmodified before calling base.SaveChanges.  It’ll work but it’s start to be a bit on the stunt side.