Microsoft 70-487: Query and manipulate data by using Data Provider for Entity Framework

Exam Objectives

Query and manipulate data by using Connection, DataReader, and Command from the System.Data.EntityClient namespace; perform synchronous and asynchronous operations; manage transactions (API); programmatically configure a Data Provider

Quick Overview of Training Materials

Exam Ref 70-487 - Chapter 2.2
[MSDN] EntityClient Provider for the Entity Framework
[MSDN] Entity Framework Code-Based Configuration (EF6 Onwards)

My post: Query and manipulate data by using ADO.NET

ADO.NET with a side of EF

On the surface, it would seem that most of the mechanics of using the EF flavor of the Connection, Command, Transaction, DataReader, etc., are exactly the same as described in detail in my post on ADO.NET.  I threw together the above example and thought myself very clever.  Here is the rub, though:  that connection isn't an EntityConnection, it's just a plain old SqlConnection.  So of course everything else is going to run just like ADO.NET.  When I stepped through the debugger and figured out that I wasn't actually dealing with the classes I thought I was, I dug a little deeper.

My first thought was to adapt the example from the book.  Once I worked past the sloppy spellchecking, I discovered that it needed some tweaking to work in my context, in particular the metadata gave me fits.  After some Googling I did eventually find a StackOverflow question or two that got me over the hump (at least so far as making the metadata behave).  A couple of frustrating hours of dicking around finally yielded results.  Lessons learned include:

• You can't load a DataTable from an EntityDataReader (because "GetSchemaTable" is unsupported).
• EntityCommand uses Entity SQL (ESQL), not standard SQL.
• ExecuteReader must be run with the SequentialAccess command behavior.
• You can't use the dictionary syntax directly on the DataReader, you have to pull out the DbDataRecord and use that (at least that's what I had to do to get mine to work...).

Every time I ran into one of these headaches my first thought was "Why in the hell would you do this??"  Building the connection string was ugly, the interfaces didn't behave the way you'd expect... it was just a serious pain.  But I suppose there are probably circumstances that require going this route.  Here is my final solution, basically comparing the Sql provider solution with the Entity provider solution:

    static void Main(string[] args)
    {
        //grab (Sql) connection from context
        using (var context = new NinjaContext())
        {
            using (var conn = context.Database.Connection)
            {
                var cmd = conn.CreateCommand();
                PrintResults(conn, cmd, "SELECT * FROM [dbo].[Ninjas]");
            }
        }

        //manually build connection
        SqlConnectionStringBuilder connBuilder = new SqlConnectionStringBuilder();
        connBuilder.InitialCatalog = "NinjaDomain.DataModel.NinjaContext";
        connBuilder.DataSource = ".\\SQLEXPRESS";
        connBuilder.IntegratedSecurity = true;

        EntityConnectionStringBuilder efBuilder = new EntityConnectionStringBuilder();
        efBuilder.Provider = "System.Data.SqlClient";
        efBuilder.ProviderConnectionString = connBuilder.ToString();
        efBuilder.Metadata = @"res://NinjaDomain.OldStyleContext/NinjaObjectContext.csdl|" +
                             @"res://NinjaDomain.OldStyleContext/NinjaObjectContext.ssdl|" +
                             @"res://NinjaDomain.OldStyleContext/NinjaObjectContext.msl";

        using (var conn = new EntityConnection(efBuilder.ToString()))
        {
            var cmd = conn.CreateCommand();
            PrintEntityResults(conn, cmd, 
                "SELECT Ninjas FROM NinjaObjectContext.Ninjas WHERE Ninjas.Id > 0");
        }

        Console.ReadLine();
    }

    //Sql Provider
    private static void PrintResults(DbConnection conn, DbCommand cmd, 
        string commandText)
    {
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = commandText;
        if (conn.State == ConnectionState.Closed)
            conn.Open();
        var dr = cmd.ExecuteReader();
        var ninjas = new DataTable();
        ninjas.Load(dr);

        foreach (DataRow row in ninjas.Rows)
        {
            Console.WriteLine(row["Id"] + ": " + row["Name"]);
        }
    }

    //Entity Provider
    private static void PrintEntityResults(DbConnection conn, DbCommand cmd, 
        string commandText)
    {
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = commandText;
        if (conn.State == ConnectionState.Closed)
            conn.Open();
        var dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess);

        while(dr.Read()){
            DbDataRecord record = (DbDataRecord)dr[0];
            Console.WriteLine(record.GetInt32(0) + ": " + record.GetString(1));
        }
    }

A bit of poking around on MSDN yielded a series of "How To" articles that would have been super helpful before I spent forever monkeying around with it.  Figures.

As far as asynchronous operations and transactions go, they work basically the same way as in the ADO.NET examples in my post on Objective 2.4. The ExecuteReader(), ExecuteNonQuery(), and ExecuteScalar() methods all have versions suffixed with Async.  Transactions can be done using a DbTransaction that can be started on the underlying Connection object (_ctx.Database.Connection.BeginTransaction() produces a transaction object, which you can call commit() or rollback() on as required).  Transaction scope doesn't change the way it works.  Just put it in a using and call scope.Complete() after you save changes on the context.

It feels a bit like cheating, but I'm just not convinced there is a lot of value to reproducing the exact same examples using a entity context instead of a database connection object.

Programmatic Configuration

One way of configuring the data provider programatically is what we did in the example above, using the two connection string builder classes to build up the necessary configuration for the underlying dataprovider and the conceptual model used by Entity Framework.  I found another example on StackOverflow that does essentially the same thing, but with the "AdventureWorks" database.  One drawback of this approach is that it requires you to have metadata to point the EntityConnectionStringBuilder at (it will throw an exception if metadata is not set), a shortcoming another answer on StackOverflow points out.

Configuring a code first data provider programmatically is done as described in the MSDN article on code-based configuration.  It seemed straightforward enough, so I threw together yet another project under the NinjaModule banner, this time with the goal of programmatically wiring up an EF context to the existing database.  I installed EF, deleted the config sections, added the couple lines from the tutorial (with the necessary tweaks to point at my database)... and nothing.  The code didn't explode, it just didn't work the way I expected.  EF created a new database connection following the typical naming convention (namespace.contextclass).

This is not what I wanted...

... this is what I wanted.

The solution was to create a connection factory class that just returns a connection using the exact connection string I passed in to the constructor, ignoring the "nameOrConnectionString" parameter that is passed in.  It feels a little hacky, but it does what I wanted it to do.  Here is the final sample code:

    class Program
    {
        static void Main(string[] args)
        {
            using (var ctx = new PrgNinjaContext())
            {
                var ninjas = ctx.Ninjas.ToList();

                foreach (var ninja in ninjas)
                {
                    Console.WriteLine(ninja.Id + ": " + ninja.Name);
                }
            }

            Console.Read();
        }
    }

    [DbConfigurationType(typeof(PrgNinjaDbConfiguration))]
    public class PrgNinjaContext : NinjaContext {}

    public class PrgNinjaDbConfiguration : DbConfiguration
    {
        public PrgNinjaDbConfiguration()
        {
            this.SetExecutionStrategy("System.Data.SqlClient", 
                () => new SqlAzureExecutionStrategy());

            SqlConnectionStringBuilder connBuilder = new SqlConnectionStringBuilder();
            connBuilder.InitialCatalog = "NinjaDomain.DataModel.NinjaContext";
            connBuilder.DataSource = ".\\SQLEXPRESS";
            connBuilder.IntegratedSecurity = true;

            DumbConnectionFactory scf = new DumbConnectionFactory(connBuilder.ToString());
            
            // This won't connect to the right database
            //SqlConnectionFactory scf = new SqlConnectionFactory(connBuilder.ToString());

            this.SetDefaultConnectionFactory(scf);
            this.SetProviderServices("System.Data.SqlClient", 
                SqlProviderServices.Instance);
        } 
    }

    public class DumbConnectionFactory : IDbConnectionFactory
    {
        public DumbConnectionFactory(string connString)
        {
            this.connString = connString;
        }

        public string connString { get; set; }

        public DbConnection CreateConnection(string nameOrConnectionString)
        {
            return new SqlConnection(connString);
        }
    }