Batching through a table variable may not work for non-default collations

[divega]

(copied from @seriouz's comment at #6577 (comment))

I have a database with some columns collations set to be case sensitive. Now EF build its query with a temp table, but this temp table does not have the collation on the column so on the insert the database server throws an exception.
Until there is a work-around i can not use ef because the regarding columns contain (case sensitive) guids.

I think @seriouz meant to say SaveChanges() fails, as opposed to querying data.

A possible workaround is to disable batching in either the OnConfiguring() method on the DbContext class or on the AddDbContext() method in Startup:

    optionsBuilder => optionsBuilder
        .UseSqlServer(connectionString)
        .MaxBatchSize(1);

cc @AndriySvyryd as FYI

[seriouz]

@divega you are right. I create multiple parent and child entities. Which reference each other with case-sensitive guid keys. (something like Pk8hUi....) All of them are added by _dbContext.Add(). And then saved in one chuck with _dbContext.SaveChanges().
In the debug output i can see this line from the query generated by ef:
DECLARE @inserted0 TABLE ([FieldGuid] nvarchar(22), [_Position] [int]); the ef query builder misses to check the collation of the underlying column which has a COLLATE SQL_Latin1_General_CP1_CS_AS resulting in a collation missmatch thrown by the mssql server.

[divega]

@seriouz thanks for confirming. Please let me know if the suggested workaround helps.

[AndriySvyryd]

We would need to add the ability of specifying collation for a given column to be able to use it in the update pipeline.

[AndriySvyryd]

@seriouz As a workaround you can specify the collation as part of the store type:

.Property(e => e.FieldGuid).HasColumnType("nvarchar(22) COLLATE SQL_Latin1_General_CP1_CS_AS");

[ajcvickers]

Should at least do the R.E. work. We may need to look at the whole update pipeline "experience" as well...

[ajcvickers]

Also see high-level collations issue #19866

[roji]

Note that since #27372 we no longer use table variables by default - we use simple OUTPUT, rather than OUTPUT INTO, unless the user explicitly tells us that the table has triggers.

[david-wimmer]

I created a PR #29518 with a proposed fix to this issue.

[angularsen]

Workaround: Save one parent entity at a time. It correctly saves related data in its properties.

I didn't see this workaround mentioned anywhere, but it was very helpful for us.

[AndriySvyryd]

Depends on #32546 and #29620