Consider the following diagram, where there is one to many relationship between the Employee and Assest table. Here 1 Employee can have M Assests.
Lets say an Employee has 2 assests namely –
So there are 3 things to be done when updating this data using the Entity Framework
- Look for items present in the newlist and missing in the oldlist, these items are to be added. The item “Car” in this example.
- Look for items present in the oldlist and missing in the newlist, these items are to be removed. The item “Bike” in this example.
- And finally the items that are present in both the list. The item “Home” in this example, is to be untouched.
The following code uses ExceptBy extension method to compare two lists and return based on the key passed to it.
public void UpdateEmployeeAssests(int employeeId, List<EmpAssets> updatedAssests)
using (var context = new SampleDbEntities())
List<EmpAssets> oldAssests = context.EmpAssets.Where(x => x.EmployeeId == employeeId).ToList();
List<EmpAssets> addedAssests = updatedAssests.ExceptBy(oldAssests, x => x.CityId).ToList();
List<EmpAssets> deletedAssests = oldAssests.ExceptBy(updatedAssests, x => x.CityId).ToList();
deletedAssests.ForEach( x => context.Entry(x).State = EntityState.Deleted);
addedAssests.ForEach(x => context.Entry(x).State = EntityState.Added);
Step 1 : Download and Install
First, you will need to download and install the following
Step 2 : Create A Database To Connect To
Make sure you create a database and a table with few columns for testing the connectivity using EF. In my example I have a ‘UserProfile’ table which has fields like userId, email, firstName, lastName etc.
Step 3 : Add Reference To Your Project
Add MySql.Data.dll and MySql.Data.Entity.dll to your project.
Step 4 : Add ADO.NET Data Entity Model
Step 5 : Time To Test
public User CreateUser(User model)
using (var context = new TestMySqlDbEntities())
// add user
var createdUser = context.Users.Add(model);
Today I kept getting this error in my Razor View. The error message said,
Could not load file or assembly ‘System.Data.Entity, Version=220.127.116.11, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies. The system cannot find the file specified
To use an external Entity Framework model, (for example : embedded in a dll as a reference) with ASP.NET MVC 3/4 you must :
- Add the following reference to your MVC project : System.Data.Entity (Version 18.104.22.168, Runtime v4.0.30319)
- Add the following line in your web.config
<compilation debug="true" targetFramework="4.0">
<add assembly="System.Data.Entity, Version=22.214.171.124, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
This solved my problem. Hope it does yours too. Cheers !
You can call Stored Procedure and Views using Entity Framework, I recently had to use one and didn’t find any good article describing this, so I am writing this one down.
Below is a Stored Procedure I have prepared for this example. As you can see in this stored procedure, there is 1 input parameter and 1 output parameter. I have tried to keep the stored proc as simple as possible :)
CREATE PROCEDURE SP_TEST_EF
@total INT OUTPUT
SELECT * FROM dbo.Users WHERE username = @username
SET @total = (SELECT COUNT(*) FROM dbo.Users)
Now that we have defined our stored proc, let move towards our next step.
Open your .edmx file and right click on the designer and select the “Update Model From Database”
then select the new stored procedure added from the list and select “Finish”.
Now right click on the designer again, and select the “Add -> Function Import” option this time.
Select the stored procedure from the list of Stored Procedures, give a nice function name I have given mine “Test_EF” now as I have my stored proc return a value I have used Complex Type. To create a complex type simply click on the “Create a New Complex Type” and Visual Studio will create one for you. Click ok.
And so if all is ok, you should be able to see the function added to your model browser as shown in the screenshot below.
And now you can use the stored procedure like any other table call,
using(var context = new SimplePmEntities())
var data = context.Test_EF("yasser", 0);
Hope you found this useful ! Cheers !