Written 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:

SELECT TOP (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 @0 NVarChar(50) = 'New Product Name'

DECLARE @1 Int = 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 @0 NVarChar(50) = 'NewProductName'

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

DECLARE @2 DateTime2 = '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 @0 Int = 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

Trackback Print

Tags:

Categories:

Location: Blogs The Brave Programmer