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:
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:
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:
Now if we open the database, we will find the stored procedures created as below:
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)
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!
Was this article worth reading? Share it with fellow developers too. Thanks!
Mahesh Sabnis is a DotNetCurry author and a Microsoft MVP having over two decades 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), and Front-end technologies like Angular and React. Follow him on twitter @
maheshdotnet or connect with him on
LinkedIn