Child Entity Insert Order Incorrect

[JamezG12]

We encountered an ordering issue when we were inserting multiple records to the database.

We expected the order of the child entities to be preserved when we called SaveChangesAsync(). SaveChanges() did not make a difference either.

The inconsistent insert order only seemed to happen consistently when we inserted 20 child items on the collection.

We currently add to the collection entity then call save changes. We tried modifying the entities and the context so that we could add directly to the DbSet, but it made no difference.

The code in question:

foreach (var document in documents)
    collection.Documents.Add(document);

await _context.SaveChangesAsync();

Our entities are as follows:

[Table("Collections")]
public class Collection
    {
        public Collection()
        {
            Documents = new List<Document>();
        }

        [Key]
        public int Id { get; set; }

        public virtual IList<Document> Documents { get; set; }
    }

[Table("Documents")]
    public class Document
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        [Required]
        public string Type { get; set; }

        [Required]
        public string MimeType { get; set; }

        [Required]
        public string Extension { get; set; }

        [Required]
        public Orientation Orientation { get; set; }

        [Required]
        public DateTime LastModified { get; set; }

        public string Uri { get; set; }
    }

public class CollectionConfiguration : IEntityTypeConfiguration<Collection>
    {
        public void Configure(EntityTypeBuilder<Collection> builder)
        {
            builder.HasMany(x => x.Documents).WithOne().OnDelete(DeleteBehavior.Cascade);
        }
    }

public class DocumentContext : DbContext
    {
        public DocumentContext(DbContextOptions options) : base(options)
        {

        }

        public DbSet<Collection> Collections { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.ApplyConfiguration(new CollectionConfiguration());

            base.OnModelCreating(modelBuilder);
        }
    }

The SQL that EF generated was this:

exec sp_executesql N'SET NOCOUNT ON;
DECLARE @inserted0 TABLE ([Id] int, [_Position] [int]);
MERGE [documentService].[Documents] USING (
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, 0),
(@p7, @p8, @p9, @p10, @p11, @p12, @p13, 1),
(@p14, @p15, @p16, @p17, @p18, @p19, @p20, 2),
(@p21, @p22, @p23, @p24, @p25, @p26, @p27, 3),
(@p28, @p29, @p30, @p31, @p32, @p33, @p34, 4),
(@p35, @p36, @p37, @p38, @p39, @p40, @p41, 5),
(@p42, @p43, @p44, @p45, @p46, @p47, @p48, 6),
(@p49, @p50, @p51, @p52, @p53, @p54, @p55, 7),
(@p56, @p57, @p58, @p59, @p60, @p61, @p62, 8),
(@p63, @p64, @p65, @p66, @p67, @p68, @p69, 9),
(@p70, @p71, @p72, @p73, @p74, @p75, @p76, 10),
(@p77, @p78, @p79, @p80, @p81, @p82, @p83, 11),
(@p84, @p85, @p86, @p87, @p88, @p89, @p90, 12),
(@p91, @p92, @p93, @p94, @p95, @p96, @p97, 13),
(@p98, @p99, @p100, @p101, @p102, @p103, @p104, 14),
(@p105, @p106, @p107, @p108, @p109, @p110, @p111, 15),
(@p112, @p113, @p114, @p115, @p116, @p117, @p118, 16),
(@p119, @p120, @p121, @p122, @p123, @p124, @p125, 17),
(@p126, @p127, @p128, @p129, @p130, @p131, @p132, 18),
(@p133, @p134, @p135, @p136, @p137, @p138, @p139, 19)) AS i ([CollectionId], [Extension], [LastModified], [MimeType], [Orientation], [Type], [Uri], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([CollectionId], [Extension], [LastModified], [MimeType], [Orientation], [Type], [Uri])
VALUES (i.[CollectionId], i.[Extension], i.[LastModified], i.[MimeType], i.[Orientation], i.[Type], i.[Uri])
OUTPUT INSERTED.[Id], i._Position
INTO @inserted0;

SELECT [t].[Id] FROM [documentService].[Documents] t
INNER JOIN @inserted0 i ON ([t].[Id] = [i].[Id])
ORDER BY [i].[_Position];

',N'@p0 int,@p1 nvarchar(4000),@p2 datetime2(7),@p3 nvarchar(4000),@p4 int,@p5 nvarchar(4000),@p6 nvarchar(4000),@p7 int,@p8 nvarchar(4000),@p9 datetime2(7),@p10 nvarchar(4000),@p11 int,@p12 nvarchar(4000),@p13 nvarchar(4000),@p14 int,@p15 nvarchar(4000),@p16 datetime2(7),@p17 nvarchar(4000),@p18 int,@p19 nvarchar(4000),@p20 nvarchar(4000),@p21 int,@p22 nvarchar(4000),@p23 datetime2(7),@p24 nvarchar(4000),@p25 int,@p26 nvarchar(4000),@p27 nvarchar(4000),@p28 int,@p29 nvarchar(4000),@p30 datetime2(7),@p31 nvarchar(4000),@p32 int,@p33 nvarchar(4000),@p34 nvarchar(4000),@p35 int,@p36 nvarchar(4000),@p37 datetime2(7),@p38 nvarchar(4000),@p39 int,@p40 nvarchar(4000),@p41 nvarchar(4000),@p42 int,@p43 nvarchar(4000),@p44 datetime2(7),@p45 nvarchar(4000),@p46 int,@p47 
nvarchar(4000),@p48 nvarchar(4000),@p49 int,@p50 nvarchar(4000),@p51 datetime2(7),@p52 nvarchar(4000),@p53 int,@p54 nvarchar(4000),@p55 nvarchar(4000),@p56 int,@p57 nvarchar(4000),@p58 datetime2(7),@p59 nvarchar(4000),@p60 int,@p61 nvarchar(4000),@p62 
nvarchar(4000),@p63 int,@p64 nvarchar(4000),@p65 datetime2(7),@p66 nvarchar(4000),@p67 int,@p68 nvarchar(4000),@p69 nvarchar(4000),@p70 int,@p71 nvarchar(4000),@p72 datetime2(7),@p73 nvarchar(4000),@p74 int,@p75 nvarchar(4000),@p76 nvarchar(4000),@p77 int,@p78 
nvarchar(4000),@p79 datetime2(7),@p80 nvarchar(4000),@p81 int,@p82 nvarchar(4000),@p83 nvarchar(4000),@p84 int,@p85 nvarchar(4000),@p86 datetime2(7),@p87 nvarchar(4000),@p88 int,@p89 nvarchar(4000),@p90 nvarchar(4000),@p91 int,@p92 nvarchar(4000),@p93 
datetime2(7),@p94 nvarchar(4000),@p95 int,@p96 nvarchar(4000),@p97 nvarchar(4000),@p98 int,@p99 nvarchar(4000),@p100 datetime2(7),@p101 nvarchar(4000),@p102 int,@p103 nvarchar(4000),@p104 nvarchar(4000),@p105 int,@p106 nvarchar(4000),@p107 datetime2(7),@p108 
nvarchar(4000),@p109 int,@p110 nvarchar(4000),@p111 nvarchar(4000),@p112 int,@p113 nvarchar(4000),@p114 datetime2(7),@p115 nvarchar(4000),@p116 int,@p117 nvarchar(4000),@p118 nvarchar(4000),@p119 int,@p120 nvarchar(4000),@p121 datetime2(7),@p122 nvarchar(4000),@p123 
int,@p124 nvarchar(4000),@p125 nvarchar(4000),@p126 int,@p127 nvarchar(4000),@p128 datetime2(7),@p129 nvarchar(4000),@p130 int,@p131 nvarchar(4000),@p132 nvarchar(4000),@p133 int,@p134 nvarchar(4000),@p135 datetime2(7),@p136 nvarchar(4000),@p137 int,@p138 
nvarchar(4000),@p139 nvarchar(4000)',@p0=422,

@p1=N'1',@p2='2018-04-16 11:05:47.7148985',@p3=N'text/plain',@p4=0,@p5=N'1',@p6=NULL,
@p7=422,@p8=N'18',@p9='2018-04-16 11:05:47.7151279',@p10=N'text/plain',@p11=0,@p12=N'18',@p13=NULL,
@p14=422,@p15=N'17',@p16='2018-04-16 11:05:47.7151279',@p17=N'text/plain',@p18=0,@p19=N'17',@p20=NULL,
@p21=422,@p22=N'16',@p23='2018-04-16 11:05:47.7151279',@p24=N'text/plain',@p25=0,@p26=N'16',@p27=NULL,
@p28=422,@p29=N'15',@p30='2018-04-16 11:05:47.7151276',@p31=N'text/plain',@p32=0,@p33=N'15',@p34=NULL,
@p35=422,@p36=N'14',@p37='2018-04-16 11:05:47.7151276',@p38=N'text/plain',@p39=0,@p40=N'14',@p41=NULL,
@p42=422,@p43=N'13',@p44='2018-04-16 11:05:47.7151276',@p45=N'text/plain',@p46=0,@p47=N'13',@p48=NULL,
@p49=422,@p50=N'12',@p51='2018-04-16 11:05:47.7151273',@p52=N'text/plain',@p53=0,@p54=N'12',@p55=NULL,
@p56=422,@p57=N'11',@p58='2018-04-16 11:05:47.7151273',@p59=N'text/plain',@p60=0,@p61=N'11',@p62=NULL,
@p63=422,@p64=N'10',@p65='2018-04-16 11:05:47.7151273',@p66=N'text/plain',@p67=0,@p68=N'10',@p69=NULL,
@p70=422,@p71=N'9',@p72='2018-04-16 11:05:47.7151270',@p73=N'text/plain',@p74=0,@p75=N'9',@p76=NULL,
@p77=422,@p78=N'8',@p79='2018-04-16 11:05:47.7151270',@p80=N'text/plain',@p81=0,@p82=N'8',@p83=NULL,
@p84=422,@p85=N'7',@p86='2018-04-16 11:05:47.7151270',@p87=N'text/plain',@p88=0,@p89=N'7',@p90=NULL,
@p91=422,@p92=N'6',@p93='2018-04-16 11:05:47.7151267',@p94=N'text/plain',@p95=0,@p96=N'6',@p97=NULL,
@p98=422,@p99=N'5',@p100='2018-04-16 11:05:47.7151267',@p101=N'text/plain',@p102=0,@p103=N'5',@p104=NULL,
@p105=422,@p106=N'4',@p107='2018-04-16 11:05:47.7151267',@p108=N'text/plain',@p109=0,@p110=N'4',@p111=NULL,
@p112=422,@p113=N'3',@p114='2018-04-16 11:05:47.7151263',@p115=N'text/plain',@p116=0,@p117=N'3',@p118=NULL,
@p119=422,@p120=N'2',@p121='2018-04-16 11:05:47.7151263',@p122=N'text/plain',@p123=0,@p124=N'2',@p125=NULL,
@p126=422,@p127=N'19',@p128='2018-04-16 11:05:47.7151279',@p129=N'text/plain',@p130=0,@p131=N'19',@p132=NULL,
@p133=422,@p134=N'20',@p135='2018-04-16 11:05:47.7151283',@p136=N'text/plain',@p137=0,@p138=N'20',@p139=NULL

The test that we set up expected the extension and type to be in ascending order.

Our workaround is to call SaveChangesAsync() after adding each entity to the parent.

Further technical details

EF Core version: 2.0.2 Database Provider: Microsoft.EntityFrameworkCore.SqlServer Operating system: Windows 10 IDE: Visual Studio 2017 15.6.6

[ajcvickers]

@JamezG12 EF determines the order of operations for SaveChanges based on dependencies between different entity types. The order in which entities are added with DbContext.Add or DbSet.Add is not preserved, neither is the order of entities in navigation properties. Even if EF did preserve the order on insertion, most database engines don't guarantee to preserve the order on query (even though it may appear that way for simple cases) so it usually wouldn't help to have ordered inserts anyway. That being said, issue #9067 is tracking support for this through mapping for navigation properties.

Could you provide some more details as to why you need the order to be preserved?

[JamezG12]

Hi @ajcvickers, thanks for getting back to me.

I appreciate EF doesn't preserve the order of insertion to a DB with relation to multiple types of entities, as it needs to insert based on it's mappings, but we have the parent record already and we're just inserting new child records.

We had a set of documents that need to be in a specific order, we assumed that if we add these to an entity's collection and SaveChanges is called, they'd be inserted in the same order to the database.

Not necessarily with sequential ID's, but at least in the order they were in when they were in memory.

I found it quite strange that EF was generating a query where it appears that it's trying to preserve the order of insertion using _Position, it just looks like it's putting the parameter values in the wrong order.

The test case we wrote looked like EF was consistent in the order it inserted the records, 1,18,17,15,14.

I'm quite happy to accept that EF doesn't support ordering of inserts, it just doesn't seem intuitive that you'd add to your ORM entity in one way, for the framework to completely ignore the order in which your entities were in memory.

Does this mean that every entity we have that needs to be ordered a specific way, must have something like an Order or Sequence number, rather than relying on the Identity field and the order they were added to the collection?

Cheers,

James

[ajcvickers]

@JamezG12 Yes.

[JamezG12]

Fantastic, thanks very much for your time that'll sort our issues and helps fill gaps in my knowledge.

Cheers,

James

[timmi4sa]

This is completely unacceptable! The ORM's purpose is to bridge the gap between Object Model and persistence, which this particular ORM is not making possible!!! We only "thing" we have to accept is having to have a primary key property (that makes no sense to the Object Model) but, sure, we accept this as a requirement in relation to the SQL-based storage media.

DO NOT use the storage engines as an excuse. MOST of the the database management systems will use the next consecutive number (SERIAL, or IDENTITY) for the consecutive inserts and primary keys being of integer type. We don't care how the rows will be physically stored on disk (and that's not what you should be bringing this up here).

Tell us straight - you are using optimizations to kick the EF6 out of the ball park, we get it, again. BUT then it's not an Object Relational Mapper. Imagine BMW telling me that to produce the fastest vehicle they need to put the engine right smack in the middle of the car and put passengers in the trunk. That's basically what you are proposing!

Let the database decide how it would Store the rows, and US how we populate dependent navigational collections: a) we create an entity A with a dependent collection of B, 2) the B object has to be in the order the B have been imported from a text file, 3) Now we have to add a new property to B for sorting that make NO SENSE to the object model, 4) why don't we just switch to ADO.NET????

Or how about this: you give us a DbContext.Database property that controls whether the INSERT order shouldn't be preserved for HIGH THROUGHPUT day trading scenarios.