Sqlclient: UDT parameters are limited to 64K
This is an issue because serialized spatial types can easily be bigger than this. See https://github.com/aspnet/EntityFrameworkCore/issues/18813.
@Wraith2 pointed to the place in the code where this limitation exists: https://github.com/dotnet/SqlClient/blob/d0672d2c4dfd0f09ba49f09d605b827366c61b1b/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/TdsParser.cs#L9027-L9029
/cc @bricelam
ajcvickers
All 25 comments
It seems that the driver can support larger data size for UDT Types (upto 2 GB, i.e. max LOB size, identified by 0x7FFFFFFF token with length as -1, identified by 0xFFFF token), as introduced in TDS 7.3, but the support was not added to the driver.
_Quoting MS TDS:_
The value 0xFFFF signifies the maximum LOB size indicating a UDT with a maximum size greater than 8000 bytes (also referred to as a Large UDT; introduced in TDS 7.3).
This applies to both .NET Framework and .NET Core.
Issue would be categorized as an enhancement.
cheenamalhotra
on 27 Nov 2019
That shouldn't be too hard to fix. If i get time i'll have a go. I'd also like to change the exception type that it throws and give a sensible message to the user explaining why they're getting the error. A default IndexOutOfRange exception makes it look like an internal problem (fooled me when i was investigating) rather than something the user can address.
Wraith2
on 27 Nov 2019
How do you get the connection tds protocol version?There's a major+minor version on the session but that doesn't seem to get used. There's also _isYukon _isKatmai _isDenali on the parser but I'm not sure how they map to protocol versions.
Wraith2
on 29 Nov 2019
@Wraith2
TDSParser when parses tokens calls this method for LOGINACK token:
https://github.com/dotnet/SqlClient/blob/1a0748ebc0b44af787623ef8416fdb911d16cfea/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlInternalConnectionTds.cs#L1947-L1961
cheenamalhotra
on 29 Nov 2019
Ehm, guys, any workaround (no matter how ugly)?
This used to work in 2.2 and it really is not an enhancement but a breaking bug
aprognimak
on 2 Dec 2019
It didn't work in 2.2. It's been this way since the inception of the feature, the only thing that can have changed is the amount of data you feed it. I'm working my way through the fix but I've got a few things going on at the minute so it's a bit slow.
Wraith2
on 2 Dec 2019
@bricelam Is it possible that EF Core didn't hit this in 2.2 because it was using a different mechanism that didn't hit this exact code path? (I don't remember exactly what we changed here.)
ajcvickers
on 2 Dec 2019
It didn't work in 2.2. It's been this way since the inception of the feature, the only thing that can have changed is the amount of data you feed it. I'm working my way through the fix but I've got a few things going on at the minute so it's a bit slow.
Well it did. Just triple hours ago I was on 2.2 using the olden NetTopologySuite types, those IGeometry and all that stuff. However saving changes to the context when using spatial data even when there were no changes took enormous time (minutes for 1k+ shapes, unchanged) so I moved to 3.0.1 and it just stopped working.
Now I can't reseed my db with us state county borders because even with the least accuracy some of those don't fit into 64k parameter value. 2.2 most likely used quite different approach when dealing with spatial data given it was just introduced then.
Trying postgres now.
aprognimak
on 3 Dec 2019
Ehm, guys, any workaround (no matter how ugly)?
This used to work in 2.2 and it really is not an enhancement but a breaking bug
I鈥檓 using ExecuteSqlRaw with geometry::STGeomFromText('{geometry.ToString()}' as a temporary fix.
capesean
on 3 Dec 2019
EF Core didn't hit this in 2.2 because it was using a different mechanism
Correct. We were using SqlBytes parameters (not UDTs) in 2.2. We switched to UDTs in 3.0 to fix https://github.com/aspnet/EntityFrameworkCore/issues/14595
bricelam
on 3 Dec 2019
@cheenamalhotra I think this raises the priority of getting this changed, since without it customers using spatial with EF Core will be running into blocking issues. Hopefully @Wraith2 will come up with a fix, but if not can we raise the priority of getting this in?
ajcvickers
on 3 Dec 2019
Prospective fix is posted https://github.com/dotnet/SqlClient/pull/340
Wraith2
on 4 Dec 2019
Thanks for the PR @Wraith2
Is there a special reason to increase size limit to int.MaxValue? I'm wondering if it would bring back the same support as sending data as SqlBytes previously with .NET Core 2.2.
The size we're looking at is 2 GB LOBs that driver needs to support with Large UDTs.
cheenamalhotra
on 5 Dec 2019
Ok, I see the driver does support Large UDTs, it was probably not hitting the right code path when working with large data. I'll continue to review PR and post comments there, just want to make sure the metadata information is available as per specs.
cheenamalhotra
on 5 Dec 2019
It can already receive large UDTs but the check in sending them was hardcoded to short.MaxValue which as you pointed out earlier in this discussion was only for 2005. I just made the check version aware.
Since sending the UDT doesn't require you to send the size field for the UDT description this check could be omitted entirely but if a user did go over the limit they would get an SqlException back from the server after having had to wait for transferring>2Gib and it wouldn't be a very friendly message.
Wraith2
on 5 Dec 2019
Any hint on when this fix will show up in ef core? This is what's blocking us from upgrading to ef core 3
loth0
on 12 Dec 2019
Do you need it on netcore or desktop? this fix is only netcore at the moment, it's probably possible to get it into desktop as well if the code in that version is pretty similar.
Wraith2
on 12 Dec 2019
If it is for EF Core, it will always be the .NET Core SqlClient
ErikEJ
on 12 Dec 2019
Hi @loth0
Our next preview release containing the fix is planned in mid-January, after which you should be able to custom import the new preview Microsoft.Data.SqlClient package in your application with EF Core 3.1.
EF Core should in future upgrade the dependency version for Microsoft.Data.SqlClient, but for their timelines you'll need to check with them.
cheenamalhotra
on 12 Dec 2019
@cheenamalhotra When will we know when this is fixed? Because I have EF Core 3.1.1 and I can still repro this problem.
jsgoupil
on 28 Jan 2020
@jsgoupil
Since the fix was released in v2.0.0-preview1 of Microsoft.Data.SqlClient, you need to add custom reference to this version to get this fix:
<PackageReference Include="Microsoft.Data.SqlClient" Version="2.0.0-preview1.20021.1" />
See also https://github.com/NetTopologySuite/NetTopologySuite/issues/383, it seems the work around is to use a smaller string for now.
juliusfriedman
on 20 Feb 2020
Have this been included in latest EF Core (3.1.7)? i.e can I remove my package reference to Microsoft.Data.SqlClient?
loth0
on 31 Aug 2020
Hi @loth0
EF Core seems to use SqlClient v2.0.0 starting 5.0.0-preview versions:
https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.SqlServer/5.0.0-preview.7.20365.15
While v3.1.7 depends on M.D.S v1.1.3 that does not contain this fix.
cheenamalhotra
on 31 Aug 2020
@loth0 it should be fine to use EF Core 3.1.7 and to take an explicit dependency on SqlClient 2.0.1 - that version just isn't brought in by default, that's all.
roji
on 31 Aug 2020
Related issues
alvipeo
路
4Comments
OdaShinsuke
路
5Comments
dehghani-mehdi
路
8Comments
dmuruges
路
4Comments
ajcvickers
路
5Comments
Most helpful comment
Hi @loth0
Our next preview release containing the fix is planned in mid-January, after which you should be able to custom import the new preview Microsoft.Data.SqlClient package in your application with EF Core 3.1.
EF Core should in future upgrade the dependency version for Microsoft.Data.SqlClient, but for their timelines you'll need to check with them.