In this post I will be looking into EF 6.0 using the Code First Workflow and its support for stored procedures.
I will be building a simple ASP.Net MVC 5.0 application that will be the client application that will consume the Entity Framework data access layer.
I will be using Visual Studio 2013,C# 5.0 and LocalDb in this demo.
While I am building this simple ASP.Net application I will point out to various tools and option in VS 2013 that make the completion of an application faster.
I will be writing as less code as possible. I will be leveraging the scaffolding functionality as much as possible.
I will also show you how to log queries sent to the database by Entity Framework 6.0.
With EF 6.0 we do have a mechanism to trace/profile everything EF sents to the data store.
I will also demonstrate how you can install Entity Framework Power Tools that enables developers to visualise the POCO classes in an entity designer.
We will create the ASP.NET Web Application with the MVC 5 Project Template.
1) Launch Visual Studio 2013 and click on the "New Project". Select the Web from the left pane and create the ASP.NET Web Application. Have a look at the picture below
2) Select the MVC Project Template as shown below.
3) Visual Studio 2103 will automatically create the ASP.Net MVC 5.0 application.In the Models folder we will add a new class file, Footballer.cs
Right-click on the Models folder and Add a new Class, Footballer.cs.
public class Footballer
{
public int FootballerID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public double Weight { get; set; }
public double Height { get; set; }
}
4) We will add declarative code in this simple class in order to define that we need the properties (columns in the database) to be required - not null fields in the database.
We need to reference the System.ComponentModel.DataAnnotations assembly. Have a look at the code below.
using System.ComponentModel.DataAnnotations;
namespace EF6StoreProcMVC5.Models
{
public class Footballer
{
public class Footballer
{
public int FootballerID { get; set; }
[Required]
public string FirstName { get; set; }
[Required]
public string LastName { get; set; }
[Required]
public double Weight { get; set; }
[Required]
public double Height { get; set; }
}
}
}
5) Entity Framework is already installed in our application. Looking into packages.config I see that i have the
<package id="EntityFramework" version="6.1.1" targetFramework="net451" />
I need to update to the latest version of the Entity Framework. YI update the package by entering the following command in the Package Manager Console:
Update-Package EntityFramework
Have a look at the picture
Now Entity Framework in my solution is updated to 6.1.3.
6) Now I am going to build the Controller. I am going to the Controllers folder and click Add --> New Scaffolded Item.
Have a look at the picture below
7) In the Add Scaffold wizard, select the MVC 5 Controller with views.
Have a look at the picture below
8) In the next Add Controller wizard, select the Model Class and add the Data Context class, select a suitable name for that and select a name for the controller and click Add
Have a look at the picture below
9) Have a look at the generated FootballerDBContext.cs
using System.Data.Entity;
namespace EF6StoreProcMVC5.Models
{
public class FootballerDBContext : DbContext
{
// You can add custom code to this file. Changes will not be overwritten.
//
// If you want Entity Framework to drop and regenerate your database
// automatically whenever you change your model schema, please use data migrations.
// For more information refer to the documentation:
// http://msdn.microsoft.com/en-us/data/jj591621.aspx
public FootballerDBContext() : base("name=FootballerDBContext")
{
}
public DbSet<Footballer> Footballers { get; set; }
}
}
At this point if you want to visualise the Footballer entity then you need to install the EF Power Tools. You must go to Tools and then Extensions and Updates.
Then do a search for Entity Framework Power Tools and then install them.
Have a look at the picture below
You can then select the FootballerDBContext.cs and then Entity Framework --> View Entity Data Model. Have a look at the picture below.
Have a look at the picture below to see the entity in the designer. This is very handy tool if you want to visualise your entities.
10) Have a look at the FootballersController.cs that was also generated.
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web.Mvc;
using EF6StoreProcMVC5.Models;
namespace EF6StoreProcMVC5.Controllers
{
public class FootballersController : Controller
{
private FootballerDBContext db = new FootballerDBContext();
// GET: Footballers
public ActionResult Index()
{
return View(db.Footballers.ToList());
}
// GET: Footballers/Details/5
public ActionResult Details(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Footballer footballer = db.Footballers.Find(id);
if (footballer == null)
{
return HttpNotFound();
}
return View(footballer);
}
// GET: Footballers/Create
public ActionResult Create()
{
return View();
}
// POST: Footballers/Create
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include = "FootballerID,FirstName,LastName,Weight,Height")] Footballer footballer)
{
if (ModelState.IsValid)
{
db.Footballers.Add(footballer);
db.SaveChanges();
return RedirectToAction("Index");
}
return View(footballer);
}
// GET: Footballers/Edit/5
public ActionResult Edit(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Footballer footballer = db.Footballers.Find(id);
if (footballer == null)
{
return HttpNotFound();
}
return View(footballer);
}
// POST: Footballers/Edit/5
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include = "FootballerID,FirstName,LastName,Weight,Height")] Footballer footballer)
{
if (ModelState.IsValid)
{
db.Entry(footballer).State = EntityState.Modified;
db.SaveChanges();
return RedirectToAction("Index");
}
return View(footballer);
}
// GET: Footballers/Delete/5
public ActionResult Delete(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Footballer footballer = db.Footballers.Find(id);
if (footballer == null)
{
return HttpNotFound();
}
return View(footballer);
}
// POST: Footballers/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int id)
{
Footballer footballer = db.Footballers.Find(id);
db.Footballers.Remove(footballer);
db.SaveChanges();
return RedirectToAction("Index");
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
db.Dispose();
}
base.Dispose(disposing);
}
}
}
11) You can also have a look at the generated views that were created by the Scaffold wizzard
We choose to open the _Layout.cshtml view and add the following line of code in the navigation pane
<li>@Html.ActionLink("Footballers", "Index", "Footballers")</li>
the full code is
<ul class="nav navbar-nav">
<li>@Html.ActionLink("Home", "Index", "Home")</li>
<li>@Html.ActionLink("About", "About", "Home")</li>
<li>@Html.ActionLink("Footballers", "Index", "Footballers")</li>
<li>@Html.ActionLink("Contact", "Contact", "Home")</li>
</ul>
Now we can navigate to our page through the menu.
Build and run your application.It will take some time to appear in the first time since the database is created.The database is under the App_Data special folder.
When you will see you application, click on Footballers from the menu and then add some sample data. Have a look at the picture below.
12) After you insert those records in the database then you would wonder what is the T-SQL that EF DBContext sent to the SQL Server LocalDb to execute.
There are various ways to profile the T-SQL statements that EF sends to the data store. Have a look here, here and here for some ways of profiling the data in earlier versions of EF.
You can always use the SQL Server Profiler. In EF 6.0 we can trace the T-SQL statements using the the Log property of DbContext.
I am going to add some code in the FootballersController.cs class file in order to intercept the T-SQL statements.
I am going to add inside the FootballersController constructor the following code.
public FootballersController()
{
db.Database.Log = T => Debug.Write(T);
}
Make sure you add a reference to the System.Diagnostics assembly.
Have a look below to see where above statement fits with everything.
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web.Mvc;
using EF6StoreProcMVC5.Models;
using System.Diagnostics;
namespace EF6StoreProcMVC5.Controllers
{
public class FootballersController : Controller
{
private FootballerDBContext db = new FootballerDBContext();
public FootballersController()
{
db.Database.Log = T => Debug.Write(T);
}
// GET: Footballers
public ActionResult Index()
{
return View(db.Footballers.ToList());
}
Now If I build and run my application again and try to insert more records and I have my Output window open i can see wha is sent to the database.
Have a look at the picture below. Αs you can see a transaction is opened and values are inserted using an Insert statement. Then the transaction is commited. So we can see the complete T-SQL code. Note that when we have an Insert statement EF engine starts a transaction.
13) We want to use stored procedures instead of Insert,Update,Delete statements. We can have the EF engine generate them. We need to enable Code First Migrations
Go to Tools-> NuGet Package Manager->Package Manager Console and enter the following command and hit Enter:
Enable-Migrations -ContextTypeName EF6StoreProcMVC5.Models.FootballerDBContext
You have to put your own DBContext class where I put EF6StoreProcMVC5.Models.FootballerDBContext.
Have a look at the files generated under the Migrations folder.
Code first Migrations were enabled for the project.
Now we must tell the DbContext class that must use the Stored Procedures. Do open the FootballerDBContext class and update the code as shown below:
We just add this method and keep everything else.
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Footballer>().MapToStoredProcedures();
}
We do override the OnModelCreating method.
14) Now we need to generate the stored procedures
Go to Tools-> NuGet Package Manager->Package Manager Console and enter the following command and hit Enter:
Add-Migration MyfootballSPs
The 201503282306498_MyfootballSPs.cs is generated. Have a look at the code below to see the Stored Procedures.
namespace EF6StoreProcMVC5.Migrations
{
using System;
using System.Data.Entity.Migrations;
public partial class MyfootballSPs : DbMigration
{
public override void Up()
{
CreateStoredProcedure(
"dbo.Footballer_Insert",
p => new
{
FirstName = p.String(),
LastName = p.String(),
Weight = p.Double(),
Height = p.Double(),
},
body:
@"INSERT [dbo].[Footballers]([FirstName], [LastName], [Weight], [Height])
VALUES (@FirstName, @LastName, @Weight, @Height)
DECLARE @FootballerID int
SELECT @FootballerID = [FootballerID]
FROM [dbo].[Footballers]
WHERE @@ROWCOUNT > 0 AND [FootballerID] = scope_identity()
SELECT t0.[FootballerID]
FROM [dbo].[Footballers] AS t0
WHERE @@ROWCOUNT > 0 AND t0.[FootballerID] = @FootballerID"
);
CreateStoredProcedure(
"dbo.Footballer_Update",
p => new
{
FootballerID = p.Int(),
FirstName = p.String(),
LastName = p.String(),
Weight = p.Double(),
Height = p.Double(),
},
body:
@"UPDATE [dbo].[Footballers]
SET [FirstName] = @FirstName, [LastName] = @LastName, [Weight] = @Weight, [Height] = @Height
WHERE ([FootballerID] = @FootballerID)"
);
CreateStoredProcedure(
"dbo.Footballer_Delete",
p => new
{
FootballerID = p.Int(),
},
body:
@"DELETE [dbo].[Footballers]
WHERE ([FootballerID] = @FootballerID)"
);
}
public override void Down()
{
DropStoredProcedure("dbo.Footballer_Delete");
DropStoredProcedure("dbo.Footballer_Update");
DropStoredProcedure("dbo.Footballer_Insert");
}
}
}
15) We need to tell the database to create the MyfootballSPs. Enter the following command in the Package Manager Console:
Update-Database
and hit Enter. Go to Server Explorer and open the database. Have a look under Stored Procedures. The stored procedures are created.
Have a look at the picture below to see what I got when I did update the database.
16) We want to check if Entity Framework is now using the stored procedures. Do run the application again and add some footballers in the web page.
Have a look at the picture below to see what I got in the Output window:
I can see clearly that the Insert statements are not used and instead of that the dbo.Footballer_Insert is used.
In this post we did create a small ASP.Net MVC 5.0 application and we did go through on how to use stored proecedures with Code Firsr EF workflow.
Hope it helps!!!
In the past few weeks I was looking into the new changes that have been made in the current beta release of ASP.NET 5.0. In this post I will talk about those new changes and what impressed me most.
There have been many changes and in my opinion this release is the most important , significant release since the ASP.Net introduction.
If you want to install it and write some ASP.Net 5 code first you need to download the latest CTP of Visual Studio 2015 which was made available a few weeks back.
Let me start by telling you that ASP.NET is 15 years old since it has been released back in 2000, just to put things into perspective.
ASP.Net 5 is open source
ASP.NET 5 is developed as an open source project and is available on GitHub.
If you want you can download the code and make changes and then submit those changes. Obviously the ASP.Net team is responsible which of those changes will find their way to the official final release.
I really like the new approach that MS is taking on the whole open source subject. I think most developers love it and it helps us to understand more about how everything is built.
ASP.NET is supported by a cross-platform runtime
This simply means that ASP.Net 5 applications can run now on the MAc OS X and Linux operating systems.
This means that developers that do not come from a Windows background can build and run their apps in a MacBook. I think that will open new horizons for people that chose not to use machines that run microsoft operating systems
Some developers might argue that they have been running ASP.Net apps on Mono. As we know Mono is an open source implementation of Microsoft's .NET Framework. It is not the same code base.
When hosting your application the ASP.Net 5 gives you two choices and great flexibility since ASP.Net 5 works with two runtimes, .NET Core and .Net Framework.
Applications using the .NET Core (which is more limited than the full .Net version) will run on this version of the runtime even if the host machine runs a different version of the runtime.
If one updates the runtime of that particular application other applications that run on different versions of the runtime will not be affected. Another thing to bear in mind is that you will not be prompted for features that you do not need.
You can also have your application running on the full .Net Framework so you can have full access to all of the APIs available.
Web Forms won’t be part of ASP.NET 5
This is a pretty big decision and a major change. Web forms will still be supported in the sense that someone will be able to build web apps with web forms in VS 2015 but will not get all the goodies that ASP.Net 5 comes with e.g those web form apps will not run on Linux or OS X operating systems.
Web forms was a great programming paradigm that help MS to bring into the ASP.Net stack all those window VB developers that used to build window applications on a form environment.
Things have moved since. Web forms abstract the web and do not embrace it as ASP.Net MVC does. The choice that has been made by MS is clear, they want us to develop or rewrite new web applications on ASP.Net MVC which is what happens anyway in most cases. ASP.Net MVC 6 that ships with the ASP.Net 5 is the technology of choice for all new ASP.Net 5.0 applications
New Project Templates
There are new project templates in ASP.Net 6.0 and VS 2015. When starting a new ASP.NET 5 project, "File -> New Project -> Web -> ASP.NET Web Application" you can see the set of project templates displayed as below.
ASP.NET 4.6 templates are grouped together and the new ASP.NET 5 preview templates are grouped seperately.
ASP.Net 5 does not support VB.Net
This is another big decision made by Microsoft. I quote Scott Hunter here "ASP.NET 5 is C# only at this point and that will not change before we RTM. We plan to have extensibility points so other languages like VB, F#, etc can be added via the form of a support package or such."
Most developers (I would say 98% of the .Net projects in the last 5 years have been implemented in C#) were anticipating such a decision I suppose.
ASP.Net 5 & Tag Helpers
Tag Helpers is one brand new feature in ASP.Net 5 and ASP.Net MVC 6.
This is a direct alternative of the MVC helper methods that we are writing inside our Razor Views.
I am pretty sure HTML designers will love this new feature as it makes more sense to them.
Let's have a look at the View below
@using (Html.BeginForm())
{
<div>
@Html.LabelFor(m => p.Αge, "Age:")
@Html.TextBoxFor(m => p.Age)
</div>
<input type="submit" value="Submit" />
}
We have the Html.BeginForm(), Html.LabelFor(), and Html.TextBoxFor() helper methods that are part of the razor syntax in order to build a form.
Let's rewrite the snipper above using Tag Helpers:
@addtaghelper "Microsoft.AspNet.Mvc.TagHelpers"
<form asp-controller="Customers" asp-action="Submit" method="post">
<div>
<label asp-for="Age">Age:</label>
<input asp-for="Age" />
</div>
<input type="submit" value="Save" />
</form>
Have a look here for a complete example
Support and Integration with Bower, Grunt and Gulp
There is a built in support for Bower, Grunt and Gulp in VS 2015.
Developers can manage JavaScript and CSS libraries through Bower which is basically a package manager for client-side libraries
Through GruntJS developers can minify javascript files,compile LESS and Sass files into CSS, do code validation, run javascript unit tests.
A great addition for front-end development that will excite many client side developers.
Unified MVC 6 model
When building ASP.Net applications we often use MVC, Web API and Web Pages. In this new release of ASP.Net 5 these programming frameworks are merged into one.
For example in ASP.Net MVC 6 there is only one Controller class, I mean one base Controller class (Microsoft.AspNet.Mvc.Controller) class.
In previous versions of ASP.NET MVC, MVC controllers were different than Web API controllers.
Support for AngularJS
AngularJS is a JavaScript framework. It can be added to an HTML page with a <script> tag. AngularJS extends HTML attributes with Directives.
It is extremely popular amongst client-side developers and now there is great support in Visual Studio 2015 for AngularJs modules e.t.c.
ASP.NET 5 Dependency Injection Framework support
Most professional developers use dependency injection as a software design pattern that implements inversion of control. There is built-in support for DI in ASP.Net 5 so we do not need to rely on third party DI frameworks like AutoFac.
xUnit.net built in support in VS 2015
Mstest ([TestClass], [TestMethod]) was the default testing framework for Visual Studio so far.
ASP.NET 5 and VS 2015 uses xUnit.net as a unit test framework. This framework uses the [Fact] attribute. Support for Mstest still exists.
Have a look at this extensive and detailed example on how to use xUnit net and ASP.net
Hope it helps!!!