Slide Decks and Demos from my ADO.Net Entity Framework Talk

Yesterday I gave my first talk in front of the Israeli Architects User Group. I had put a lot of effort into this talk (creating the presentation, creating the demos and stuff), and it was all worth it. It seemed that people had a nice time, the atmosphere was great, and a few people came to me after the presentation to tell that is was great. I got back home very satisfied and looking forward to my next talk…

I will go over the main issues of the talk here in this post for those who couldn't come or for those who had forgotten something:

Motivation
We pay a really high price both in development time and maintenance for the was our relational database is designed. When we talk about the conceptual model of our business applications we use terms like entities,  inheritance and relations that cannot be naturally expressed in relational databases. So the way we make up for it causes our code (Stored Procedures, Database View or ad hoc queries in our code) to contain "deep knowledge" of the logical schema of the database.

Introducing ADO.Net Entity Framework
The next version of ADO.Net contains a new framework called ADO.Net Entity Framework that helps us to separate the way out application views the data from the way this data is represented in the database. The framework contains a new data model for design time and a run-time engine that supports this model. The framework uses a technology called Client Views which is a mapping technology that maps the conceptual schema (the way our application views the data) to the logical schema. What is important about this technology (and explains its name) is that it is implemented and executed on the client side. This gives each application a separate "view" of the data instead of polluting a central database with application-specific views or stored procedure which are harder to maintain.

Entity Data Model
In order to model the data at a higher level of abstraction we now use a new data model called Entity Data Model. This model is a new Entity Relationship Model that allows us to use all the terms we couldn’t use when we modeled data in the relational database. Key concepts that are used in this model are Entities, that are instances of Entity Types. EntityTypes are a structured record with a key. Entities are grouped into EntitySets.

Mapping Provider
After creating a model, we want to query it and get result in terms of the new model. So as a natural evolution of ADO.Net, we use the Provider Model. But instead using SqlCommand and SqlConnection, we can now use MapCommand and MapConnection instead.
With the Map Provider we can replace ADO.Net SQL Commands that targets the logical schema of the database such as this one:

using (SqlConnection conn = new SqlConnection(Settings.Default.AWv3SqlConnectionString))
{
    conn.Open();
    SqlCommand cmd = conn.CreateCommand();
    cmd.CommandText = @"
        SELECT HireDate, FirstName
        FROM SalesPerson as sp
        INNER JOIN Employee as e ON sp.SalesPersonID = e.EmployeeID
        INNER JOIN Contact as c ON e.EmployeeID = c.ContactID
        WHERE HireDate < @Date";
        cmd.Parameters.AddWithValue("Date", date);

    DbDataReader r = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
    while (r.Read())
    {
        Console.WriteLine(" {0:d}\t{1}", r["HireDate"], r["FirstName"]);
    }
}

with another command that targets the conceptual schema. (The conceptual schema had an entity type called SalesPerson that was mapped to Contact, Employee and SalesPerson tables in the logical schema).

using (MapConnection conn = new MapConnection(Settings.Default.AWv3MappingConnectionString))
{
    conn.Open();
    MapCommand cmd = conn.CreateCommand();
    cmd.CommandText = @"
        SELECT sp.HireDate, sp.FirstName    
        FROM AdventureWorksModel.AdventureWorks.SalesPeople as sp
        WHERE sp.HireDate < @Date";
    cmd.Parameters.AddWithValue("Date", date);

    DbDataReader r = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
    while (r.Read())
    {
        Console.WriteLine(" {0:d}\t{1}", r["HireDate"], r["FirstName"]);
    }
}

Entity SQL
The motivation to create the new data model was to be able to use all the above terms that we couldn't use when working against the logical schema of the database. So in order to take advantage of those terms when we use the mapping provider, the SQL Statement we provide the MapCommand object is really a statement in a new query language called Entity SQL. This new language is a variation of SQL as we know, but let us query the model in terms of entities, relationships and inheritance. Since the Client Views run in the client side, the Entity SQL statement is processed and translated into actual SQL Statement on the client side. This behavior allows this query language to be store-independent. You can change the database you work against, but the Entity SQL statement stays the same.

Some examples to Entity SQL:

Selecting a full entity by using VALUE keyword:

 using (MapConnection conn = new MapConnection(Settings.Default.AWv3MappingConnectionString))
{
    conn.Open();
    MapCommand cmd = conn.CreateCommand();
    cmd.CommandText = @"
        SELECT VALUE sp    
        FROM AdventureWorksModel.AdventureWorks.SalesPeople as sp
        WHERE sp.HireDate < @Date";
    cmd.Parameters.AddWithValue("Date", date);

    DbDataReader r = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
    while (r.Read())
    {
        Console.WriteLine(" {0:d}\t{1}", r["HireDate"], r["FirstName"]);
    }
}

Getting entities according to thier type (using inheritance):

 using (MapConnection conn = new MapConnection(Settings.Default.AWv3MappingConnectionString))
{
    conn.Open();
    MapCommand cmd = conn.CreateCommand();
    cmd.CommandText = @"
        SELECT VALUE sp    
        FROM AdventureWorksModel.AdventureWorks.SalesPeople as sp
        AND so IS OF(AdventureWorksModel.StoreSalesOrder);
    cmd.Parameters.AddWithValue("Date", date);

    DbDataReader r = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
    while (r.Read())
    {
        IExtendedDataRecord er = (IExtendedDataRecord)r;
        Console.Write(er.DataRecordInfo.Metadata.Name);
        Console.WriteLine(" {0:d}\t{1}", r["HireDate"], r["FirstName"]);
    }
}

ADO.Net Object Layer
There is no doubt that using rows and columns is great for several scenarios such as Reporting or Business Intelligence. But, when you write heavy business logic against the results of your query, in most cases you would prefer working with objects.
Instead of building a new stack for Object Relational Mapping, the ADO.Net team has chosen to build a new object layer above the existing ADO.Net stack. As a matter of fact, your conceptual model should not be any different if you eventually want to get objects back or rows and columns.
While using the provider model on the mapping layer (using MapConnection and MapCommand), using the object layer is slightly different. This layer has a single entry point called ObjectContext. For example:

Using Object Context to query objects:

ObjectContext ctx = new ObjectContext(Settings.Default.AWv3MappingConnectionString, "AdventureWorksModel.AdventureWorks");

Query<SalesPerson> query = ctx.GetQuery<SalesPerson>(@"
    SELECT VALUE sp
    FROM AdventureWorksModel.AdventureWorks.SalesPeople as sp
    WHERE sp.HireDate < @Date",
    new QueryParameter("Date", date));

foreach (SalesPerson sp in query)
{
    Console.WriteLine(" {0:d}\t{1}", sp.HireDate, sp.FirstName);
}

Using Object Context to reflect changes back to the database:

ObjectContext ctx = new ObjectContext(Settings.Default.AWv3MappingConnectionString, "AdventureWorksModel.AdventureWorks");

Query<SalesPerson> query = ctx.GetQuery<SalesPerson>(@"
    SELECT VALUE sp
    FROM AdventureWorksModel.AdventureWorks.SalesPeople as sp
    WHERE sp.HireDate < @Date",
    new QueryParameter("Date", date));

foreach (SalesPerson sp in query)
{
    if (sp.FirstName == "Amy")
    {
        sp.Bonus += 10;
        sp.MiddleName = "Guy";
        sp.Title = "Senior Consultant";
    }
    Console.WriteLine(" {0:d}\t{1}", sp.HireDate, sp.FirstName);
}
ctx.SaveChanges();

Language Integrated Query and LINQ to Entities
In order to eliminate the impedance mismatch between programming languages and the way we communicate with the database (usually SQL), we can use LINQ. LINQ is a set of innovations for programming languages that allows us to integrate queries as part of the programming language we use to write our business logic. Using LINQ we can use intellisence, compilation checks, and pass typed parameters to our queries.

An example for LINQ query ober the conceptual model:

AdventureWorks db = new AdventureWorks();

var query = from sp in db.SalesPeople
where sp.HireDate.Year == 2006
select sp;

foreach (SalesPerson sp in query)
{
    Console.WriteLine(" {0:d}\t{1}", sp.HireDate, sp.FirstName);
}

I hope you also enjoyed the presentation and demos. You can download the slide deck from here, and the demos from here.

Using the Demos:
Before you download and start playing with the demos, you should install:
LINQ May CTP 
ADO.Net vNext August CTP
Entity Data Model Designer CTP
After installing, read further instructions here.

If you have any questions regarding the presentation or demos, I'd like to here them. More important, if you have any feedback you want to provide about the ADO.Net Entity Framework, I'd like to hear about it!

Enjoy!