[SqlServer] BulkInsertOrUpdate does not insert in correct order and database IDs not assigned

[nino-s]

Using the newest version (6.4.4) I have the problem that the order of the entities to be inserted is changed. Also the entities don't have the database IDs assigned (see Output section "newValuesFromMemory").

Given the following code:

[HttpGet]
public async Task<IActionResult> CreateStudents()
{
    var entriesToInsertOrUpdate = new List<Student>
    {
        new Student { CourseId = 1, EnrollmentDate = new DateTimeOffset(2020, 1, 1, 10, 0, 0, TimeSpan.FromHours(0)), Name = "Fawn Shawna"},      // Updated
        new Student { CourseId = 1, EnrollmentDate = new DateTimeOffset(2022, 1, 1, 23, 0, 0, TimeSpan.FromHours(0)), Name = "Fawn Shawna"},      // Created should be 11, but is 14
        new Student { CourseId = 1, EnrollmentDate = DateTimeOffset.UtcNow, Name = "Indy Carreen"},                                               // Created should be 12, but is 15
        new Student { CourseId = null, EnrollmentDate = DateTimeOffset.UtcNow, Name = "Nelly Cecil"},                                             // Created should be 13, but is 13
        new Student { CourseId = null, EnrollmentDate = new DateTimeOffset(2020, 1, 1, 10, 0, 0, TimeSpan.FromHours(0)), Name = "Fawn Shawna"},   // Created should be 14, but is 12
        new Student { CourseId = null, EnrollmentDate = new DateTimeOffset(2020, 1, 1, 10, 0, 0, TimeSpan.FromHours(0)), Name = "Keila Emerson"}, // Updated
        new Student { CourseId = 1, EnrollmentDate = DateTimeOffset.UtcNow, Name = "Quentin Creighton"},                                          // Created should be 15, but is 16
        new Student { CourseId = 2, EnrollmentDate = new DateTimeOffset(2020, 1, 1, 8, 0, 0, TimeSpan.FromHours(0)), Name = "Aston Fenton" },     // Updated
        new Student { CourseId = null, EnrollmentDate = DateTimeOffset.UtcNow, Name = "Earlene Aaren"},                                           // Created should be 16, but is 11
    };

    var beforeInsertOrUpdate = await _applicationDbContext.Set<Student>().ToArrayAsync();

    await _applicationDbContext.BulkInsertOrUpdateAsync(
        entities: entriesToInsertOrUpdate,
        bulkConfig: new BulkConfig
        {
            SetOutputIdentity = true,
            PreserveInsertOrder = true,
            UpdateByProperties = new List<string>
            {
                nameof(Student.CourseId),
                nameof(Student.Name),
                nameof(Student.EnrollmentDate),
            },
            PropertiesToExcludeOnUpdate = new List<string>
            {
                nameof(Student.CreatedAt),
            },
            SqlBulkCopyOptions = SqlBulkCopyOptions.CheckConstraints
                | SqlBulkCopyOptions.FireTriggers
                | SqlBulkCopyOptions.UseInternalTransaction
        });

    return Ok(new
    {
        OldValuesFromDatabase = beforeInsertOrUpdate,
        NewValues = entriesToInsertOrUpdate,
        ValuesFromDatabase = await _applicationDbContext.Set<Student>().ToArrayAsync()
    });
}

Entities:

public class Student
{
    [Key]
    public long Id { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTimeOffset? CreatedAt { get; set; }

    [Required]
    [MaxLength(255)]
    public string? Name { get; set; }

    public long? CourseId { get; set; }

    public DateTimeOffset EnrollmentDate { get; set; }

    public Course? Course { get; set; }
}

public class Course
{
    [Key]
    public long Id { get; set; }

    [Required]
    [MaxLength(255)]
    public string? Name { get; set; }
}

Context

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<Student>(entity =>
    {
        entity.HasOne(s => s.Course)
            .WithMany()
            .HasForeignKey(s => s.CourseId)
            .OnDelete(DeleteBehavior.Restrict);

        entity.HasData(
            new Student { Id = 1, CourseId = 1, EnrollmentDate = new DateTimeOffset(2020, 1, 1, 8, 0, 0, TimeSpan.FromHours(0)), Name = "Libby Briar" },
            new Student { Id = 2, CourseId = 1, EnrollmentDate = new DateTimeOffset(2020, 1, 1, 8, 0, 0, TimeSpan.FromHours(0)), Name = "Kehlani Hallie" },
            new Student { Id = 3, CourseId = 1, EnrollmentDate = new DateTimeOffset(2020, 1, 1, 10, 0, 0, TimeSpan.FromHours(0)), Name = "Fawn Shawna" },
            new Student { Id = 4, CourseId = 2, EnrollmentDate = new DateTimeOffset(2020, 1, 1, 8, 0, 0, TimeSpan.FromHours(0)), Name = "Richardine Dash" },
            new Student { Id = 5, CourseId = 2, EnrollmentDate = new DateTimeOffset(2020, 1, 1, 8, 0, 0, TimeSpan.FromHours(0)), Name = "Fletcher Kaylin" },
            new Student { Id = 6, CourseId = 2, EnrollmentDate = new DateTimeOffset(2020, 1, 1, 10, 0, 0, TimeSpan.FromHours(0)), Name = "Delilah Cari" },
            new Student { Id = 7, CourseId = 2, EnrollmentDate = new DateTimeOffset(2020, 1, 1, 10, 0, 0, TimeSpan.FromHours(0)), Name = "Chanel Linton" },
            new Student { Id = 8, CourseId = 2, EnrollmentDate = new DateTimeOffset(2020, 1, 1, 8, 0, 0, TimeSpan.FromHours(0)), Name = "Aston Fenton" },
            new Student { Id = 9, CourseId = null, EnrollmentDate = new DateTimeOffset(2020, 1, 1, 8, 0, 0, TimeSpan.FromHours(0)), Name = "Renee Madisyn" },
            new Student { Id = 10, CourseId = null, EnrollmentDate = new DateTimeOffset(2020, 1, 1, 10, 0, 0, TimeSpan.FromHours(0)), Name = "Keila Emerson" });
    });

    modelBuilder.Entity<Course>(entity =>
    {
        entity.HasData(
            new Course { Id = 1, Name = "Software Engineering" },
            new Course { Id = 2, Name = "Mechanical Engineering" });
    });
}

Output

{
  "oldValues": [
    {
      "id": 1,
      "createdAt": null,
      "name": "Libby Briar",
      "courseId": 1,
      "enrollmentDate": "2020-01-01T08:00:00+00:00",
      "course": null
    },
    {
      "id": 2,
      "createdAt": null,
      "name": "Kehlani Hallie",
      "courseId": 1,
      "enrollmentDate": "2020-01-01T08:00:00+00:00",
      "course": null
    },
    {
      "id": 3,
      "createdAt": null,
      "name": "Fawn Shawna",
      "courseId": 1,
      "enrollmentDate": "2020-01-01T10:00:00+00:00",
      "course": null
    },
    {
      "id": 4,
      "createdAt": null,
      "name": "Richardine Dash",
      "courseId": 2,
      "enrollmentDate": "2020-01-01T08:00:00+00:00",
      "course": null
    },
    {
      "id": 5,
      "createdAt": null,
      "name": "Fletcher Kaylin",
      "courseId": 2,
      "enrollmentDate": "2020-01-01T08:00:00+00:00",
      "course": null
    },
    {
      "id": 6,
      "createdAt": null,
      "name": "Delilah Cari",
      "courseId": 2,
      "enrollmentDate": "2020-01-01T10:00:00+00:00",
      "course": null
    },
    {
      "id": 7,
      "createdAt": null,
      "name": "Chanel Linton",
      "courseId": 2,
      "enrollmentDate": "2020-01-01T10:00:00+00:00",
      "course": null
    },
    {
      "id": 8,
      "createdAt": null,
      "name": "Aston Fenton",
      "courseId": 2,
      "enrollmentDate": "2020-01-01T08:00:00+00:00",
      "course": null
    },
    {
      "id": 9,
      "createdAt": null,
      "name": "Renee Madisyn",
      "courseId": null,
      "enrollmentDate": "2020-01-01T08:00:00+00:00",
      "course": null
    },
    {
      "id": 10,
      "createdAt": null,
      "name": "Keila Emerson",
      "courseId": null,
      "enrollmentDate": "2020-01-01T10:00:00+00:00",
      "course": null
    }
  ],
  "newValuesFromMemory": [
    {
      "id": 0,
      "createdAt": null,
      "name": "Fawn Shawna",
      "courseId": 1,
      "enrollmentDate": "2020-01-01T10:00:00+00:00",
      "course": null
    },
    {
      "id": 0,
      "createdAt": null,
      "name": "Fawn Shawna",
      "courseId": 1,
      "enrollmentDate": "2022-01-01T23:00:00+00:00",
      "course": null
    },
    {
      "id": 0,
      "createdAt": null,
      "name": "Indy Carreen",
      "courseId": 1,
      "enrollmentDate": "2022-04-27T08:41:24.2603513+00:00",
      "course": null
    },
    {
      "id": 0,
      "createdAt": null,
      "name": "Nelly Cecil",
      "courseId": null,
      "enrollmentDate": "2022-04-27T08:41:24.260352+00:00",
      "course": null
    },
    {
      "id": 0,
      "createdAt": null,
      "name": "Fawn Shawna",
      "courseId": null,
      "enrollmentDate": "2020-01-01T10:00:00+00:00",
      "course": null
    },
    {
      "id": 0,
      "createdAt": null,
      "name": "Keila Emerson",
      "courseId": null,
      "enrollmentDate": "2020-01-01T10:00:00+00:00",
      "course": null
    },
    {
      "id": 0,
      "createdAt": null,
      "name": "Quentin Creighton",
      "courseId": 1,
      "enrollmentDate": "2022-04-27T08:41:24.2603528+00:00",
      "course": null
    },
    {
      "id": 0,
      "createdAt": null,
      "name": "Aston Fenton",
      "courseId": 2,
      "enrollmentDate": "2020-01-01T08:00:00+00:00",
      "course": null
    },
    {
      "id": 0,
      "createdAt": null,
      "name": "Earlene Aaren",
      "courseId": null,
      "enrollmentDate": "2022-04-27T08:41:24.260353+00:00",
      "course": null
    }
  ],
  "newValuesFromDatabase": [
    {
      "id": 1,
      "createdAt": null,
      "name": "Libby Briar",
      "courseId": 1,
      "enrollmentDate": "2020-01-01T08:00:00+00:00",
      "course": null
    },
    {
      "id": 2,
      "createdAt": null,
      "name": "Kehlani Hallie",
      "courseId": 1,
      "enrollmentDate": "2020-01-01T08:00:00+00:00",
      "course": null
    },
    {
      "id": 3,
      "createdAt": null,
      "name": "Fawn Shawna",
      "courseId": 1,
      "enrollmentDate": "2020-01-01T10:00:00+00:00",
      "course": null
    },
    {
      "id": 4,
      "createdAt": null,
      "name": "Richardine Dash",
      "courseId": 2,
      "enrollmentDate": "2020-01-01T08:00:00+00:00",
      "course": null
    },
    {
      "id": 5,
      "createdAt": null,
      "name": "Fletcher Kaylin",
      "courseId": 2,
      "enrollmentDate": "2020-01-01T08:00:00+00:00",
      "course": null
    },
    {
      "id": 6,
      "createdAt": null,
      "name": "Delilah Cari",
      "courseId": 2,
      "enrollmentDate": "2020-01-01T10:00:00+00:00",
      "course": null
    },
    {
      "id": 7,
      "createdAt": null,
      "name": "Chanel Linton",
      "courseId": 2,
      "enrollmentDate": "2020-01-01T10:00:00+00:00",
      "course": null
    },
    {
      "id": 8,
      "createdAt": null,
      "name": "Aston Fenton",
      "courseId": 2,
      "enrollmentDate": "2020-01-01T08:00:00+00:00",
      "course": null
    },
    {
      "id": 9,
      "createdAt": null,
      "name": "Renee Madisyn",
      "courseId": null,
      "enrollmentDate": "2020-01-01T08:00:00+00:00",
      "course": null
    },
    {
      "id": 10,
      "createdAt": null,
      "name": "Keila Emerson",
      "courseId": null,
      "enrollmentDate": "2020-01-01T10:00:00+00:00",
      "course": null
    },
    {
      "id": 11,
      "createdAt": null,
      "name": "Earlene Aaren",
      "courseId": null,
      "enrollmentDate": "2022-04-27T08:41:24.260353+00:00",
      "course": null
    },
    {
      "id": 12,
      "createdAt": null,
      "name": "Fawn Shawna",
      "courseId": null,
      "enrollmentDate": "2020-01-01T10:00:00+00:00",
      "course": null
    },
    {
      "id": 13,
      "createdAt": null,
      "name": "Nelly Cecil",
      "courseId": null,
      "enrollmentDate": "2022-04-27T08:41:24.260352+00:00",
      "course": null
    },
    {
      "id": 14,
      "createdAt": null,
      "name": "Fawn Shawna",
      "courseId": 1,
      "enrollmentDate": "2022-01-01T23:00:00+00:00",
      "course": null
    },
    {
      "id": 15,
      "createdAt": null,
      "name": "Indy Carreen",
      "courseId": 1,
      "enrollmentDate": "2022-04-27T08:41:24.2603513+00:00",
      "course": null
    },
    {
      "id": 16,
      "createdAt": null,
      "name": "Quentin Creighton",
      "courseId": 1,
      "enrollmentDate": "2022-04-27T08:41:24.2603528+00:00",
      "course": null
    }
  ]
}

[borisdj]

OrderBy is set in SqlQueryBuilder.cs on segment:

EFCore.BulkExtensions/EFCore.BulkExtensions/SqlQueryBuilder.cs

Lines 179 to 184 in 38a3c37

	if (tableInfo.BulkConfig.PreserveInsertOrder)
	{
	int numberOfEntities = tableInfo.BulkConfig.CustomSourceTableName == null ? tableInfo.NumberOfEntities : int.MaxValue;
	var orderBy = (primaryKeys.Count() == 0) ? "" : $"ORDER BY {GetCommaSeparatedColumns(primaryKeys)}";
	sourceTable = $"(SELECT TOP {numberOfEntities} * FROM {sourceTable} {orderBy})";
	}

But when UpdateByProperties are configured then orderBy is done with those props instead of ID.
I have tried to change it, and keep IdentityCol, with following line:

var orderByCols = tableInfo.HasIdentity ? new List<string> { tableInfo.IdentityColumnName } : primaryKeys;
var orderBy = (orderByCols.Count() == 0) ? "" : $"ORDER BY {GetCommaSeparatedColumns(orderByCols)}";

But still it does not make proper order.
Seems this is due to how Sql MERGE works, it can only OrberBy cols that are in ON list to be compared for update.
Only alternative would be to first use BulkRead and find which records already exist then split the list into 2 lists entitiesForUpdate and entitiesForInsert without configuring UpdateByProps.