Добавление данных в БД Entity Framework

Ошибка: Нарушено "PK_PassportSystem_SZI_1" ограничения PRIMARY KEY. Не удается вставить повторяющийся ключ в объект "dbo.PassportSystem_SZI". Повторяющееся значение ключа: (3598, 20, -).Выполнение данной инструкции было прервано.

Ошибка: Нарушено "PK_PassportSystem_SZI_1" ограничения PRIMARY KEY. Не удается вставить повторяющийся ключ в объект "dbo.PassportSystem_SZI". Повторяющееся значение ключа: (3598, 20, -).Выполнение данной инструкции было прервано.

    public static LicMngrEntities Db { get; set; }
    static Class_EntityConnection()
    {
        LicMngrEntities _db = new LicMngrEntities();
        Db = _db;
    }

       public static int Insert<TEntity>(TEntity entity) where TEntity : class
    {
        Db.Database.Log = (s => System.Diagnostics.Debug.WriteLine(s));
        try
        {
            Db.Entry(entity).State = EntityState.Added;
            Db.SaveChanges();
            return 0;
        }
        catch (Exception ex)
        {
            MessageBox.Show("Ошибка при добавлении записи в БД" + ex.Message); return -1;
        }
    }

     PassportSystem_SupplyItem passSystemSuppItem = new PassportSystem_SupplyItem
      {
          id_SupItem_PassSys_SupItem = Convert.ToInt32(cbSelectSuppItem.SelectedValue),
          idSystem_PassSys_SupItem = IdSystem,
          serialNumberPassSys_SupItem = tbSerialNumb.Text,
          countPassSys_SupItem = Convert.ToInt32(tbCount.Text),
          dateEndPassSys_SupItem = Convert.ToDateTime(dpDateEnd.Text).Date,
          dateStartPassSys_SupItem = Convert.ToDateTime(dpDateStart.Text).Date,
          dateSupplyPassSys_SupItem = Convert.ToDateTime(dpDateSupply.Text).Date,
          notePassSys_SupItem = tbNote.Text
       };
       Class_EntityConnection.Insert(passSystemSuppItem);

    Запущенная транзакция в 17.07.2018 18:30:39 +03:00
    INSERT [dbo].[PassportSystem_SZI]([idSZI_PassSys_SZI],         [idSystem_PassSys_SZI], [serialNumberPassSys_SZI], [countPassSys_SZI], [dateStartPassSys_SZI], [dateEndPassSys_SZI], [dateSupplyPassSys_SZI], [notePassSys_SZI])VALUES (@0, @1, @2, @3, @4, @5, @6, @7)
    -- @0: '3598' (Type = String, Size = 20)
    -- @1: '20' (Type = Int32)
    -- @2: '9999' (Type = String, Size = 50)
    -- @3: '9' (Type = Int32)
    -- @4: '29.06.2018 0:00:00' (Type = DateTime2)
    -- @5: '29.06.2018 0:00:00' (Type = DateTime2)
    -- @6: '29.06.2018 0:00:00' (Type = DateTime2)
    -- @7: '' (Type = String, Size = 255)
    -- Выполнение в 17.07.2018 18:30:39 +03:00
    -- Выполнено за 15 мс. Результат: 1

Запущенная транзакция в 17.07.2018 18:30:53 +03:00
INSERT [dbo].[PassportSystem_SZI]([idSZI_PassSys_SZI], [idSystem_PassSys_SZI], [serialNumberPassSys_SZI], [countPassSys_SZI], [dateStartPassSys_SZI], [dateEndPassSys_SZI], [dateSupplyPassSys_SZI], [notePassSys_SZI])VALUES (@0, @1, @2, @3, @4, @5, @6, @7)
-- @0: '3598' (Type = String, Size = 20)
-- @1: '20' (Type = Int32)
-- @2: '-' (Type = String, Size = 50)
-- @3: '9' (Type = Int32)
-- @4: '29.06.2018 0:00:00' (Type = DateTime2)
-- @5: '29.06.2018 0:00:00' (Type = DateTime2)
-- @6: '29.06.2018 0:00:00' (Type = DateTime2)
-- @7: '' (Type = String, Size = 255)
-- Выполнение в 17.07.2018 18:30:53 +03:00
-- Сбой через 20 мс. Ошибка: Нарушено "PK_PassportSystem_SZI_1" ограничения PRIMARY KEY. Не удается вставить повторяющийся ключ в объект "dbo.PassportSystem_SZI". Повторяющееся значение ключа: (3598, 20, -).Выполнение данной инструкции было прервано.

Дело в том, что DbContext реализует паттерн Unit of Work (Единица Работы). Паттерн используется для представления бизнес-транзакций на уровне предметной области.

Единица работы копит изменения, которые должны быть сделаны в базе данных и затем сохраняет их все вместе. В DbContext для регистрации добавления/удаления применяют методы DbSet.Add и DbSet.Delete, а для сохранения — метод SaveChanges.

Здесь и кроется корень проблемы. Если при сохранении возникает исключение, все объекты помеченные на вставку, обновление или удаление, таковыми и остаются. При следующем сохранении DbContext попробует снова сохранить их в базе данных.

Теперь о том, как исправить ситуацию. Во-первых, надо понимать, что время жизни DbContext должно быть коротким, чтобы ошибка в одной записи не приводила к ошибкам в других записях. К счастью, расходы на создание объектов DbContext невелики: Entity Framework при первом создании DbContext строит схему отображения базы данных на объекты и сохраняет её, поэтому все последующие контексты создаются очень быстро.

Вставлять запись лучше через метод Add:

public class MyDbContext : DbContext
{
    public virtual DbSet<SupplyItem> SupplyItems { get; set; }
}
…
public void Add(IEnumerable<SupplyItem> items)
{
    foreach (var item in items)
    {
        using (var dbContext = new MyDbContext())
        {
            dbContext.Add(item);
            dbContext.SaveChanges();
        }
    }
}

В вашем случае DbContext надо создавать для каждой записи. Но есть и другие способы. Если записей не очень много, в пределах нескольких сотен, вы можете прочитать их в память, и уже в памяти определить, какие их них новые, а какие существующие. Если записей много, ту же самую операцию можно сделать постраничной.

public void Add(IEnumerable<SupplyItem> items)
{
    using (var dbContext = new MyDbContext())
    {
        var oldItems = dbContext.SupplyItems.ToList();
        var itemsToInsert = items.Except(oldItems);
        var itemsToUpdate = from oldItem in oldItems
                            join newItem in items
                              on newItem.Id equals item.Id
                            select new { New = item, Old = oldItem };
        dbContest.SupplyItems.AddRange(itemsToInsert);
        foreach (var item in itemsToUpdate)
        {
            item.Old.SerialNumber = item.New.SerialNumber;
            …
        }
        dbContext.SaveChanges();
    }
}

Поздняя вставка В комментариях написали, что эта схема будет работать только если вставка выполняется с одного клиента, а несколько клиентов будут друг другу мешать. Это верно. К сожалению, эта задача плохо решается даже не на уровне Entity Framework, а на уровне непосредственно БД. Даже если писать на чистом SQL, вставка/изменение большого количества записей с разных клиентов будет выполняться строго последовательно (как пишет Фаулер, в этом случае применяется паттерн Пессимистическая блокировка).

Такие задачи решают не техническими, а организационными средствами. В моей практике был такой опыт, и там вставкой/обновлением занимался единственный клиент, фоновый процесс, который запускался несколько раз в день. Все остальные клиенты (рабочие места операторов) большую часть данных только читали, поэтому и проблем с блокировками было гораздо меньше. Конец вставки

Можно также в явном виде вызывать SQL-запрос, который на сленге программистов называется UPSERT. Если запись уже есть, то это будет UPDATE, а если её нет, то INSERT.

Для SQL Server запрос UPSERT делается с помощью команды MERGE.

EF напрямую не поддерживает инструкцию MERGE, но он позволяет выполнять SQL-запросы. В новом EF Core это может быть сделано так:

public void Add(IEnumerable<SupplyItem> items)
{
    using (var dbContext = new MyDbContext())
    {
        foreach (var item in items)
        {
            dbContext.Database
                     .ExecuteSqlCommand(@"MERGE dbo.SupplyItems AS Target
                                          USING (SELECT @Id, @SerialNumber) AS Source (Id, SerialNumber)
                                          ON Source.Id = Target.Id
                                          WHEN MATCHED THEN
                                            UPDATE SET SerialNumber = Source.SerialNumber
                                          WHEN NON MATCHED THEN
                                            INSERT (Id, SerialNumber)
                                            VALUES (Source.Id, Source.SerialNumber)",
                                        new SqlParameter("@Id", item.Id),
                                        new SqlParameter("@SerialNumber", item.SerialNumber);
        }
    }
}

Недостатком такого подхода является то, что перечислять все параметры придётся вручную. Мы не можем воспользоваться средствами EF для генерации запроса. При изменении схемы мы можем просто забыть внести изменения и здесь тоже.

Вторым недостатком является не очень высокая скорость внесения изменений. Чтобы ускорить, можно сделать групповую вставку во временную таблицу и затем вставлять/обновлять данные уже из неё.

Плюсом будет то, что такой метод исключает конфликт идентификаторов, даже если вставка происходит с разных клиентов, и при этом не сильно блокирует систему.

public class DefaultContext: DbContext
{
    public DefaultContext(DbConnection connection)
        :base(connection, true)
    {
    }
    public DbSet<Worker> Workers { get; set; }
}
public class Worker
{
    public int Id { get; set; }
    [Index(IsUnique = true)]
    public string Name { get; set; }
}

Я сымитирую похожую ошибку, нарушение индекса.

DbConnection connection = Effort.DbConnectionFactory.CreateTransient();            
using (DefaultContext _context = new DefaultContext(connection))
{
    var worker = new Worker { Name = "Ivan" };
    _context.Entry(worker).State = EntityState.Added;
    _context.SaveChanges();
    _context.Entry(worker).State = EntityState.Detached;
    try
    {
        Console.WriteLine("from try");
        _context.Entry(worker).State = EntityState.Added;
        _context.SaveChanges();
    }
    catch (DbUpdateException dbEx)
    {
        var errors = dbEx.Entries.Select(x=>x.Entity).ToList();
        foreach(var entity in errored)
        {
            //Убираем из контекста ошибочные сущности
            _context.Entry(entity).State = EntityState.Detached;
        }
        //Сохраняем
        _context.SaveChanges();
    }
}

В данном примере я добавляю в бд одну запись, после этого пытаюсь добавить запись которая будет нарушать index.

В блоке catch получаем строки которые вызвали ошибку при сохранении (dbEx.Entries.Select(x=>x.Entity)), убираем данные строки из контекста (_context.Enty(entity).State = EntityState.Detached) после этого сохраняем/оповещает пользователя

Прикладываю скорректированный код:

    public static int Insert<TEntity>(TEntity entity) where TEntity : class
    {
        using (var _db = new LicMngrEntities())
        {
            _db.Database.Log = (s => System.Diagnostics.Debug.WriteLine(s));
            try
            {
                _db.Entry(entity).State = EntityState.Added;
                _db.SaveChanges();
                return 0;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Ошибка при добавлении записи в БД" + ex.Message); return -1;
            }
        }
    }

И код отправки на сохранение:

    PassportSystem_SupplyItem passSystemSuppItem = new PassportSystem_SupplyItem
    {
      id_SupItem_PassSys_SupItem = Convert.ToInt32(cbSelectSuppItem.SelectedValue),
      idSystem_PassSys_SupItem = IdSystem,
      serialNumberPassSys_SupItem = tbSerialNumb.Text,
      countPassSys_SupItem = Convert.ToInt32(tbCount.Text),
      dateEndPassSys_SupItem = Convert.ToDateTime(dpDateEnd.Text).Date,
      dateStartPassSys_SupItem = Convert.ToDateTime(dpDateStart.Text).Date,
      dateSupplyPassSys_SupItem = Convert.ToDateTime(dpDateSupply.Text).Date,
      notePassSys_SupItem = tbNote.Text
      };
      if (Class_EntityConnection.FindPassSuppItem(passSystemSuppItem.idSystem_PassSys_SupItem, passSystemSuppItem.id_SupItem_PassSys_SupItem, passSystemSuppItem.serialNumberPassSys_SupItem) != null)
       {
        var result = MessageBox.Show("Данная запись уже существует в таблице, хотите ее обновить?", "Обновление записи в БД", MessageBoxButton.YesNo, MessageBoxImage.Information);
        if (result == MessageBoxResult.Yes)
              Class_EntityConnection.Update(passSystemSuppItem);
        }
      else
      Class_EntityConnection.Insert(passSystemSuppItem);

Код поиска по БД:

    public static View_PassportSystems_SupplyItem FindPassSuppItem(int idSystem, int idSuppItem, string serialNumb)
    {
        using (var _db = new LicMngrEntities())
        {
            return _db.View_PassportSystems_SupplyItem.Find(idSuppItem, idSystem, serialNumb);
        }
    }

P.S. Ответы на вопросы по применению контекста будут добавлены после разбора темы у Тролсена

Насчет автогенерируемого ключа в EF, его

   public static int SaveCustomerToDB(Customer _newCustomer)
    {
        try
        {
            Db.Customer.Add(_newCustomer); //добавляем объект к контексту
            Db.SaveChanges(); 
            return _newCustomer.idCustomer; //возвращает автогенерируемы ключ
        }
        catch (Exception ex)
            { MessageBox.Show("Ошибка добавления нового заказчика в базу данных!" + ex.Message); return -1; }
     }