Few days back I was optimizing the performance with Entity framework and Linq queries and I was using LinqPad and looking SQL generated by the Linq or entity framework queries. After some point of time I got the same question in mind that how I can find the SQL Statement generated by Entity framework?
/****** Object: Table [dbo].[Customer] Script Date: 09/09/2011 00:18:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
[CustomerId] [int] NOT NULL,
[CustomerName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[CustomerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: StoredProcedure [dbo].[esp_GetCustomerCount] Script Date: 09/09/2011 00:20:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[esp_GetCustomerCount]
@CustomerCount INT OUTPUT
AS
select @CustomerCount=COUNT(customerId) from Customer
GO
using System;
namespace ExperimentConsole
{
class Program
{
static void Main(string[] args)
{
using (ExperimentEntities myContext = new ExperimentEntities())
{
System.Data.Objects.ObjectParameter output = new System.Data.Objects.ObjectParameter("CustomerCount", typeof(int));
myContext.GetCustomerCount(output);
Console.WriteLine(output.Value);
}
}
}
}
[Key]
public int ArticleId { get; set; }
Here article ID will act as primary key.[ForeignKey("CategoryId")]
public ICollection<Category> Categories { get; set; }
In above example CategoryId is a foreign key of category table to article table.[ScaffoldColumn(false)]
public int ArticleId { get; set; }
Here it will tell article id will not have UI in scaffoling.[StringLength(512)]
public string Title { get; set; }
Here in the above code it tell that title could not have more then 512 character string.
[HttpPost]
public ActionResult Create(Models.Customer customer)
{
try
{
using (var databaseContext = new Models.MyDataContext())
{
databaseContext.Customer.Add(customer);
databaseContext.SaveChanges();
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
public ActionResult Edit(int id)
{
using (var databaseContext = new Models.MyDataContext())
{
return View(databaseContext.Customer.Find(id));
}
}
[HttpPost]
public ActionResult Edit(int id, Models.Customer customer)
{
try
{
using (var databaseContext = new Models.MyDataContext())
{
databaseContext.Entry(customer).State= System.Data.EntityState.Modified;
databaseContext.SaveChanges();
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
// GET: /Customer/Delete/5
public ActionResult Delete(int id)
{
using (var databaseContext = new Models.MyDataContext())
{
return View(databaseContext.Customer.Find(id));
}
}
//
// POST: /Customer/Delete/5
[HttpPost]
public ActionResult Delete(int id,Models.Customer customer)
{
try
{
using (var databaseContext = new Models.MyDataContext())
{
databaseContext.Entry(databaseContext.Customer.Find(id)).State = System.Data.EntityState.Deleted;
databaseContext.SaveChanges();
}
return RedirectToAction("Index");
}
catch
{
return View();
}
}
public ActionResult Details(int id)
{
using (var databaseContext = new Models.MyDataContext())
{
return View(databaseContext.Customer.Find(id));
}
}
We can all see lots of buzz about NuGet in asp.net world. So I decided to explore it. let’s explore it in some details. Following is a explanation of NuGet Extension on the code plex here.
"NuGet is a Visual Studio extension that makes it easy to add, remove, and update libraries and tools in Visual Studio projects that use the .NET Framework. When you add a library or tool, NuGet copies files to your solution and automatically makes whatever changes are needed in your project, such as adding references and changing your app.config or web.config file. When you remove a library, NuGet removes files and reverses whatever changes it made in your project so that no clutter is left."
Installing NuGet
Let’s see how we can install it. For that first you have to go thorough the Tools->Extension Manager and then search for nuget and it will appear in the list like below.
After that click on download and it will download and install it from the web. Make sure your computer has access to internet. Once you download your nugest extension it will ask you to restart visual studio. So once your are restarted you are ready with nuget packages.
How to install packages with NuGet
Here, I am going to install Entity Framework code first CTP 5 package with NuGet. To install that package first you have to go right click your project and then click add Library Reference Package like following.
After that a reference dialog will appear their you have click online and search entity as we need to install entity framework ctp. After searching the right package click on install it will start installing your package. Following is screen host of add library package reference dialog.
After clicking install EFlicence dialog will appear like this.
Click on I Accept and install will install package.After installing package a green icon will appear on top as indication of your successful installation like following.
Now after clicking on close if you see on your project reference An Entity framework is added to your project reference like following.
Also you can see a package.config will be added to your project. like following.
<?xml version="1.0" encoding="utf-8"?>That's it your package is installed and ready to use in your application. There is another way of adding packages with package console is also there. I will explain this on future blog posts. Hope you liked this. Stay tuned for more..
<packages>
<package id="EFCodeFirst" version="0.8" />
</packages>
Microsoft Entity Framework version 4.0 is a brand new ORM(Object Relational Mapper) from Microsoft. It’s provides now some new features which are not there in the earlier version of Entity framework. Let’s walk through a simple example of a new features which will create a new Entity class based on stored procedure result. We will use same table for this example for which they have used earlier for Linq Binding with Custom Entity.
Below is the table which have simple fields like first name,last name etc.
Let’s insert some data like following.
Below is the stored procedure which I am going to use for this example which will simply fetch data from the table.
CREATE PROCEDURE dbo.GetAllUsers
AS
SET NOCOUNT ON
SELECT
UserId,
UserName,
FirstName,
LastName,
FirstName + ' ' + LastName AS [FullName]
FROM dbo.Users
Now click ok and now it will create function which call the stored procedure and will return Object Result set of Type ‘UserInfo’ which we have just created. Now let’s bind that to a simple grid view to see how its works. So Let’s take a simple grid view like below.
<asp:GridView ID="grdUserList" runat="server">
</asp:GridView>
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
using (BlogEntities myEntity = new BlogEntities())
{
grdUserList.DataSource = myEntity.GetAllUsers();
grdUserList.DataBind();
}
}
}
That’s it very easy and simple to bind complex type with stored procedure using ADO.NET Entity Framework 4.0. Hope this will help you.. Happy Programming!!!
