Using Entity Framework 6.x in a Real Project

Prepare the Environment

1. Visual Studio 2013 is the best choice. When you installed VS2013, you almost get everything set. For example: Sql server express, .net 4.5, tools, nuget etc.

2. Entity Framework Power Tool: http://www.microsoft.com/en-us/download/details.aspx?id=40762

The Pain Point

In an information project, we will probability need

1. Database where we have tables, views, ETL, Stored Procedures. Use SP to provide outside data accessing.
Pain Point 1: Create business classes and create accordingly tables. C# engineer don't like doing things with SQL, am I right?
Pain Point 2: one of the most painful thing is mange the change of those classes and tables. Tedious and bug prone.
2. Data service, in this level we use ADO.NET to call SPs to get data. And mapping to .net classes (aka POCO). To increase performance and ensure security, we published data operations unit as Stored Procedures.
Pain Point 3: Writing ADO.NET code to call stored procedures and things will go crazy when SP output changed to be larger.
3. Business level.

A Realistic Working Model

If there is no requirement change in the development process, SQL is not favorable but still acceptable. Like tax and death is unavoidable in life, no project without change too. And those changes bring all the troubles. If we can write and manage all thing through model, and code, and database tables update automatically along with models. This sound perfect. My sense tells me ORM could bring all these advantages.

Another issue is performance, ORM has a bad reputation in performance. Especially when interact with huge data rows. I don't think it is a good idea to get all table data into .net objects and query data with Linq. One cure is Stored Procedure. If we can embed heavy queries inside a SP. And tune performance in the sql realm. Like this

UI ó EF ó SP ó Tables

EF interact with table only in table and model creation and migration. All data interactions occur through stored procedure.

NHibernate or Entity Framework

Before getting wet with ORM, I had carefully compared NHibernate and EF. Tried NHibernate first and then dropped out and then turn to EF, find EF is pretty easy to use full with samples and help document, NHibernate official site was even inaccessible recently.

Create Model with Data Annotation or Fluent API

First, create model data and model context class. Here I take a sample from MSDN document.

public class Blog {
    public int BlogId { get; set; }
    public string Name { get; set; }
    public virtual List<Post> Posts { get; set; }
}

public class Post {
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public int BlogId { get; set; }
    public virtual Blog Blog { get; set; }
}

And a context class, be careful here, don't use "name=BloggingDB_First", or no connection name found error will be raised up.

public class BloggingContext : DbContext {
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }
}

Create Database and Tables from Model

1.input "Enable-Migration" in the Package Manager Console. The command will create a Migrations folder and a Configuration file inside the folder.
2.input "Add-Migration initialModelCreation" , the command will scan target database and create the first migration model in Migrations folder.
3.input "update-database", then, open you database, you shall find tables created there now.

For your test compare, I paste comment execution result here.

Generate Model from Databases

After generating models from database, then you change the table a bit how to update the model with database? The answer is don't do this, since it is a Code First EF. Code should be the first. When you are going to change something, change only models and sync models to database.

Update Database and Tables from Model

For example, I add a [StringLength(300)] for a property. Then I input comment "Add-Migration changeSiteNameLengthTo300" in PMC. A new migration file will be created. Then , input "update-database" will update related tables.

For more and detail information on migration topic, see: http://msdn.microsoft.com/en-us/data/jj591621

Working with Stored Procedure

1. Work with query stored procedure

Let's see a most simple sp called GetAllBlogs:

CREATE PROCEDURE GetAllBlogs

AS

BEGIN

    select * from [dbo].[Blogs]

END

GO

 

Add a method in DbContext derived class

public virtual ObjectResult<Blog> GetAllBlogs() {

    this.Database.Initialize(force: false);

 

    var cmd = this.Database.Connection.CreateCommand();

    cmd.CommandText = "GetAllBlogs";

    this.Database.Connection.Open();

    var reader = cmd.ExecuteReader();

 

    var blogs = ((IObjectContextAdapter)this)

                    .ObjectContext

                    .Translate<Blog>(reader, "Blogs", MergeOption.AppendOnly);

    return blogs;

}


The Translate method accept a execution reader, a entity set name, and a MergeOption setting. with the GetAllBlogs, we can call the SP by

using (var dbcontext = new DBtoModel()) {

    var result = dbcontext.GetAllBlogs();

    foreach (var item in result) {

        Console.WriteLine(item.Name);

    }

}


One advantage of using EF to call SP Is that we are no longer need to manually mapping sp result and model.

    var blogs = ((IObjectContextAdapter)this)

                    .ObjectContext

                    .Translate<Blog>(reader, "Blogs", MergeOption.AppendOnly);

The above code will auto mapping it according to property names.

To dive deep of using sp in EF, you can read more here.

Summary

All I talked above is quite simple and need read more document to understand EF to leverage more power from EF. Nevertheless, the topics I list above prove that EF can ease the pain points and greatly ease the information project development. EF is powerful tool could make dev life better and project robust.

blog comments powered by Disqus