Written by:
2013/04/09 09:53 AM 

Entity Framework 5 is the latest release of Microsoft ADO.Net data access. As with all data access frameworks, the prime purpose is to access and manipulate data. Entity Framework would be useless as a data access framework if it could not do so.

In my previous post on Entity Framework and CRUD operations I go through Create, Retrieve, Update and Delete operations using Linq to Entities and Entity Framework. In this article I will show how with Entity Framework we can perform multiple CRUD operations at once, allowing Entity framework to decide what to do, how to do it, and which operation to do first.

Create, Retrieve, Update and Delete

The challenge is that you have to update a row, create a new row and also delete a row. Traditionally we think of all three of these operations independently and rightly so. But also we would update the database independently three times. But in Entity Framework we are not working against the database so there is no need to think in database terms. So with Entity Framework 5 we can perform all three operations and only call the save changes once. By doing this we leave all the database decisions to Entity Framework.

First we will find a row and then update it

            //Update

            Customer cust = GetCustomer(30126);

            cust.LastName = "CrudSurname";

Without saving to the database, we will now delete a row

            //Delete Customer

            var delCustomer = GetCustomer(30136);

            if (delCustomer != null)

                etx.Customers.Remove(delCustomer);

Then without saving the data again, we add a new row

            //Add Customer

            Customer cust2 = new Customer();

            cust2.CompanyName = "Crud Add Company Name";

            cust2.EmailAddress = "company@u.com";

            cust2.FirstName = "FirstName";

            cust2.LastName = "LastName";

            cust2.ModifiedDate = DateTime.Today;

            cust2.NameStyle = false;

            cust2.rowguid = Guid.NewGuid();

            cust2.PasswordHash = "L/Rlwxzp4w7RWmEgXX+/A7cXaePEPcp+KwQhl2fJL7w=";

            cust2.PasswordSalt = "1KjXYs4=";

            cust2.Suffix = "JR";

            //Add to dbcontext

            etx.Customers.Add(cust2);

Now we call save changes once and Entity Framework takes care of update, deleting and adding all the data for us.

            etx.SaveChanges();

Is there some magic happening here that we are not aware of? Well not really. Remember that we are not coding against a database, so do not think in database terms. Think in data model terms, in classes, entities and collections. Another important thing to remember is that the dbContext SaveChanges is wrapped in a transaction. So if one operation fails they all fail. Let's look the SQL to see how Entity Framework handles the SQL.

-- Region Parameters

DECLARE @0 NVarChar(50) = 'UpdateCrudSurname'

DECLARE @1 Int = 30126

DECLARE @2 NVarChar(50) = 'CrudSurname'

-- EndRegion

update [SalesLT].[Customer]

set [LastName] = @0

where (([CustomerID] = @1) and ([LastName] = @2))

GO

-- Region Parameters

DECLARE @0 Int = 30136

DECLARE @1 NVarChar(50) = 'LastName'

-- EndRegion

delete [SalesLT].[Customer]

where (([CustomerID] = @0) and ([LastName] = @1))

GO

-- Region Parameters

DECLARE @0 Bit = 0

DECLARE @1 NVarChar(50) = 'FirstName'

DECLARE @2 NVarChar(50) = 'LastName'

DECLARE @3 NVarChar(10) = 'JR'

DECLARE @4 NVarChar(128) = 'Crud Add Company Name'

DECLARE @5 NVarChar(50) = 'company@u.com'

DECLARE @6 VarChar(128) = 'L/Rlwxzp4w7RWmEgXX+/A7cXaePEPcp+KwQhl2fJL7w='

DECLARE @7 VarChar(10) = '1KjXYs4='

DECLARE @8 UniqueIdentifier = 'd21ff719-2d7e-436a-b2b9-2c1702f3e31e'

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

-- EndRegion

insert [SalesLT].[Customer]([NameStyle], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [CompanyName], [SalesPerson], [EmailAddress], [Phone], [PasswordHash], [PasswordSalt], [rowguid], [ModifiedDate])

values (@0, null, @1, null, @2, @3, @4, null, @5, null, @6, @7, @8, @9)

select [CustomerID]

from [SalesLT].[Customer]

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

Baring the SQL statements that retrieve the correct rows to delete and update we can see that Entity Framework actually creates the three SQL statements for us. By doing we do not have to deal with or worry about the actual SQL that will eventually accomplish the task.

Related Reading:

• Entity Framework and CRUD operations
• An Introduction to Entity Framework 5
• The Difference between IQueryable and IEnumerable

Trackback Print

Tags:

Categories:

Location: Blogs The Brave Programmer