物體框架6使用更新的資料更新所有記錄

我正在嘗試在我的應用程式中實作物體框架 6，但是我在執行記錄更新時遇到問題。

如果我在資料庫中有 2 條記錄，讓我們說：

然后我將 id 為 1 的那個從“Jason”更改為“Water”并UpdatePerson使用Person具有相同主鍵的新物件呼叫該函式。

結果將是：

為什么會是這樣的結果？？我已經在四處尋找解決方案，但找不到任何線索。任何人都知道我做錯了什么？

據我了解我使用的斷開連接的資料背景關系，可以簡單地使用主鍵的知識更新記錄。

供參考EF6的頁面

我的代碼如下所示：

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

編輯

表沒有很好地顯示。

編輯 2

這里的其余代碼和輸出 context.Database.Log = s => Console.WriteLine(s);

輸出：

`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

代碼：

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

編輯 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

uj5u.com熱心網友回復：

因此，在 miniprofiler（感謝 Armando Bracho）和 mysql 日志（感謝 Steve Py）中看到始終只有一個 sql 查詢并且 Gert Arnold 指出該程序可能失敗后，我專注于該程序。

看起來，該程序將PersonId列名與-header 中PersonId定義的變數混合在一起CREATE PROCEDURE。

所以我添加了一些代碼來手動定義更新程序引數。

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

它將存盤程序更改為：

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

這終于奏效了！一排受影響。

編輯

改變為MapToStoredProcedures就足夠了

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

這種方式沒有為所有屬性定義它。

標籤：c# entity-framework entity-framework-6

上一篇：如何將SQL陳述句轉換為LINQ？

下一篇：自定義型別的名稱，即使是帶有可變引數的模板