Fix a nullable property
Make a nullable property, such as string? or int?, not null, and stop the null reference warning it gives.
Use this when a property is nullable, but its column is never null. Use it too when a property must be nullable, and is not.
Why a property is nullable
SqlSource treats a column as possibly null unless it can show that it never is. See which columns can be null on PostgreSQL and SQL Server. The cost is a null check in your code, never a wrong value. You can overrule it in the .sql file.
The rules you need.
- A column that can be null gets a property that can be null, such as
int?. A column that is never null gets one that cannot, such asint. See Which properties can be null.
Say that a column is never null
If you know that a column is never null, add -- column: <name> not null inside its query:
-- name: ListUserDisplayNames
-- column: display_name not null
SELECT
u.id,
initcap(u.name) AS display_name
FROM users AS u
ORDER BY u.id;
Or end the column's alias with !:
The alias is in double quotes: initcap(u.name) AS "display_name!".
What you get. ListUserDisplayNamesDto.DisplayName is a string, not a string?. ListOrderMonthsDto.MonthEnd is a DateTime, not a DateTime?.
The rules you need.
- The build does not check a
!or anot null. If the column is null after all, the method throws. See How a row is read. - The
-- column:line names the column as the query returns it. It is a comment, so the query needs no describing again. See Setting a column's nullability or type with a marker. - The
!stays in the column's name. A change to an alias changes the query, so rundotnet sqlsource describe. See Which properties can be null and Describing again. - With Dapper, use the
-- column:line, since Dapper matches columns to properties by name. See Which properties can be null.
Say that a column can be null
If a method throws "Column 'name' is null." or "Data is Null.", a null reached a property that cannot be null. See How a row is read. Remove your ! or not null, if any. Otherwise, add -- column: <name> null:
-- name: ListUserNames
-- column: name null
SELECT id, name
FROM users;
Or end the alias with ?: AS "name?" on PostgreSQL, AS [name?] on SQL Server.
What you get. ListUserNamesDto.Name is a string?.
The rules you need.
nullor?gives a property that can be null, whatever SqlSource found. See Which properties can be null.
Make a query parameter nullable
If a query parameter can be null, add null to its -- param: line:
-- name: FindUsers
-- param: @name text null
SELECT
id,
name,
email
FROM users
WHERE @name IS NULL OR name = @name
ORDER BY name;
What you get. FindUsersParams.Name is a string?, so new FindUsersParams(null) finds every user.
The rules you need.
- A query parameter can be null only when its
-- param:line saysnull, or, for a list, when the query tests the list for null. See Which properties can be null. not nullmeans the same as no word, and makes nothing not null. See Giving a parameter a type:-- param:.- After a change of the type or the nullability,
not nullincluded, rundotnet sqlsource describeagain. See Giving a parameter a type:-- param:.
On PostgreSQL
On PostgreSQL, a column most often gets a nullable property when:
- It is on the optional side of a
LEFT,RIGHTorFULLjoin. - It comes from a view, whose nullability PostgreSQL does not record.
- It is a call of a function that the full rules do not list, such as
initcap(name). - It is
sum,max,minoravgwith noGROUP BY, which is null over no rows. - A
WITHquery or a subquery inFROMcomputes it, such as acount(*).
To fix one, say that the column is never null. See Which columns can be null and the limit.
On SQL Server
On SQL Server, a column most often gets a nullable property when:
- It is on the optional side of an outer join or an
OUTER APPLY. - A filter keeps it from being null, such as
WHERE x IS NOT NULL. - It is a function such as
EOMONTH,DATEADDorAT TIME ZONE, even of values that are never null. - It is
CASE WHEN x IS NULL THEN 0 ELSE x END. WriteISNULL(x, 0), which is never null. - It is a count or a sum in a
UNION, or in anOUTPUTclause.
To fix one, say that the column is never null. See Which columns can be null and the limit.
If it does not work
- "Column 'name' is null.", or on SQL Server Microsoft.Data.SqlClient's
SqlNullValueException, "Data is Null.". A null reached a property that cannot be null: say that the column can be null. See How a row is read. On SQL Server, do not runSET ANSI_WARNINGS OFFon the connection: see the limit. - SQLSRC260. The
-- column:line names no column of the result, or the alias already ends in!or?. Keep the mark or the line'snullornot null, not both. See SQLSRC260. - SQLSRC227 on PostgreSQL.
@name IS NULLdoes not fix the type of@name. Give the type in its-- param:line. See SQLSRC227.