The Brave Programmer - Blogging and coding
Not for the faint hearted
 

Blog Posts From The Brave Programmer

Minimize

Entity Framework 5 – CRUD Operations

Apr8

Written by:
2013/04/08 04:03 PM RssIcon

Entity Framework 5 (EF5) is the latest public release of Microsoft's ADO.net Entity Framework model. The whole purpose of Entity Framework is to make working with data easier and faster. CRUD (Create, Retrieve, Update, Delete) operations is how we add new data, delete data, update existing data and retrieve or pull data from a database.

A framework or ORM such as Entity Framework is totally useless for data operations if it no ability to perform CRUD operations.

Today we are going to look at how to perform these CRUD operations using Linq to Entities. The assumptions that we have is that you have already created your Model and you are at least familiar with Linq.

In all cases we have to have a context to programme against. So I am going to use the using (EFDemoEntities dbc = new EFDemoEntities()) method in all my examples

Retrieve Data

using (EFDemoEntities dbc = new EFDemoEntities())

{

var prodModels = from p in dbc.ProductModels

select p;

}

Above is a simple Linq statement that retrieves all data and all columns from the ProductModel table. Because we are using Linq To Entities we are programming against the Entity (Class), i.e. ProductModels. This would then return a IQueryable collection of ProductModel (IQueryable<ProductModel>). Remember that Entity Framework uses lazy loading by default. This means that the SQL statement is prepared by no actual SQL execution is performed by the database to retrieve the data until it is actually needed. That is we have to iterate over the collection or use something else that will perform some kind of iteration, like binding to a grid.

So in the example above and all other examples, unless otherwise stated, we will automatically bind to a grid to product a similar output as below. This would then be a sample result of the above Linq statement.

The above would produce the following SQL:

SELECT

[Extent1].[ProductModelID]AS[ProductModelID],

[Extent1].[Name]AS[Name],

[Extent1].[CatalogDescription]AS[CatalogDescription],

[Extent1].[rowguid]AS[rowguid],

[Extent1].[ModifiedDate]AS[ModifiedDate]

FROM[SalesLT].[ProductModel]AS [Extent1

Update Data

To update date we retrieve a single row by using a where clause. That would return a single object. We can then alter the property values of the object and the save it back to the database.

There are several ways of retrieving a subset of data. The simplest is to use a where clause.

using (EFDemoEntities dbc = new EFDemoEntities())

{

var prodModels = (from p in dbc.ProductModels

where p.ProductModelID == 10

select p).SingleOrDefault();

    prodModels.Name = "New Product Name";

    dbc.SaveChanges();

}

 

Notice the use of the SingleOrDefault extension method. This ensures that we get a single populated ProductModel Object. Single would throw an exception if the resultant query returned more than one row. We would then make any changes we need to by changing the values of one or any of the properties.

        prodModels.Name = "New Product Name";

 

Now to save the changes we just call the SaveChanges method of the Context

dbc.SaveChanges();

The Following SQL would be produced:

SELECTTOP (2)

[Extent1].[ProductModelID]AS[ProductModelID],

[Extent1].[Name]AS[Name],

[Extent1].[CatalogDescription]AS[CatalogDescription],

[Extent1].[rowguid]AS[rowguid],

[Extent1].[ModifiedDate]AS[ModifiedDate]

FROM[SalesLT].[ProductModel]AS [Extent1]

WHERE 10 =[Extent1].[ProductModelID]

GO

 

-- Region Parameters

DECLARE@0NVarChar(50) = 'New Product Name'

DECLARE@1Int= 10

-- EndRegion

update[SalesLT].[ProductModel]

set[Name]=@0

where ([ProductModelID]=@1)

Create New Data

In order to create or insert new date we first instantiate a new type. We then update the values of the properties. Next we add it to the underlying set of the context and then save that back to the database.

using (EFDemoEntities dbc = new EFDemoEntities())

{

ProductModel newProdModel = new ProductModel();

newProdModel.Name = "NewProductName";

newProdModel.rowguid = Guid.NewGuid();

newProdModel.ModifiedDate = DateTime.Today;

//Add to Entity Set of context

dbc.ProductModels.Add(newProdModel);

//Save

dbc.SaveChanges();

 

}

Which would produce the following SQL:

-- Region Parameters

DECLARE@0NVarChar(50) = 'NewProductName'

DECLARE@1UniqueIdentifier= 'bb27dbdc-5937-4c1d-83bc-699ab16f1116'

DECLARE@2DateTime2= '2013-04-08 00:00:00.0000000'

-- EndRegion

insert[SalesLT].[ProductModel]([Name], [CatalogDescription], [rowguid], [ModifiedDate])

values (@0, null, @1, @2)

select[ProductModelID]

from[SalesLT].[ProductModel]

where@@ROWCOUNT> 0 and [ProductModelID] = scope_identity()

Notice the extra select with the scope_identity() statement. This is actually returning the inserted row back to our model. In fact we can use that new inserted row and bind it directly to a control. So if you inspected newProductModel and or bind it to a control right after the save changes method, you would expect to see data if all went well.

Deleting Data

Like updating data I need to first retrieve the row that I want to delete and then delete it. This might at first seem weird, but remember we are working with objects. So our typed Entity Object needs to know which row or data it needs to delete. Once we have that we can then just delete it from the Entity set of the context. We use the Single() extension method to make sure we get only one row back. This would through an exception if either a null or more than one row is returned.

using (EFDemoEntities dbc = new EFDemoEntities())

{

ProductModel delProdModels = (from p in dbc.ProductModels

where p.ProductModelID == 129

select p).Single();

//Delete

dbc.ProductModels.Remove(delProdModels);

//Save the changes to the database

dbc.SaveChanges();

 

}

The following SQL would be produced, excluding the initial select to find the row:

-- Region Parameters

DECLARE@0Int= 129

-- EndRegion

delete[SalesLT].[ProductModel]

where ([ProductModelID]=@0)

Conclusion

CRUD operations are extremely important. Using Entity Framework makes that job just a little easier. The thing we have to keep in mind is that we are not programming against a database but against the Entity Model even though our final result will affect the database.

Related Reading:

An Introduction to Entity Framework 5

The Difference between IQueryable and IEnumerable

Tags:
Categories:
blog comments powered by Disqus
 
Blog Updates Via E-mail
 Blog Updates Via E-mail
Minimize

Do you want to receive blog updates via e-mail. Then just click on the link below. You will be redirected to Google's feed burner, where you can fill out a form. Supplying your e-mail address.

The subscription is managed entirely by Google's Feedburner. We cannot and do not collect your email address.

Subscribe to The Brave Programmer by Email

Print  
 

 

Latest Comments
 Latest Comments
Minimize
Powered by Disqus

Sign up with Disqus to enjoy a  surprise box of features

Print  
 
Blog Roll
 Blog Roll
Minimize
Print  
 
Categories
 Categories
Minimize
Print  
 
<h1>Search Blogs From The Brave Programmer</h1>
 

Search Blogs From The Brave Programmer

Minimize
Print  
 
Archive
 Archive
Minimize
Archive
<August 2017>
SunMonTueWedThuFriSat
303112345
6789101112
13141516171819
20212223242526
272829303112
3456789
Monthly
Go
Print  
 
<h1>News Feeds (RSS)</h1>
 

News Feeds (RSS)

Minimize
Print  
 

Follow robertbravery on Twitter

Blog Engage Blog Forum and Blogging Community, Free Blog Submissions and Blog Traffic, Blog Directory, Article Submissions, Blog Traffic

View Robert Bravery's profile on LinkedIn

Mybyte

 

Robert - Find me on Bloggers.com

Tags
 Tags
Minimize
Print  
 
Contact Us Now
 Contact Us Now
Minimize
 

Email  us now or call us on 082-413-1420,  to host your website.

We design and develop websites. We develop websites that make a difference. We do Dotnetnuke Module development.

Web Masters Around The World
Power By Ringsurf
Print