DotNetCurry Logo

Entity Framework 6.1 – Some New Features via an ASP.NET MVC application

Posted by: Mahesh Sabnis , on 3/14/2015, in Category Entity Framework
Views: 17412
Abstract: Entity Framework 6.1 comes with new features like Async support, Connection Resiliency, Logging etc. We will explore these features in an ASP.NET MVC application

In the current era of .NET enterprise applications, EntityFramework is used in most data access scenarios. Entity Framework (EF) in its initial release focused on the Database First approach, where the Database schema was ready and you could generate entities from it. This approach has been and is quite useful for data-intensive applications. Another way is Model-Driven Development, where you need to first plan for the Models required in the application and the relationship across them, and then generate Database Schema in any Database technology from it. For e.g. in case of an order management application, we need Customer-Order models. In this new software development system, we do not have any database schemas created (e.g. Tables and Stored Procs); instead the database schema is then generated with tables from these models using a visual Entity Data Model (EDM) designer.  Yet another approach called the Code First approach begins with classes that describe your conceptual model. There’s no visual EDM used with Code First.

Find Slow Code in .NET Apps

EF is now available to us via NuGet Package manager. Let us take an overview of some new features in EF 6 with extended support.

Async Support and Connection Resiliency

While performing Database transactions, if the connection is dropped due to some reason, the data does not get saved. This may cause issues, especially in business applications. In the Entity framework 6, we have the ability to retry. Connection Resiliency is a very helpful feature. Typically this is more important in cases when the database is available on the cloud. The class IDbConnectionStrategy is used to configure the retries.

In the following steps, we will implement an ASP.NET MVC application using EntityFramework Code First architecture. To complete this article we need to have access to SQL Server. In SQL Server create a database with the name ‘A3Ext’.

Step 1: Open Visual Studio 2013 Community Edition and create a new empty ASP.NET MVC 5 application. Name it as A3_EF6_Ext.

Step 2: In the MVC project > Models folder, add a new class file with the name ModelClasses.cs. In this class file add the following code:

using System.ComponentModel.DataAnnotations;

namespace A3_EF6_Ext.Models
{
    public class EmployeeInfo
    {
        [Key]
        public int Id { get; set; }
        public string EmpName { get; set; }
        public decimal Salary { get; set; }
        public string DeptName { get; set; }
        public string Designation { get; set; }
    }
}

Step 3: In the Models folder, right click and add an ADO.NET Entity Data Model and name it as ‘ExtEDMX’. In this wizard, select Empty Code First model as shown here:

efcodefirst

Once the wizard is finished, a class ExtEDMX is created which is derived from DbContext class.

Step 4: In this class, add the following property:

public virtual DbSet<EmployeeInfo> Employees { get; set; }

Step 5: In the web.config file, change the connection string:

<add name="ExtEDMX" connectionString="data source=DBServer;initial catalog=A3Ext;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" providerName="System.Data.SqlClient" />

The above connection string connects to the Sql Server Database named DbServer and to a database named A3Ext.

Step 6: In the Controllers folder, add a new MVC controller of the name EmployeeInfoController. In EF 6 Extended Enhancements we have Async Support. In .NET 4.5, Async support is provided for Task based programming using async and await keywords. In EF 6 Ext, the Async pattern is provided to LINQ query execution. To implement this in the Controller class, use the following namespace:

using System.Data.Entity;

The asynchronous methods for LINQ query execution are provided as FirstAsync(), LastAsync(), ToListAsync(), etc.

Step 7: In the controller, add the following action methods:

using System.Web.Mvc;

using System.Data.Entity; //The Reference for Async Methods

using A3_EF6_Ext.Models;
using System.Threading.Tasks;

namespace A3_EF6_Ext.Controllers
{
    public class EmployeeInfoController : Controller
    {
        ExtEDMX ctx;
        public EmployeeInfoController()
        {
            ctx = new ExtEDMX(); 
        }

        // GET: EmployeeInfo
        public async Task<ActionResult> Index()
        {
            var Emps = await ctx.Employees.ToListAsync();
            return View(Emps);
        }

        public ActionResult Create()
        {
            return View(new EmployeeInfo());
        }

        [HttpPost]
        public async Task<ActionResult> Create(EmployeeInfo Emp)
        {

            if (ModelState.IsValid)
            {
                ctx.Employees.Add(Emp);
                await ctx.SaveChangesAsync();
                return RedirectToAction("Index");
            }
            else
            {
                return View(Emp);
            }
        }
    }
}

In the above code, we are using SaveChangesAsync() method.

Step 8: Scaffold the Index View and Create View from the Index and Create action methods. Run the Index View. This will create a new table of name EmployeeInfo in the A3Ext database:

dbdesign

Logging the Database operations

In this release of Entity Framework (EF), whenever EF sends a command to the database, the application code can intercept it. The DbContext provides Log property using which logging can be implemented. When the Log property is set, the following items will be logged:

  • Queries like normal LINQ Queries, raw queries and eSql.
  • DML statements generated when SaveChanges() is executed.
  • Parameters
  • The Command execution whether Async or Synchronous.
  • The Starting time for command.

One nice feature here is that the logging can be done in other places as well for e.g. using File System

Step 9: In the MVC project, add a new folder with the name Loggerrepository. In this folder, add a text file of the name LoggerFile.txt.

Step 10: In the EmployeeInfoController add the following method. This method will read the LoggerFile.txt and adding log entries as shown here:

/// <summary>
/// Method for Logging Infrastructure
/// </summary>
/// <param name="logmessage"></param>
private void LogInfo(string logmessage)
{
    string FilePath = HttpContext.Server.MapPath("~/LoggerRepository/LoggerFile.txt");
    System.IO.File.AppendAllText(FilePath, logmessage);
}

Step 11: Add the following line in the beginning of Index and Create (with HttpPost) action methods:

ctx.Database.Log = l => LogInfo(l);

Step 12: Run the application and work with Index and Create Views, the Database commands will be logged in LoggerFile.txt as shown here:

Opened connection at 1/23/2015 11:21:06 PM +05:30

SELECT Count(*)
FROM INFORMATION_SCHEMA.TABLES AS t
WHERE t.TABLE_SCHEMA + '.' + t.TABLE_NAME IN ('dbo.EmployeeInfoes')
    OR t.TABLE_NAME = 'EdmMetadata'
-- Executing at 1/23/2015 11:21:06 PM +05:30
-- Completed in 16 ms with result: 1

Closed connection at 1/23/2015 11:21:06 PM +05:30
Opened connection at 1/23/2015 11:21:07 PM +05:30
SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[__MigrationHistory] AS [Extent1]
        WHERE [Extent1].[ContextKey] = @p__linq__0
    )  AS [GroupBy1]
-- p__linq__0: 'A3_EF6_Ext.Models.ExtEDMX' (Type = String, Size = 4000)
-- Executing at 1/23/2015 11:21:07 PM +05:30
-- Completed in 22 ms with result: SqlDataReader

Closed connection at 1/23/2015 11:21:07 PM +05:30
Opened connection at 1/23/2015 11:21:07 PM +05:30
SELECT TOP (1) 
    [Project1].[C1] AS [C1], 
    [Project1].[MigrationId] AS [MigrationId], 
    [Project1].[Model] AS [Model], 
    [Project1].[ProductVersion] AS [ProductVersion]
    FROM ( SELECT 
        [Extent1].[MigrationId] AS [MigrationId], 
        [Extent1].[Model] AS [Model], 
        [Extent1].[ProductVersion] AS [ProductVersion], 
        1 AS [C1]
        FROM [dbo].[__MigrationHistory] AS [Extent1]
        WHERE [Extent1].[ContextKey] = @p__linq__0
    )  AS [Project1]
    ORDER BY [Project1].[MigrationId] DESC
-- p__linq__0: 'A3_EF6_Ext.Models.ExtEDMX' (Type = String, Size = 4000)
-- Executing at 1/23/2015 11:21:07 PM +05:30
-- Completed in 20 ms with result: SqlDataReader

Closed connection at 1/23/2015 11:21:07 PM +05:30
Opened connection asynchronously at 1/23/2015 11:21:07 PM +05:30
SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[EmpName] AS [EmpName], 
    [Extent1].[Salary] AS [Salary], 
    [Extent1].[DeptName] AS [DeptName], 
    [Extent1].[Designation] AS [Designation]
    FROM [dbo].[EmployeeInfoes] AS [Extent1]
-- Executing asynchronously at 1/23/2015 11:21:07 PM +05:30
-- Completed in 20 ms with result: SqlDataReader

Closed connection at 1/23/2015 11:21:07 PM +05:30
Opened connection asynchronously at 1/23/2015 11:21:20 PM +05:30
Started transaction at 1/23/2015 11:21:20 PM +05:30
INSERT [dbo].[EmployeeInfoes]([EmpName], [Salary], [DeptName], [Designation])
VALUES (@0, @1, @2, @3)
SELECT [Id]
FROM [dbo].[EmployeeInfoes]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()
-- @0: 'LS' (Type = String, Size = -1)
-- @1: '78000' (Type = Decimal, Precision = 18, Scale = 2)
-- @2: 'IRC' (Type = String, Size = -1)
-- @3: 'Lead' (Type = String, Size = -1)
-- Executing asynchronously at 1/23/2015 11:21:20 PM +05:30
-- Completed in 1 ms with result: SqlDataReader

Committed transaction at 1/23/2015 11:21:20 PM +05:30
Closed connection at 1/23/2015 11:21:20 PM +05:30
Opened connection asynchronously at 1/23/2015 11:21:20 PM +05:30
SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[EmpName] AS [EmpName], 
    [Extent1].[Salary] AS [Salary], 
    [Extent1].[DeptName] AS [DeptName], 
    [Extent1].[Designation] AS [Designation]
    FROM [dbo].[EmployeeInfoes] AS [Extent1]
-- Executing asynchronously at 1/23/2015 11:21:20 PM +05:30
-- Completed in 1 ms with result: SqlDataReader

Closed connection at 1/23/2015 11:21:20 PM +05:30


The above log shows the Connection Opened and Closed Time. Its shows the Async connection open and closed as well. One important point here is that these logs have parameters logged during insert operations for Create action method.

During the logging, we can change the settings for what is logged and the format of logging. This can be achieved using the class derived from DataLogFormatter. We need to override the following methods:

-LogCommand: How commands are logged before being executed.

-LogResult: The logging of the result of command execution.

-LogParameter: Formatting and content of parameter logging.

Performing Code First DML Operations using Stored Procedures

The Code First approach by default, configures entities to perform DML operations using direct table access. From EF 6 onwards, Code First can be configured to use stored procedures. We need to make some changes in our ExtEDMX class.

Step 13: Add the following code in the ExtEDMX class:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  modelBuilder.Entity<EmployeeInfo>().MapToStoredProcedures();
}

The above code will generate stored procedures for Insert, Update and Delete. Parameters for the stored procedures will correspond to the entity property names.

Step 14: To work on the stored procedure with Code First, we need to make some changes in the project and use the Migration commands.

Add the following line in the Global.asax

Database.SetInitializer<ExtEDMX>(null); //For Changes

Step 15: Enable migrations for ExtEDMX using package Manage Command as below:

Enable-Migrations -ContextTypeName A3_EF6_Ext.Models.ExtEDMX

Add the migration for the EmployeeInfo as shown here:

add-migration "Add Sproc for EmployeeInfo"

Now update the database with following command:

update-database

The result will be as shown here:

updatedb

Now if we open the database, we will find the stored procedures created as below:

stored-procedure

We can view the definition of the stored procedure e.g. EmployeeInfo_Insert:

USE [A3Ext]
GO
/****** Object:  StoredProcedure [dbo].[EmployeeInfo_Insert]    Script Date: 1/23/2015 11:57:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[EmployeeInfo_Insert]
    @EmpName [nvarchar](max),
    @Salary [decimal](18, 2),
    @DeptName [nvarchar](max),
    @Designation [nvarchar](max)
AS
BEGIN
    INSERT [dbo].[EmployeeInfoes]([EmpName], [Salary], [DeptName], [Designation])
    VALUES (@EmpName, @Salary, @DeptName, @Designation)
    
    DECLARE @Id int
    SELECT @Id = [Id]
    FROM [dbo].[EmployeeInfoes]
    WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()
    
    SELECT t0.[Id]
    FROM [dbo].[EmployeeInfoes] AS t0
    WHERE @@ROWCOUNT > 0 AND t0.[Id] = @Id
END

Step 16: Run the Create Action of the Controller and execute it. In the LoggerFile.txt we can now see that the Stored Procedure is used for performing Insert operations:

Closed connection at 1/23/2015 11:21:20 PM +05:30
Opened connection asynchronously at 1/24/2015 12:00:59 AM +05:30
Started transaction at 1/24/2015 12:00:59 AM +05:30
[dbo].[EmployeeInfo_Insert]
-- EmpName: 'TS' (Type = String, Size = 1073741823)
-- Salary: '78000' (Type = Decimal, IsNullable = false, Precision = 18, Scale = 2)
-- DeptName: 'RES' (Type = String, Size = 1073741823)
-- Designation: 'Lead' (Type = String, Size = 1073741823)
-- Executing asynchronously at 1/24/2015 12:00:59 AM +05:30
-- Completed in 8 ms with result: SqlDataReader

Committed transaction at 1/24/2015 12:00:59 AM +05:30
Closed connection at 1/24/2015 12:00:59 AM +05:30

Conclusion: In Entity Framework 6 and its enhancements, some great features for developing Line of Business (LOB) applications have been provided. New features like Code First with support for Stored Procs, Async programming and Logging  give us more control on application development.

Download the entire source code of this article (Github)

Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+
Further Reading - Articles You May Like!
Author
Mahesh Sabnis is a DotNetCurry author and Microsoft MVP having over 17 years of experience in IT education and development. He is a Microsoft Certified Trainer (MCT) since 2005 and has conducted various Corporate Training programs for .NET Technologies (all versions). Follow him on twitter @maheshdotnet


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by Aqua on Tuesday, March 17, 2015 7:56 AM
Good article. I was really hoping someone spoon feeds me with what's new in the Entity Framework arena and you just did :)
Comment posted by Denise San on Tuesday, March 24, 2015 9:14 PM
What about Oracle? Any support without needing expensive connectors?
Comment posted by Suprotim Agarwal on Tuesday, March 24, 2015 9:30 PM
Oracle 12 supports EF6 and the same drivers work with Oracle 11 as well. By the way, did you give this a try https://www.nuget.org/packages/odp.net.managed/