SQL Server: NodaTime, NetTopologySuite and JSON
NodaTime
// sqlsource.json, in the folder of a project that references NodaTime
{
"root": true,
"dialect": "mssql",
"libraryTypes": {
"nodaTime": "on",
},
}
-- name: GetShift -> one
SELECT id, day, opens_at, starts_at, booked_at
FROM dbo.shifts
WHERE id = @id;
public sealed record GetShiftDto(
long Id,
global::NodaTime.LocalDate Day,
global::NodaTime.LocalTime OpensAt,
global::NodaTime.LocalDateTime StartsAt,
global::NodaTime.OffsetDateTime BookedAt);
A project that references NodaTime, and turns it on with the library types, gets NodaTime's types for the columns and parameters of its queries whose output is repository or methods. Above, day is a date, opens_at a time, starts_at a datetime2 and booked_at a datetimeoffset, each NOT NULL.
- On SQL Server, NodaTime is off until a settings file or a marker turns it on, for a project, a folder, a file or a query. This holds whether the project references NodaTime or not.
- Nothing is registered in your application. Microsoft.Data.SqlClient reads and sends no NodaTime type. So the query methods convert each value from the C# type of the types as they read it, and to that type before they send it.
- The query methods that use NodaTime need NodaTime 3.1.9 or later. An older one is SQLSRC282.
| SQL Server type | C# type with NodaTime |
|---|---|
date |
NodaTime.LocalDate |
time |
NodaTime.LocalTime |
datetime2, datetime, smalldatetime |
NodaTime.LocalDateTime |
datetimeoffset |
NodaTime.OffsetDateTime |
An alias type over one of these types has its C# type, as the kinds say. A CLR type of your own never has one of them.
Every other type keeps the type of the types.
Reading a column as one of these types changes no value.
datetime2(7),time(7)anddatetimeoffset(7)hold the values of NodaTime to the 100 nanoseconds. What sending a value can change or refuse is below.Only query methods get NodaTime's types. A query whose output is
modelskeeps the types of the types, and so do its models. This holds whether Dapper or your own code reads the rows. A model that such a query shares with a query with a method is SQLSRC257: see library types.Your own choices win. A type override, a column type and a
-- param:or-- column:marker give their type over NodaTime's, so a project with"date": "System.DateOnly"keeps itsDateOnlycolumns."date": "default"in a nearer settings file, or-- type-override: date=defaultin a file, givesdateto NodaTime there.NodaTime.InstantandNodaTime.Durationare yours to choose, with the type override, a column type or a marker, whether NodaTime is on or not. The query methods convert them as they convert the types above.datetime2=NodaTime.Instantreads adatetime2as anInstant, taking the value stored as UTC, and sends anInstantas its date and time in UTC.datetimeoffset=NodaTime.Instantreads the instant of adatetimeoffsetand drops its offset, and sends anInstantat offset zero.time=NodaTime.Durationreads atimeas theDurationsince midnight.
Each NodaTime type is converted from the C# type that some database types are read as, whichever of those database types you give it to:
- a
LocalDate, aLocalDateTimeor anInstantfrom theSystem.DateTimeof adate, adatetime2, adatetimeor asmalldatetime; - a
LocalTimeor aDurationfrom theSystem.TimeSpanof atime; - an
OffsetDateTimeor anInstantfrom theSystem.DateTimeOffsetof adatetimeoffset.
So
datetime2=NodaTime.LocalDatereads the date of adatetime2and drops its time.- a
One of these types given to another database type is SQLSRC279, such as
datetimeoffset=NodaTime.LocalDateTime. The error names the type that it can be converted from.A NodaTime type that none of these is, such as a
ZonedDateTime, needs converters of your own. Without them, Microsoft.Data.SqlClient throwsInvalidCastExceptionwhen the query runs.Your converters replace the query methods' own. A converter of your project between a NodaTime type and the C# type it is read as is used in its direction, with no error. An example is a converter from
System.DateTimetoNodaTime.Instant. SqlSource's conversion is kept for the other direction.
What sending a value of NodaTime can change or refuse:
Nanoseconds below 100 are dropped, never rounded: a
LocalTimeof12:00:00.999999999is sent as12:00:00.9999999, the most thattime(7)holds, and so for the other types.An
OffsetDateTimewhose offset has seconds is sent with the offset cut to the minute, with no error, sinceDateTimeOffsetholds no seconds of an offset.+05:30:15is sent as+05:30with the same local date and time, so the instant stored moves by 15 seconds.An offset beyond 14 hours throws
InvalidOperationException. NodaTime allows such an offset, andDateTimeOffsetdoes not.An
OffsetDateTimewhose local date and time has an instant before year 1 or after year 9999 throwsArgumentOutOfRangeException.A value outside the range of
System.DateTime, before year 1 or after year 9999, throws before the query is sent. NodaTime allows such a value. ALocalDatethrowsArgumentOutOfRangeException, and aLocalDateTimeor anInstantthrowsInvalidOperationException.datetimeandsmalldatetimeare rounded before they are sent, as for aSystem.DateTime. Adatetimeis rounded to its three-hundredths of a second,.1234567to.123, and asmalldatetimeto the minute,12:34:56to12:35.23:59:59.999sent to adatetimeis the next day's00:00:00.A
datetimeor asmalldatetimeoutside its range, or rounded past it, throws before the query is sent:SqlTypeExceptionfor adatetimebefore 1753;OverflowExceptionfor asmalldatetimebefore 1900, or one that rounds past2079-06-06 23:59;OverflowExceptionfor adatetimeof9999-12-31 23:59:59.999.
9999-12-31 23:59:59.9999999, the last value before year 10000, is stored in adatetimeas23:59:59.997.A
Durationof 24 hours or more, or below zero, throwsOverflowException, "SqlDbType.Time overflow", before the query is sent: atimeholds less than a day.
NetTopologySuite
// sqlsource.json, in the folder of a project that references NetTopologySuite and NetTopologySuite.IO.SqlServerBytes
{
"root": true,
"dialect": "mssql",
"libraryTypes": {
"netTopologySuite": "on",
},
}
-- name: GetPlace -> one
SELECT id, centre, outline
FROM dbo.places
WHERE id = @id;
public sealed record GetPlaceDto(
long Id,
global::NetTopologySuite.Geometries.Geometry Centre,
global::NetTopologySuite.Geometries.Geometry Outline);
A project gets NetTopologySuite's Geometry for the geography and geometry columns and parameters of its queries whose output is repository or methods, when all of these are true:
- the project references
NetTopologySuite.IO.SqlServerBytes; - it references NetTopologySuite 2.5.0 or later;
- it turns NetTopologySuite on with the library types.
Above, centre is a geography and outline a geometry, each NOT NULL.
- Reference NetTopologySuite by name.
NetTopologySuite.IO.SqlServerBytesalone brings NetTopologySuite 2.0.0, which is SQLSRC282. - On SQL Server, NetTopologySuite is off until a settings file or a marker turns it on, for a project, a folder, a file or a query, whatever the project references.
- Nothing is registered in your application, and Microsoft.SqlServer.Types is not needed. Microsoft.Data.SqlClient reads and sends no NetTopologySuite type, so the query methods read and write each value themselves.
- The query methods that use NetTopologySuite need NetTopologySuite 2.5.0 or later and
NetTopologySuite.IO.SqlServerBytes2.1.0 or later. An older one is SQLSRC282.
| SQL Server type | C# type with NetTopologySuite |
|---|---|
geography, geometry |
NetTopologySuite.Geometries.Geometry |
Every other type keeps the type of the types.
- Only query methods get NetTopologySuite's types. A query whose output is
modelshas no C# type forgeographyorgeometry, with NetTopologySuite on or off, and is SQLSRC246. Give the query the outputrepositoryormethods, or map the type to a type of Microsoft.SqlServer.Types, as the types say. - A shape is yours to choose, with the type override, a column type or a
-- param:or-- column:marker, whether NetTopologySuite is on or not. An example isgeography=NetTopologySuite.Geometries.Point,Polygonor another ofGeometry's subtypes. A value of another shape throwsInvalidCastExceptionwhen it is read. - Your converters do not replace these reads and writes. A converter of yours between a NetTopologySuite type and another is not used for a
geographyor ageometry. To read one in another way, map it toMicrosoft.SqlServer.Types.SqlGeographyorSqlGeometry, and convert from that. - Without
NetTopologySuite.IO.SqlServerBytes, NetTopologySuite turned on is SQLSRC281. A type override to a NetTopologySuite type then leaves the value to Microsoft.Data.SqlClient, which cannot read it, so the query fails when it runs. - On a connection that wraps a
SqlConnection, a method reads a spatial column as it does on theSqlConnection. A method with parameters has the limit of such a connection.
What reading and sending a value of NetTopologySuite keeps or refuses:
- The SRID is the value's own, sent with the geometry and read from the column, and Z and M are kept: a point of four dimensions comes back with both.
- A
geographyneeds an SRID. SQL Server refuses ageographyof SRID 0, NetTopologySuite's default, withSqlExceptionNumber8023, whose message says the value is not a valid instance ofgeography. Make ageography's values with its SRID, as aGeometryFactoryof SRID 4326 does. Ageometrytakes SRID 0. - A
geographypolygon's shell is counter-clockwise. One whose shell is clockwise throwsArgumentExceptionbefore the query is sent. Reverse it, withReverse(), before the call. - NetTopologySuite's services are taken once. The query methods read and write with
NtsGeometryServices.Instanceas it is at the first query that reads or sends a spatial value, and keep it. An application that uses services of its own, such as another precision model, setsNtsGeometryServices.Instanceat startup, before that query. - EF Core's
UseNetTopologySuite(...)does not set these services. InsideUseSqlServer(...), it configures the services of EF Core, and notNtsGeometryServices.Instance. So the query methods use the defaults of NetTopologySuite unless the application sets it.
JSON
// sqlsource.json, in the project's folder
{
"root": true,
"dialect": "mssql",
"libraryTypes": {
"json": "on",
},
"columnTypes": {
"orders.metadata": { "type": "OrderMetadata", "json": true },
"orders.document": "System.Text.Json.JsonDocument",
},
}
-- name: GetOrder -> one
SELECT id, payload, metadata, document
FROM dbo.orders
WHERE id = @id;
public sealed record GetOrderDto(
long Id,
global::System.Text.Json.JsonElement Payload,
global::MyApp.OrderMetadata Metadata,
global::System.Text.Json.JsonDocument Document);
A json column, the type of SQL Server 2025, is a string, as the types say, until the library types turn System.Text.Json on, named json. Then the json columns and parameters of the queries whose output is repository or methods, and those of an alias type over json, are System.Text.Json.JsonElement. Above, payload is a json, and metadata and document are nvarchar(max) columns that hold JSON, each NOT NULL. The column types give metadata a class of the project, by the JSON opt-in, and document a JsonDocument.
jsonis off until a settings file or a marker turns it on, for a project, a folder, a file or a query. Every .NET project has System.Text.Json, so it needs no package.Nothing is registered in your application. Microsoft.Data.SqlClient reads
jsonand the string types as astring, and reads and sends no type of System.Text.Json. So the query methods convert each value from thatstringas they read it, and to astringbefore they send it.An
nvarcharcolumn stays astringwithjsonon, since nothing says that it holds JSON.Only query methods get
JsonElement. A query whose output ismodelskeeps thestringof the types, and so do its models. A model that such a query shares with a query with a method is SQLSRC257: see library types.Your own choices win. A type override, a column type and a
-- param:or-- column:marker give their type overJsonElement.System.Text.Json's types are yours to choose, with the type override, a column type or a marker, with
jsonon or off. They areJsonElement,JsonDocument, andJsonNode,JsonObjectandJsonArrayofSystem.Text.Json.Nodes. You can choose one for ajsoncolumn, or for any column read as astring, such asnvarchar(max), with no opt-in. Each is parsed from the text of the column, and sent as the text of its JSON, as the parameter's own type.Text that is not JSON throws System.Text.Json's
JsonExceptionwhen it is read. A string column can hold such text.A
JsonObjectread from a JSON array, or aJsonArrayread from an object, throwsInvalidCastException.A
JsonDocumentis yours to dispose. Each one that a method returns, in the property of its row, holds memory until the document is disposed. Dispose it, withusingor once you have read it. One that you send stays yours. AJsonElementneeds no disposal.A class of your own in a
jsoncolumn is read and sent through System.Text.Json's serializer, withjsonon or off. So is one in a string column that holds JSON by the JSON opt-in, such asmetadataabove. What a class is, the options of the serializer, and the converters that replace it, are in a class in a JSON column.Without the opt-in, a class given to a string column is given to Microsoft.Data.SqlClient as it is, unless it has converters. Microsoft.Data.SqlClient then throws
InvalidCastExceptionwhen the query runs.Your converters replace the query methods' own. A converter of your project between one of System.Text.Json's types and
stringis used in its direction, with no error. SqlSource's conversion is kept for the other direction.A JSON
nullis not a SQLNULL. A column that holds the JSONnullis read as aJsonElementof the kindNull, or as aJsonDocumentwhose root is one. This holds whether its property can be null or not.A
JsonNode, aJsonObjectand aJsonArraycannot hold a JSONnull. Into a property that can be null it is null. Into one that cannot, it throwsInvalidCastException, "Column 'node' holds a JSON null.", as a class does.A SQL
NULLgives its property null, or throws as any column does.A
jsoncolumn refuses a value that is not an object or an array. Thejsonof SQL Server 2025 takes no JSON text that is a string, a number,true,falseornullalone. It refuses one with error 13609, "JSON text is not properly formatted", thrown as aSqlExceptionwhen the query runs. So these cannot be sent to ajsoncolumn:- a
JsonElementof the kindNull, or one that holds a number; - a class whose JSON is not an object or an array.
An
nvarchar(max)column takes them.- a
jsonkeeps the structure, not the text. It stores JSON in a form of its own:- the white space between its tokens is dropped;
- escapes such as
\u0041are written as their characters; - of two keys of one name, the first alone is kept, so
{"a":1,"a":2}reads back as{"a":1}.
So a value reads back equal in structure, not in text: compare JSON read back by its structure. An
nvarchar(max)column keeps the text as it was sent.