I am trying to implement entity framework 6 in my application, but I have a problem performing updates on the records.

If I have 2 records in the database, lets say:

Then I alter the one with id 1 from "Jason" to "Water" and call the UpdatePerson function, with a new Person object that has the same primary key.

The result will be:

WHY should that be the result?? I was already looking around for a solution but could not find any clue. Anyone an idea what I am doing wrong?

As far as I understood the disconnected datacontext I used, can simply update a record with the knowledge of the primary key.

For reference the page for EF6

My code looks like this:

public class Person
{
    private int _id = -1;
    private string _name;
    private string _lastname;

    public int PersonId { get => _id; set => _id = value; }
    [Required]
    [MaxLength(255)]
    public string Name { get => _name; set => _name = value; }
    [Required]
    public string Lastname { get => _lastname; set => _lastname = value; }
}

DbContext:

public partial class Model1 : DbContext
{
    public Model1() : base("name=entity_test") { }

    public DbSet<Person> People { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Person>().MapToStoredProcedures();
    }     
}

public class PersonModel
{
    public ObservableCollection<Person> GetPeople()
    {
        using (Model1 context = new Model1())
        {
            var list = context.People.AsNoTracking().ToList();
            if (list == null)
                return null;
            return new ObservableCollection<Person>(list);
        }
    }

    public void AddPerson(Person person)
    {
        using (Model1 context = new Model1())
        {
            context.People.Add(person);
            context.SaveChanges();
        }
    }

    public void UpdatePerson(Person person)
    {
        using (Model1 context = new Model1())
        {
            context.Entry(person).State = EntityState.Modified;
            context.SaveChanges();
        }
    }
}

EDIT

Tables were not displayed nicely.

EDIT 2

Here the rest of the code and the output of context.Database.Log = s => Console.WriteLine(s);

Output:

`Person_Update`


-- PersonId: '1' (Type = Int32, IsNullable = false)

-- Name: 'Water' (Type = String, IsNullable = false, Size = 5)

-- Lastname: 'Momoa' (Type = String, IsNullable = false, Size = 5)

-- Executing at 29.10.2021 16:46:05  02:00

-- Completed in 198 ms with result: 2

Code:

public class NotifyBase : INotifyPropertyChanged
{
    public event PropertyChangedEventHandler PropertyChanged;
    protected bool SetProperty<T>(ref T field, T newValue, [CallerMemberName] string propertyName = null)
    {
        if (!EqualityComparer<T>.Default.Equals(field, newValue))
        {
            field = newValue;
            PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
            return true;
        }
        return false;
    }
}
public partial class MainWindow : Window
{
    public MainWindow()
    {
        InitializeComponent();
        DataContext = new ViewModel();

    }
    private void Button_Click(object sender, RoutedEventArgs e)
    {
        PersonModel model = new PersonModel();

        if (DataContext is ViewModel vm)
        {
            vm.AddModifyPerson();
        }
    }
}

public class ViewModel : NotifyBase
{
    public ViewModel()
    {
        MiniProfilerEF6.Initialize();
        model = new PersonModel();

        using (var db = new Model1())
        {
            // create if not exists
            if (db.Database.CreateIfNotExists())
            {
                Console.WriteLine();
            }
            People = model.GetPeople();
        }
    }

    private PersonModel model;

    private ObservableCollection<Person> people = new ObservableCollection<Person>();
    private Person currentPerson = new Person();

    public ObservableCollection<Person> People { get => people; set => SetProperty(ref people, value); }
    public Person CurrentPerson { get => currentPerson; set => SetProperty(ref currentPerson, value); }

    public void AddModifyPerson()
    {
        if (CurrentPerson.PersonId == -1)
        {
            model.AddPerson(CurrentPerson);
        }
        else
        {
            model.UpdatePerson(
                new Person()
                {
                    PersonId = CurrentPerson.PersonId,
                    Lastname = CurrentPerson.Lastname,
                    Name = CurrentPerson.Name,
                });
        }
        People = model.GetPeople();
    }
}

Edit 3

Code for from miniprofiler

    public void UpdatePerson(Person person)
    {
        var profiler = MiniProfiler.StartNew("My Profiler");
        using (MiniProfiler.Current.Step("Update_Sql"))
        {
            using (Model1 context = new Model1())
            {
                context.Entry(person).State = EntityState.Modified;
                context.SaveChanges();
            }
        }

        Console.WriteLine(MiniProfiler.Current.RenderPlainText());
    }

EDIT 4

output of an update call from mysql.general_log

It does look like the update procedure is not performing the where clause. If I run call Person_Update_Custom(1, 'test','tes') in the workbench all rows are updated. Here is the stored procedure:

    CREATE DEFINER=`root`@`localhost` PROCEDURE `Person_Update`(IN PersonId int,IN Name varchar(255) ,IN Lastname longtext)
    BEGIN 
    UPDATE `People` SET `Name`=Name, `Lastname`=Lastname WHERE `PersonId` = PersonId;
     END

CodePudding user response：

So after seeing in the miniprofiler (thanks Armando Bracho) and the mysql log (thanks Steve Py) that there was always one single sql query and Gert Arnold pointing out the procedure could be failing, I focused on the procedure.

It looks like, the procedure mixes the PersonId column name with the PersonId variable defined in the CREATE PROCEDURE-header.

So I've added some code to define the update procedure parameters by hand.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.Entity<Person>().MapToStoredProcedures(p => p.Update(pp => pp.HasName("Person_Update").Parameter(pm => pm.Name, "db_Name").Parameter(pm => pm.Lastname, "db_Lastname").Parameter(pm => pm.PersonId, "db_PersonId")));
}      

which changes the stored procedure to:

CREATE DEFINER=`root`@`localhost` PROCEDURE `Person_Update`(IN db_PersonId int,IN db_Name varchar(255) ,IN db_Lastname longtext)
BEGIN 
UPDATE `People` SET `Name`=db_Name, `Lastname`=db_Lastname WHERE `PersonId` = db_PersonId;
 END

and this finally works! One row affected.

EDIT

It's enough to change the MapToStoredProcedures to

modelBuilder.Entity<Person>().MapToStoredProcedures(p => p.Update(pp => pp.HasName("Person_Update").Parameter(pm => pm.PersonId, "db_PersonId")));

this ways one does not have define it for all properties.