SQL Server: types
Types
-- name: GetOrder
SELECT id, placed_at, total, notes, row_version
FROM dbo.orders
WHERE id = @id;
Each column and each parameter of a described SQL Server query gets a C# type: the type that Microsoft.Data.SqlClient reads a value of its SQL Server type as. If id is a bigint, placed_at a datetime2(7), total a decimal(12,2), notes an nvarchar(max) and row_version a rowversion, they are long, System.DateTime, decimal, string and byte[]. @id is a long. A column that can be null gets its type with ?: a bigint column that can be null is a long?, and an nvarchar one a string?.
- The build gives these types to each query whose output is
repository,methodsormodels, and whose entry in its sidecar is current. They are the types of the properties of the query's models. - A column or parameter that has no C# type is an error, such as SQLSRC246 for
geography. A query with such an error has no models. - To give a type another C# type, use the type override:
"date": "System.DateOnly"in thetypeOverridesof a settings file. - To give one column another C# type, use a column type:
"users.id": "MyApp.UserId"in itscolumnTypes. For one parameter, use its-- param:marker:-- param: @id -> MyApp.UserId. - A query method reads and sends the C# type that you chose as it is. So a type of yours that Microsoft.Data.SqlClient does not read needs converters to and from the type of the table below. Examples are an id, and an
Emailover an alias type. - Rows read through Dapper with a type of your choice need a type handler: see the recipe.
- A project that references NodaTime can give NodaTime's types to the date and time types of its queries whose output is
repositoryormethods: see NodaTime. - A project that references
NetTopologySuite.IO.SqlServerBytescan give NetTopologySuite's types togeographyandgeometryin those queries: see NetTopologySuite. - A project can read its JSON as System.Text.Json's types or as classes of its own, in
jsonor in a string column: see JSON.
Which version of Microsoft.Data.SqlClient
Microsoft.Data.SqlClient 6.1 and 7 read every type as the same C# type. So the types are those of the table below, whatever version of 6.1 or later the project references, directly or through another package:
- Microsoft.Data.SqlClient 6.1 or 7 gets the types of the table.
- Microsoft.Data.SqlClient older than 6.1 is SQLSRC267, an error. Update it to 6.1 or later. EF Core 8 and 9 bring an older one: see limits. Until you update it, no query gets its method, and every type but
vectorgets the type of the table. Avectorcolumn or parameter is SQLSRC270 at its query, since its C# type is first in Microsoft.Data.SqlClient 6.1. - Microsoft.Data.SqlClient 8 or later gets the types of Microsoft.Data.SqlClient 7, with the information SQLSRC268, since this version of SqlSource does not know a newer one.
- A project with no Microsoft.Data.SqlClient gets the types of the table too, but for
vector. An example is a library that holds the queries and leaves the driver to the application. The C# type ofvectoris Microsoft.Data.SqlClient's own, so it is SQLSRC269 until the project references Microsoft.Data.SqlClient. - Such a project gets models alone. Give its queries the output
models, since a query method needs the driver (SQLSRC273). - The older
System.Data.SqlClientpackage is not Microsoft.Data.SqlClient. A project that references it alone has no Microsoft.Data.SqlClient.
The types
The names are SQL Server's, and they are the keys of the type override. In parentheses are the other one-word names that are also keys. The length, precision or scale of a type does not change its C# type: nvarchar(50) and nvarchar(max) are both string, and decimal(12,2) is a decimal.
| SQL Server type | C# type |
|---|---|
bigint |
long |
int (integer) |
int |
smallint |
short |
tinyint |
byte |
bit |
bool |
decimal (dec), numeric, money, smallmoney |
decimal |
float |
double |
real |
float |
date, datetime, datetime2, smalldatetime |
System.DateTime |
time |
System.TimeSpan |
datetimeoffset |
System.DateTimeOffset |
char (character), varchar, text, nchar, nvarchar, ntext, xml, json |
string |
binary, varbinary, image, timestamp (rowversion) |
byte[] |
uniqueidentifier |
System.Guid |
sql_variant |
object |
vector |
Microsoft.Data.SqlTypes.SqlVector<float>. Without Microsoft.Data.SqlClient, SQLSRC269; with one older than 6.1, SQLSRC270 |
hierarchyid, geography, geometry |
None: SQLSRC246. Reference the package Microsoft.SqlServer.Types and map the type with the type override: hierarchyid=Microsoft.SqlServer.Types.SqlHierarchyId, geography=Microsoft.SqlServer.Types.SqlGeography, geometry=Microsoft.SqlServer.Types.SqlGeometry. A query method then reads the column as that type, and sends the parameter as its SQL Server type. For geography and geometry, the query methods of a project that turns NetTopologySuite on get NetTopologySuite.Geometries.Geometry instead |
A type that the table does not name gets its C# type by what it is:
| Kind | C# type |
|---|---|
An alias type, CREATE TYPE dbo.Email FROM nvarchar(320) |
Its base type's C# type: dbo.Email is a string. To give it another, map it with the type override by its schema and name, dbo.Email=MyApp.Email. sysname is a string |
| A CLR type of your own assembly, or a type of a newer SQL Server | None: SQLSRC246. Map it with the type override to the type the application reads it as; the message names the key to write |
What to know about some types
timestampis abyte[]. It is SQL Server's row version, which it also callsrowversion, and not a time. PostgreSQL'stimestampis a time: see type overrides for an entry in a project with both.dateandtimeare aSystem.DateTimeand aSystem.TimeSpanon Microsoft.Data.SqlClient 6.1 and 7. They are never aSystem.DateOnlyor aSystem.TimeOnly.date,datetime,datetime2andsmalldatetimeare read as aSystem.DateTimewhoseKindisUnspecified.datetimeoffsetkeeps its offset.- A query method sends a
datetime2parameter as adatetime2, with all seven digits: see how a parameter is sent. - Dapper sends a
DateTimeparameter as adatetime, to 1/300 of a second. So adatetime2loses digits unless it is sent as adatetime2: see the recipe. float(1)tofloat(24)are afloatin C#, since they are arealto SQL Server.float(25)tofloat(53), andfloatwithout a length, are adouble.sql_variantis anobject. Its value has the C# type of the value that the row holds: anintfor anint, astringfor annvarchar.json, the type of SQL Server 2025, is astring. It is aJsonElementwhere the library types turn System.Text.Json on: see JSON.vectoris aMicrosoft.Data.SqlTypes.SqlVector<float>, a type of Microsoft.Data.SqlClient. The project references Microsoft.Data.SqlClient to name it.
Limits
SQLSRC267 in a project that uses EF Core 8 or 9
What you see. A project that uses EF Core 8 or 9 gets SQLSRC267.
Why. The project references Microsoft.Data.SqlClient 5.1, which the SQL Server provider of EF Core brings.
What to do. Reference Microsoft.Data.SqlClient 6.1 or later from the project itself, beside the EF Core packages:
<ItemGroup>
<PackageReference Include="Microsoft.Data.SqlClient" Version="6.1.7" />
</ItemGroup>