Entity Framework 6: DB Logging and Stored Procedure Mapping for EF Code First

Entity Framework 6 has gone from Beta to Release Candidate 1 since we last posted our ‘what’s new’ article, so today we’ll see how to upgrade from Beta to RC 1 and then take a look at two new EF 6 features, DB Logging and Mapping CRUD operations to Stored Procedures directly.

Updating from EF6 Beta to RC 1

We’ll start off with the Solution from our previous article. You can clone the code from the GitHub Repo or download the zip archive directly. Once you have the solution loaded in Visual Studio 2013 (Preview at the moment), updating from EF6 Beta to RC 1 is pretty easy. All you have to do is in the Package Manager Console, type in the following:

PM> update-package EntityFramework –pre

Compile and you should be good to go, unless you were using APIs that are no longer supported (here are the release notes).

Monitoring EF activity with the new DB Logging capabilities

DB Logging implies monitoring the interactions between EF and the Database. Earlier we have seen two excellent ASP.NET components in MiniProfiler and Glimpse that gave us details of what kind of Queries are being executed. EF now provides this ability natively now. The DB Logging capability aims to provide:

- A means of understanding how EF is translating LINQ to SQL

- Digging into see unexpected behavior of some of your queries that might be long running unexpectedly

- Trying to analyze complex EF queries if they are not returning expected results.

Enabling Debugging

Enabling debugging is as simple as assigning an action method that takes a string parameter as we can see below:

We can update our PostsController.cs and add Logging for the Details action method to view the query that EF is firing when we do a db.Posts.Find(id). So we add a Logger function and assign it to the db.Database.Log. Whenever EF needs to Log, it will call our Logger function, which essentially logs to the Debug console.

public ActionResult Details(Int32 id)
{
db.Database.Log = Logger;
Post post = db.Posts.Find(id);
if (post == null)
{
  return HttpNotFound();
}
return View(post);
}

private void Logger(string logString)
{
Debug.WriteLine(logString);
}

Now when we run the application and navigate to the Details page, our Logger method is invoked thrice, once for the Find query, once when execution starts and finally when execution is complete.

If we look into the Debug Console, we’ll see it executed a “SELECT TOP(2)…” (hmm… wonder why) query, that had the input parameter value of 1 and took 72ms to execute (in the following case) using the SqlDataReader.

Neat, but apparently we didn’t have any control over this Log. What if we want to format the string or do other custom stuff? Well, EF provides a lot of extension points that can be leveraged to create a Glimpse like logging and tracking. It deserves a full article of its own, so we’ll pick it up another day. For now, we have a easy way to put temporary Log statements and take a peek under specific EF queries for some adhoc investigations.

Lets now move on to another new feature – Mapping CRUD operations to stored procedures.

EF Code First - Stored Procedure mapping

The Database is often a very controlled environment in Production and has strict monitoring, conventions and permissions. This can often lead to restrictions for OR/M frameworks. EF 6 thus introduces one new feature in its arsenal for the Code First approach, making it easier to talk to highly locked-down Databases. Other conditions when Stored Procedure mapping can be used are – Mapping to existing databases that already use Stored Procedures, you need to run additional Save logic best performed within the database, Business or Technical strengths of a team mandates use of stored procedures and so on.

Using Stored Procedure Mapping

To use stored procedure mapping, we update our OnModelCreating method and add the following code to it

modelBuilder
.Entity<Post>()
.MapToStoredProcedures();

The new MapToStoreProcedures() method has two overrides, one without parameters, that uses EF Code First conventions to setup the Stored Procedures as seen above. There is another override that takes an Action method as input, allowing you to customize the Stored Procedure name, parameters etc. in case you are mapping to existing stored procedures.

Let’s look at the default convention first. Once the OnModelCreating method is updated, we run migration to update the Database

PM> add-migration PostStoredProcedures

The DBMigration class that gets generated has the Stored Procedures defined.

public override void Up()
{
CreateStoredProcedure(
  "dbo.Post_Insert",
  p => new
  {
   POST_TITLE = p.String(maxLength: 200),
   POST_CONTENT = p.String(),
  },
  body:
   @"INSERT [dbo].[Posts]([POST_TITLE], [POST_CONTENT])
    VALUES (@POST_TITLE, @POST_CONTENT)

     DECLARE @POST_PK_POST_ID int

     SELECT @POST_PK_POST_ID = [POST_PK_POST_ID]
     FROM [dbo].[Posts]
     WHERE @@ROWCOUNT > 0 AND [POST_PK_POST_ID] = scope_identity()
             
     SELECT t0.[POST_PK_POST_ID]
     FROM [dbo].[Posts] AS t0
     WHERE @@ROWCOUNT > 0 AND t0.[POST_PK_POST_ID] = @POST_PK_POST_ID"
);
// … rest of code removed for brevity
}

As we can see above, the DBMigration class has been generated with the Stored Procedure definitions for Insert, Update and Delete. There are no stored procedures for SELECT.

We upgrade the database using EF Migrations

PM> update-database

To see the Stored Procedure in action, we’ll use the Logging features we just saw above and update the Edit (POST) action method to Log the EF interactions as follows

public ActionResult Edit(Post post)
{
if (ModelState.IsValid)
{
  db.Database.Log = Logger;
  db.Entry(post).State = EntityState.Modified;
  db.SaveChanges();
  return RedirectToAction("Index");
  }
return View(post);
}

Now we can run the application and update a Post entry.

When we hit save, the DB interaction is logged and we get the following in our debug window. We see that the [dbo].[Post_Update] SP has been called with the ID, the Title the (new) Content and it has taken 415 ms to execute.

Okay, so far so good, what if we wanted to map to Stored Procedures that were already written? Well there are options we can use.

Customizing SP Mapping Options

We can use the second overload of the MapToStoredProcedures method to specify options for Insert, Update and Delete stored procedures.

.

Since we tried Update above, and have the Logging setup for it, let’s customize the Update method to map it to a Stored Procedure name ‘UpdatePost’ instead of the earlier default of ‘Post_Update’ and also we’ll call the Content parameter by a different name, say ‘post_content’. The final MapToStoredProcedures method call is as follows:

modelBuilder
.Entity<Post>()
.MapToStoredProcedures(sp=>
  sp.Update(u => u.HasName("UpdatePost").Parameter(p => p.Content,
  "post_content")));

Once again we generate the Migration, update the database and then run the app. On updating the Post entry, we can see the Log now shows the Stored Procedure Name as ‘UpdatePost’ and the CONTENT param as post_content, implying our custom mapping is working just fine.

This brings us to the close of this post that highlighted two new features of EF 6 (now in RC1) – DB Command Logging and Code First Mapping to Stored Procedures.

The Mapping API is pretty involved and we didn’t explore all the options, but it should suffice most customization requirements. Most stored procedures do something in addition to the basic CRUD operations, and we can easily add those additional SQL queries in the Scaffolded class before running the migration. EF will not interfere with the updated queries and add them to the DB appropriately. This makes EF Code First + Stored Procedure Mapping + Migrations a rather neat Versioning mechanism for Stored Procedures as well.

Conclusion

Entity Framework continues to mature as a first class OR Mapping framework and with version 6, brings some interesting goodies that will make it more acceptable to a bigger set of Enterprise Customers.

Download the entire source code of this article (Github)

This article has been editorially reviewed by Suprotim Agarwal.

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigious Microsoft MVP award for Sixteen consecutive years. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that offers Digital Marketing and Branding services to businesses, both in a start-up and enterprise environment.

Get in touch with him on Twitter @suprotimagarwal or at LinkedIn