Search code examples
c#linq-to-entitiessavechanges

Linq to Entities and update records

I have the following structure:

public interface IChainByPreviousId
{
    int Id { get; set; }
    int? PreviousDeviceId { get; set; }
}

public class RegisteredDevice : IChainByPreviousId
{
    public int Id { get; set; }

    public int? PreviousDeviceId { get; set; }
    public int? Position { get; set; }

    public string DeviceName { get; set; }
    public string ModelNumber { get; set; }

    public virtual RegisteredDevice? PreviousDevice { get; set; }
}

so, as we can see, every device can have previous device or null (if it's the first device in the our ordered list)

        builder.HasOne(a => a.PreviousDevice)
            .WithOne()
            .HasForeignKey<RegisteredDevice>(p => p.PreviousDeviceId)
            .OnDelete(DeleteBehavior.NoAction);

then I want to "reorder" devices. model.DeviceReordered is collection of

public class DeviceReorderedDto
{
    public int DeviceId { get; set; }
    public int? NewPreviousDeviceId { get; set; }
}

so, this class describes reordering device (new previous deviceId)

for my case there are (moved device with Id == 2 from second position (after id=1) to devices between id == 4 and id == 5) :

[
  {
    "DeviceId": 3,
    "NewPreviousDeviceId": 1
  },
  {
    "DeviceId": 2,
    "NewPreviousDeviceId": 4
  },
  {
    "DeviceId": 5,
    "NewPreviousDeviceId": 2
  }
]

get devices:

var localDevices = _dataContext.RegisteredDevices.ToList();

init localDevices (after get data and before applying changes) is the following:

[
  {
    "Id": 1,
    "ModelNumber": "20I",
    "CompanyName": "J. J. Keller \u0026 Associates, Inc.",
    "DeviceName": "J. J. Keller ELD - iOS 2.0",
    "PreviousDeviceId": null,
    "Position": 0
  },
  {
    "Id": 2,
    "ModelNumber": "25I",
    "CompanyName": "J. J. Keller \u0026 Associates, Inc.",
    "DeviceName": "J. J. Keller ELD - iOS 2.5",
    "PreviousDeviceId": 1,
    "Position": 1
  },
  {
    "Id": 3,
    "ModelNumber": "FLT3",
    "CompanyName": "HOS247 LLC",
    "DeviceName": "#1 ELD by HOS247",
    "PreviousDeviceId": 2,
    "Position": 2
  },
  {
    "Id": 4,
    "ModelNumber": "N775G",
    "CompanyName": "XPERT-IT SOLUTIONS INC.",
    "DeviceName": "Xpert ELD",
    "PreviousDeviceId": 3,
    "Position": 3
  },
  {
    "Id": 5,
    "ModelNumber": "PMG001",
    "CompanyName": "PeopleNet",
    "DeviceName": "PeopleNet Mobile Gateway - Trimble Driver ELD",
    "PreviousDeviceId": 4,
    "Position": 4
  }
]

and applying changes:

for (int i = 0; i < model.DeviceReordered.Count; i++)
{
   var item = model.DeviceReordered[i];
   var device = localDevices.Where(a => a.Id == item.DeviceId).First();
   var previousDevice = localDevices.Where(a => a.Id == item.NewPreviousDeviceId).FirstOrDefault();
   device.PreviousDevice = previousDevice;
}
localDevices = localDevices.OrderBy(a => a.Position).ToList();

and save changes:

        await _dataContext.SaveChangesAsync();

I got error:

Cannot insert duplicate key row in object 'dbo.RegisteredDevice' with unique index 'IX_RegisteredDevice_PreviousDeviceId'. The duplicate key value is (4).

But I don't have duplicates in localDevices Ok, before calling await _dataContext.SaveChangesAsync(); I add: ValidateDuplicates(list);

    void ValidateDuplicates(IList<T> positionList)
    {
        var duplicates = positionList
            .Where(x => x.PreviousDeviceId.HasValue)
            .GroupBy(x => x.PreviousDeviceId)
                                        .Where(g => g.Count() > 1)
                                        .Select(x => x.Key).ToList();
        if (duplicates.Any())
            throw new PositionReorderDuplicateException(duplicates);
    }

none duplicate. Ok, check again:

var prev = localDevices.Where(a => a.PreviousDeviceId == 4).ToList();

prev has only one record.

My localDevices before call await _dataContext.SaveChangesAsync():

[
  {
    "Id": 1,
    "ModelNumber": "20I",
    "CompanyName": "J. J. Keller \u0026 Associates, Inc.",
    "DeviceName": "J. J. Keller ELD - iOS 2.0",
    "PreviousDeviceId": null,
    "Position": 0
  },
  {
    "Id": 3,
    "ModelNumber": "FLT3",
    "CompanyName": "HOS247 LLC",
    "DeviceName": "#1 ELD by HOS247",
    "PreviousDeviceId": 1,
    "Position": 1
  },
  {
    "Id": 4,
    "ModelNumber": "N775G",
    "CompanyName": "XPERT-IT SOLUTIONS INC.",
    "DeviceName": "Xpert ELD",
    "PreviousDeviceId": 3,
    "Position": 2
  },
  {
    "Id": 2,
    "ModelNumber": "25I",
    "CompanyName": "J. J. Keller \u0026 Associates, Inc.",
    "DeviceName": "J. J. Keller ELD - iOS 2.5",
    "PreviousDeviceId": 4,
    "Position": 3
  },
  {
    "Id": 5,
    "ModelNumber": "PMG001",
    "CompanyName": "PeopleNet",
    "DeviceName": "PeopleNet Mobile Gateway - Trimble Driver ELD",
    "PreviousDeviceId": 2,
    "Position": 4
  }
]

So, seems, anything is ok. Reordered as expected, no duplicates, only one device has PreviousDeviceId == 4

My full code is:

        var localDevices = _dataContext.RegisteredDevices.ToList();

        if (model.DeviceReordered.Any())
        {
            for (int i = 0; i < model.DeviceReordered.Count; i++)
            {
                var item = model.DeviceReordered[i];
                var device = localDevices.Where(a => a.Id == item.DeviceId).First();
                var previousDevice = localDevices.Where(a => a.Id == item.NewPreviousDeviceId).FirstOrDefault();
                device.PreviousDeviceId = previousDevice?.Id;
            }
        }

        int? previousId = null;
        var liveRecordsCount = localDevices.Where(a => a.Position.HasValue).Count();

        for (int i = 0; i < liveRecordsCount; i++)
        {
            var device = localDevices.Where(a => a.PreviousDeviceId == previousId).First();
            device.Position = i;
            previousId = device.Id;
        }

        localDevices = localDevices.OrderBy(a => a.Position).ToList();

        await _dataContext.SaveChangesAsync();

compiled project with the issue I added to https://github.com/oshastitko/reordering_problem

What is wrong in my code?

ADDED 29/07/2023

it generates the following SQL code:

exec sp_executesql N'SET NOCOUNT ON;
UPDATE [RegisteredDevice] SET [PreviousDeviceId] = @p0
OUTPUT 1
WHERE [Id] = @p1;
UPDATE [RegisteredDevice] SET [PreviousDeviceId] = @p2
OUTPUT 1
WHERE [Id] = @p3;
UPDATE [RegisteredDevice] SET [PreviousDeviceId] = @p4
OUTPUT 1
WHERE [Id] = @p5;
',N'@p1 int,@p0 int,@p3 int,@p2 int,@p5 int,@p4 int',@p1=5,@p0=2,@p3=2,@p2=4,@p5=3,@p4=1

which can't be executed too:

Msg 2601, Level 14, State 1, Line 2 Cannot insert duplicate key row in object 'dbo.RegisteredDevice' with unique index 'IX_RegisteredDevice_PreviousDeviceId'. The duplicate key value is (2). The statement has been terminated. Msg 2601, Level 14, State 1, Line 5 Cannot insert duplicate key row in object 'dbo.RegisteredDevice' with unique index 'IX_RegisteredDevice_PreviousDeviceId'. The duplicate key value is (4). The statement has been terminated. Msg 2601, Level 14, State 1, Line 8 Cannot insert duplicate key row in object 'dbo.RegisteredDevice' with unique index 'IX_RegisteredDevice_PreviousDeviceId'. The duplicate key value is (1). The statement has been terminated.

as I understand, it's not in transaction (don't know why). How to "cover" it to transaction?

ADDED 29/07/2023 #2

Also, the same code here, for copy/paste to console app:

DataContext.cs:

public class DataContext : DbContext
{
    public DataContext()
    {
    }

    public DataContext(DbContextOptions<DataContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(RegisteredDeviceMap).Assembly);
    }

    public virtual DbSet<RegisteredDevice> RegisteredDevices { get; set; }
}

RegisteredDevice.cs

public interface IChainByPreviousId
{
    int Id { get; set; }
    int? PreviousDeviceId { get; set; }
}

public class RegisteredDevice : IChainByPreviousId
{
    public int Id { get; set; }

    public int? PreviousDeviceId { get; set; }
    public int? Position { get; set; }

    public string DeviceName { get; set; }
    public string ModelNumber { get; set; }

    public virtual RegisteredDevice? PreviousDevice { get; set; }
}

RegisteredDeviceMap.cs

public class RegisteredDeviceMap : IEntityTypeConfiguration<RegisteredDevice>
{
    public void Configure(EntityTypeBuilder<RegisteredDevice> builder)
    {
        builder.ToTable(nameof(RegisteredDevice));
        builder.HasKey(p => p.Id);

        builder.HasOne(a => a.PreviousDevice)
            .WithOne()
            .HasForeignKey<RegisteredDevice>(p => p.PreviousDeviceId)
            .OnDelete(DeleteBehavior.NoAction);

        string data = "[{\"Id\":1,\"ModelNumber\":\"20I\",\"CompanyName\":\"J. J. Keller \\u0026 Associates, Inc.\",\"DeviceName\":\"J. J. Keller ELD - iOS 2.0\",\"PreviousDeviceId\":null,\"Position\":0},{\"Id\":2,\"ModelNumber\":\"25I\",\"CompanyName\":\"J. J. Keller \\u0026 Associates, Inc.\",\"DeviceName\":\"J. J. Keller ELD - iOS 2.5\",\"PreviousDeviceId\":1,\"Position\":1},{\"Id\":3,\"ModelNumber\":\"FLT3\",\"CompanyName\":\"HOS247 LLC\",\"DeviceName\":\"#1 ELD by HOS247\",\"PreviousDeviceId\":2,\"Position\":2},{\"Id\":4,\"ModelNumber\":\"N775G\",\"CompanyName\":\"XPERT-IT SOLUTIONS INC.\",\"DeviceName\":\"Xpert ELD\",\"PreviousDeviceId\":3,\"Position\":3},{\"Id\":5,\"ModelNumber\":\"PMG001\",\"CompanyName\":\"PeopleNet\",\"DeviceName\":\"PeopleNet Mobile Gateway - Trimble Driver ELD\",\"PreviousDeviceId\":4,\"Position\":4}]";

        var devices = (List<RegisteredDevice>)JsonSerializer.Deserialize(data, typeof(List<RegisteredDevice>));
        int id = 1;
        int? previousDeviceId = null;
        foreach (var device in devices)
        {
            builder.HasData(new RegisteredDevice
            {
                Id = id,
                DeviceName = device.DeviceName,
                ModelNumber = device.ModelNumber,
                Position = id - 1,
                PreviousDeviceId = previousDeviceId
            });
            id++;
            previousDeviceId = device.Id;
        }
    }
}

Init migration:

public partial class Init : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "RegisteredDevice",
            columns: table => new
            {
                Id = table.Column<int>(type: "int", nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                PreviousDeviceId = table.Column<int>(type: "int", nullable: true),
                Position = table.Column<int>(type: "int", nullable: true),
                DeviceName = table.Column<string>(type: "nvarchar(max)", nullable: false),
                ModelNumber = table.Column<string>(type: "nvarchar(max)", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_RegisteredDevice", x => x.Id);
                table.ForeignKey(
                    name: "FK_RegisteredDevice_RegisteredDevice_PreviousDeviceId",
                    column: x => x.PreviousDeviceId,
                    principalTable: "RegisteredDevice",
                    principalColumn: "Id");
            });

        migrationBuilder.InsertData(
            table: "RegisteredDevice",
            columns: new[] { "Id", "DeviceName", "ModelNumber", "Position", "PreviousDeviceId" },
            values: new object[,]
            {
                { 1, "J. J. Keller ELD - iOS 2.0", "20I", 0, null },
                { 2, "J. J. Keller ELD - iOS 2.5", "25I", 1, 1 },
                { 3, "#1 ELD by HOS247", "FLT3", 2, 2 },
                { 4, "Xpert ELD", "N775G", 3, 3 },
                { 5, "PeopleNet Mobile Gateway - Trimble Driver ELD", "PMG001", 4, 4 }
            });

        migrationBuilder.CreateIndex(
            name: "IX_RegisteredDevice_PreviousDeviceId",
            table: "RegisteredDevice",
            column: "PreviousDeviceId",
            unique: true,
            filter: "[PreviousDeviceId] IS NOT NULL");
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "RegisteredDevice");
    }
}

Service:

internal interface ISaveDataService
{
    void ApplyChanges(List<DeviceReorderedDto> model);
}

internal class RegisteredDevicesSaveDataService : ISaveDataService
{
    protected readonly DataContext _dataContext;

    public RegisteredDevicesSaveDataService(DataContext dataContext)
    {
        _dataContext = dataContext;
    }

    public void ApplyChanges(List<DeviceReorderedDto> model)
    {
        var localDevices = _dataContext.RegisteredDevices.ToList();
        for (int i = 0; i < model.Count; i++)
        {
            var item = model[i];
            var device = localDevices.Where(a => a.Id == item.DeviceId).First();
            var previousDevice = localDevices.Where(a => a.Id == item.NewPreviousDeviceId).FirstOrDefault();
            device.PreviousDevice = previousDevice;
            //device.PreviousDeviceId = previousDevice?.Id;
        }

        int? previousId = null;
        var liveRecordsCount = localDevices.Where(a => a.Position.HasValue).Count();

        for (int i = 0; i < liveRecordsCount; i++)
        {
            var device = localDevices.Where(a => a.PreviousDeviceId == previousId).First();
            device.Position = i;
            previousId = device.Id;
        }

        localDevices = localDevices.OrderBy(a => a.Position).ToList();

        _dataContext.SaveChanges();
    }
}

Program.cs:

var configuration = new ConfigurationBuilder()
    .AddJsonFile("appsettings.test.json", false, true).Build();

string connectionString = "Server=(local)\\sqlexpress;Database=Test;Trusted_Connection=True;TrustServerCertificate=True;MultipleActiveResultSets=true;";

var services = new ServiceCollection();

services.AddDbContext<DataContext>(options => options
            .UseLazyLoadingProxies()
            .UseSqlServer(connectionString)
            , ServiceLifetime.Transient
            );

services.AddTransient<ISaveDataService, RegisteredDevicesSaveDataService>();

var sp = services.BuildServiceProvider();


var _dataContext = sp.GetService<DataContext>();
var applyService = sp.GetService<ISaveDataService>();

_dataContext.Database.Migrate();
_dataContext.SaveChanges();

List<DeviceReorderedDto> changes = new List<DeviceReorderedDto>
{
      new DeviceReorderedDto{
    DeviceId = 3,
    NewPreviousDeviceId = 1
  },
  new DeviceReorderedDto{
    DeviceId = 2,
    NewPreviousDeviceId = 4
  },
  new DeviceReorderedDto{
    DeviceId = 5,
    NewPreviousDeviceId = 2
  }
};

applyService.ApplyChanges(changes);
Solution

  • I suggest to split the modification into several steps (ideally in a transaction so no inconsistent state can happen) - 1) "clean" the previous device ids for all changed items 2) update the previous device ids 3) recalculate the positions:

    // clean the previous ids
_dataContext.RegisteredDevices
    .Where(device => model.Select(dto => dto.DeviceId).Contains(device.Id))
    .ExecuteUpdate(up => up.SetProperty(device => device.PreviousDeviceId, (int?)null));

// set new ones
var changedDevices = _dataContext.RegisteredDevices
    .Where(device => model.Select(dto => dto.DeviceId).Contains(device.Id))
    .ToList();
for (int i = 0; i < model.Count; i++)
{
    var item = model[i];
    var device = changedDevices.Where(a => a.Id == item.DeviceId).First();
    device.PreviousDeviceId = item.NewPreviousDeviceId;
}
_dataContext.SaveChanges();

// recalculate and update the positions
_dataContext.ChangeTracker.Clear();
var localDevices = _dataContext.RegisteredDevices.ToList();
// ...

    Enable the query logging, as far as I remember EF will send updates by one so you can end up in situation when you are trying to insert a PreviousDeviceId which is still used by someone.

    Submitted PR with code that worked for me.