Table of Contents

Troubleshooting: it built, but something is wrong

Use this page when the build succeeds but something is wrong: an exception at run time, a wrong result, or nothing happens. It also lists a build error that is not about your SQL.

An exception at run time

  • InvalidCastException for a NodaTime, PostGIS, enum or composite type, on PostgreSQL. A repository made from a connection string makes connections that have none of the plugins and mappings of your data source. See a repository made from a connection string.
  • Null in a column whose property cannot be null, on SQL Server. With ANSI_WARNINGS and ARITHABORT both off, SQL Server returns null where an overflow or a division by zero is otherwise an error. See ANSI_WARNINGS off.
  • A SqlException, and the transaction is rolled back, on SQL Server. An error before the rows can hide a later error of the batch that rolls the transaction back. See a hidden rollback.
  • SqlException 130 for an empty or null list, on SQL Server. A list after a NOT inside an aggregate of your own fails when it is empty or null. See a list in an aggregate of your own.
  • UnexpectedRowCountException, TransactionRolledBackException or CollectionItemException is not caught by its type. Another assembly catches these exceptions by their type only when it can see them. See an exception that is not caught by its type.
  • InvalidCastException from a query method with parameters, on a connection that wraps the driver's. The method works only on a connection whose commands take the driver's own parameters. See a connection that wraps the driver's.
  • NotSupportedException from a collection method, on a connection that wraps the driver's. Most such connections cannot run a batch of commands. See a collection method on a connection that wraps the driver's.
  • InvalidCastException for an id type made by Vogen or StronglyTypedId. SqlSource converts a type only through methods marked [SqlSourceConverter], and such a type has no method marked so. See Vogen and StronglyTypedId.
  • InvalidOperationException, "The connection source 'connections' of 'App.OrderRepository' is null." The connection source of a repository method, or the field, property or parameter that holds its factory, is null. The message names it. See the connection and a factory that a repository holds.
  • "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. See Say that a column can be null.

InvalidCastException

Find the message of the exception in this list. Each entry gives the cause, what to do, and the section that holds the rule.

  • The message names a NodaTime type, such as "Reading as 'NodaTime.Instant' is not supported". The project references Npgsql.NodaTime, so its PostgreSQL query methods read NodaTime's types. But the data source that the method ran on never called UseNodaTime(). Call UseNodaTime() on the builder of that data source, or turn NodaTime off. UseNodaTime() inside EF Core's UseNpgsql(...) teaches only the data source of EF Core. See NodaTime on PostgreSQL.
  • The message names a NetTopologySuite type, such as "Reading as 'NetTopologySuite.Geometries.Geometry' is not supported". The cause is the same as for NodaTime, with Npgsql.NetTopologySuite and UseNetTopologySuite(). Call UseNetTopologySuite() on the builder of the data source, or turn NetTopologySuite off. See NetTopologySuite on PostgreSQL.
  • A NodaTime or NetTopologySuite message from a repository whose connection is a connection string. Such a repository makes a connection of its own, with no plugin at all. Give the repository the data source instead. See a repository made from a connection string.
  • "Unable to cast object of type 'NetTopologySuite.Geometries.LineString' to type 'NetTopologySuite.Geometries.Point'". A column that you mapped to one shape held another. Map the column to NetTopologySuite.Geometries.Geometry, or keep the other shapes out in the query. See NetTopologySuite on PostgreSQL.
  • The message names a class of yours, in a column of a string type that holds JSON, such as a text or an nvarchar(max). Say that the column holds JSON with the JSON opt-in, -> json OrderMetadata. The query methods then read and send the class through System.Text.Json's serializer. See the JSON opt-in.
  • The message names a type of yours, such as a C# enum for a PostgreSQL enum, an id or an Email. A query method asked the driver to read a column, or send a parameter, as a C# type that the driver does not read. Most often a type override, a column type or a -- param: line names the type. The driver was never taught it. Do one of these:
    • Teach the driver the type, on the data source of your application: Npgsql's MapEnum<T>() for an enum, MapComposite<T>() for a composite type, or the plugin of a library such as NodaTime or NetTopologySuite.
    • Add converters: two static methods marked [SqlSourceConverter]. One converts from the type that the driver reads to your type, and the other converts back. The generated method calls them. See converters and the recipe.
  • "Column 'name' is null.", or on SQL Server Microsoft.Data.SqlClient's SqlNullValueException, "Data is Null.". The column held a null, but its property cannot be null. Give the column a nullable property, by its alias or a -- column: line, or keep the null out in the query. See How a row is read and Say that a column can be null.
  • "Column 'name' holds a null element." An element of a converted array held a null, but the element cannot be null. Give the element a type that can be null, or keep the null out in the query. See converters.
  • "Column 'name' holds a JSON null." A JSON column held the JSON null, which is no SQL NULL. It was read into a class or a JsonNode whose property cannot be null. Give the column a nullable property, or keep the JSON null out in the query. See How a row is read.
  • From a method with parameters, on a connection that wraps the driver's. If the commands of the wrapper create or accept only parameters of their own type, the method fails, typically with an InvalidCastException. Call the method on the driver's connection inside the wrapper. See the limit.

A model that looks wrong

  • SQLSRC231 for a parameter after a DECLARE. Under the mssql dialect, a parameter after a DECLARE with no ; can be taken for a variable, and describing the query stops with SQLSRC231. End the DECLARE with a ;. See a parameter taken for a variable.
  • The input model has a property for a name that is not a query parameter, such as @x written for an absolute value on PostgreSQL. SqlSource finds query parameters by how the SQL is written, not by what the database does with it. A MySQL or MariaDB user variable, and the parameter of a procedure that the SQL creates, are read as query parameters too. Their queries need the output sql, under which these names change no generated code. See names that are read as parameters.
  • A property can be null though its column is never null, on PostgreSQL. SqlSource treats many columns as possibly null when it cannot show that they never are. See columns treated as possibly null.
  • A property can be null though its column is never null, on SQL Server. SQL Server treats a column as possibly null when it cannot show that it never is. See columns treated as possibly null.
  • SQLSRC267 in a project that uses EF Core 8 or 9. EF Core's SQL Server provider brings Microsoft.Data.SqlClient 5.1, which is too old. See EF Core and Microsoft.Data.SqlClient.
  • Only the first result set of a query has a model, on SQL Server. A query that returns several result sets gets a model of the first alone, and nothing is reported of the others. See several result sets.
  • A compiler warning at a cref that names one of the exceptions of SqlSource. A project that uses another project's exceptions, and also references a project whose exceptions it cannot see, gets CS0419 or CS1574. See a cref to the exceptions.
  • A compile error about Microsoft.CodeAnalysis.EmbeddedAttribute. SqlSource adds a declaration of this class to your project, and your own declaration of it must join it. See your own EmbeddedAttribute.
  • SQLSRC019 or SQLSRC111 for a type override's key written [dbo].[Email]. A key cannot be written in brackets, as T-SQL writes a name. See a key in brackets.
  • A compiler warning in a model for a ? inside a type argument. SqlSource does not check a ? inside a type argument against the constraints of a generic type. See a ? inside a type argument.
  • One C# type cannot be converted two ways in two files. The converters of a C# type serve every column and parameter of that type in the project. See one conversion for each type.
  • A property is nullable, but its column is never null. You can say so in the .sql file. See Fix a nullable property.
  • A model is wrong for what your database returns, and no entry here explains it. Report it with a bundle of the query. See Reporting a wrong model.

A method returns something you did not expect

  • Part of your SQL is missing from the generated SQL. SqlSource can read a comment or a string differently from your database, and then removes SQL that your database runs. See what is still read differently.
  • An empty list after IN matches the rows whose value is null, on SQL Server. A connection with ANSI_NULLS off takes = NULL as true for a null. See ANSI_NULLS off.
  • Dapper sends no value for a parameter such as @account_id. Dapper sends a property of the input model only to a parameter written as @ and the property's name. See parameter names for Dapper.
  • A rowcount method returns -1, on SQL Server. SET NOCOUNT ON in a query or command that has no parameters stays on for the connection's session. See rowcount after SET NOCOUNT ON.
  • A repository does not use a marked member of its base type, or a factory that a member of its base type holds. A member of the same name in the repository, or in a base type between them, hides it, even a method with other parameters. See a marked member of a base type that is not found.
  • SQLSRC029 for two members of one name, through a holder whose type is a type parameter. The type parameter is constrained to a class and to an interface that the class does not implement. See a factory whose holder has a type parameter as its type.
  • CS1061 in a repository's generated methods, naming the repository. A static connection source of a base type, or a static member that holds your factory, has the repository's name. See a static member of a base type with the repository's name.

A query is not described, or a file is not found

  • Two projects include one .sql file, and entries are missing from its sidecar. A run of the tool on one project can remove the entries that the other project needs. See a file in two projects.
  • SQLSRC224 with Npgsql cannot read the database's types, on PostgreSQL. An operator = of your own on oid or "char", in a schema before pg_catalog on the search path, stops Npgsql from reading the types. See an operator on oid or "char".
  • Describing is slow on a table with thousands of partitions, on PostgreSQL. The tool looks at every partition of a partitioned table. See many partitions.
  • A function marked IMMUTABLE runs while a query is described, on PostgreSQL. PostgreSQL can compute a call of such a function with constant arguments in advance. See what describing does.
  • SQLSRC228 for a -- param: type name, on SQL Server. SqlSource and SQL Server can read a character that Unicode added or reclassified after version 3.2 in different ways. See a type name with such a character.
  • SQLSRC231 for a parameter name with a character that Unicode added or reclassified after version 3.2, on SQL Server. SqlSource and SQL Server can read a character that Unicode added or reclassified after version 3.2 in different ways. See a parameter name with such a character.
  • A query that writes a query parameter's name in two ways fails when it is described, on SQL Server. SqlSource compares names ignoring case alone, and SQL Server by its collation. See a name written in two ways.
  • SQLSRC207 or SQLSRC212 for a project or .sql path that exists. If the current folder is reached through a symbolic link, a full path through the link does not match the real path. See a path through a symbolic link.
  • SQLSRC241 for a query that only another target framework includes. The tool reads a project with several target frameworks for its first framework alone. See several target frameworks.
  • SQLSRC208, and nothing is described for a type whose Source is a constant or nameof. The tool reads [SqlSourceGenerate] from your C# files as text, without compiling them. See Source written as text.
  • A settings file or a .sql file is ignored when the project is built through a symbolic link. A path through a link and the real path reach different settings files. See a build through a symbolic link.
  • SQLSRC241 for a .sql file that a build target of your own adds. The tool leaves out a file that a target adds while the build runs. See a file added by a target.
  • SQLSRC209 on MySQL, SQLite, Oracle or another database. Only PostgreSQL and SQL Server queries can be described, so give the file the output sql. See Use MySQL, SQLite, Oracle or another database.
  • SQLSRC241 or SQLSRC244 after you pull. A query changed, and its sidecar was not described with it. Run dotnet sqlsource describe and commit the sidecars. See Describing again.

sqlfluff does not do what you expect

  • CV06 at the end of a query, and sqlfluff fix does not fix it. With multiline_newline = True, a query of several lines without ; is reported. End it with ;. See CV06 on a query without ;.
  • sqlfluff reports nothing on the white space or comments after a query. The white space and comments between a query without ; and the next query are neither reported nor fixed. End the query with ;. See white space and comments after a query without ;.
  • pip upgrades sqlfluff when it installs the templater, or a pre-commit hook of sqlfluff fails to install. The templater needs sqlfluff 4.5 or later. See sqlfluff 4.4 or older.

The watch does nothing

  • Saving a file starts no pass of describe --watch. The saves of editors other than Rider on macOS, and saves on Windows, have not been checked. See saves that are not seen.
  • In Visual Studio or VS Code, the models do not change after the watch writes a sidecar. What these editors show after a pass has not been checked. See editors that have not been checked.
  • After a folder is deleted and made again, saves in it start no pass. The watch does not see a few changes to folders far from your project. See folder changes that are not seen.

A secret in the output

  • A short password, or a password typed in the wrong place, shows in the output or a log. The tool does not hide a password shorter than four characters, or one that is the name of its logical database or dialect. See a short password.