Updating one to many relationship tables using Entity Framework 6

Problem Statement

Consider the following diagram, where there is one to many relationship between the Employee and Assest table. Here 1 Employee can have M Assests.

enter image description here

Lets say an Employee has 2 assests namely –

OldList NewList
Home Home
Bike Car

So there are 3 things to be done when updating this data using the Entity Framework

  1. Look for items present in the newlist and missing in the oldlist, these items are to be added. The item “Car” in this example.
  2. Look for items present in the oldlist and missing in the newlist, these items are to be removed. The item “Bike” in this example.
  3. And finally the items that are present in both the list. The item “Home” in this example, is to be untouched.

Code

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);

        context.SaveChanges();
    }
}

References:

How To Use Entity Framework With MySql Database ?

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. enter image description here

Step 4 : Add ADO.NET Data Entity Model

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

Step 5 : Time To Test

public User CreateUser(User model)
{
    using (var context = new TestMySqlDbEntities())
    {
        // add user
        var createdUser = context.Users.Add(model);
        context.SaveChanges();
    }

    return model;
}

ASP.NET MVC View Error – Could not load file or assembly ‘System.Data.Entity’ or one of its dependencies.

Today I kept getting this error in my Razor View. The error message said,

Could not load file or assembly ‘System.Data.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ or one of its dependencies. The system cannot find the file specified

Solution :

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 4.0.0.0, Runtime v4.0.30319)
  • Add the following line in your web.config

config

<compilation debug="true" targetFramework="4.0">  
<assemblies>  
<add assembly="System.Data.Entity, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />  
</assemblies>  
</compilation>

This solved my problem. Hope it does yours too. Cheers !

How to Call Stored Procedure from Entity Framework in ASP.NET MVC

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
@username varchar(50),
@total INT OUTPUT
AS
BEGIN
SELECT * FROM dbo.Users WHERE username = @username
SET @total = (SELECT COUNT(*) FROM dbo.Users)
END
GO

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,

For example:

using(var context = new SimplePmEntities())
{
    var data = context.Test_EF("yasser", 0);
}

Hope you found this useful ! Cheers !