Entity Framework 5 – CRUD Operations
Apr8Written by:
2013/04/08 04:03 PM 
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
blog comments powered by