Nullable input parameter not supplied when NULL

[ChristofColle-VIVES]

Using Dapper 2.1.66 in a C# dotnet10 solution, connecting to a Dataverse database (TDS)...

My code is :

var queryParams = new
{
	FirstName = person.FirstName,
	LastName = person.LastName,
	Email = person.Email,
	AccountId = request.CompanyId
};

var contactId = await sqlConnection.QueryFirstOrDefaultAsync<Guid?>(Data.Queries.FindContactsForRegistration, queryParams);

The first 3 parameters are non-nullable strings.
The fourth parameter is a nullable GUID (uniqueidentifier).

My error output is :
The parameterized query '(@FirstName nvarchar(200),@LastName nvarchar(200),@Email nvarcha' expects the parameter '@AccountId', which was not supplied.

It suggests Dapper isn't providing the @AccountId parameter to the query, when its value is NULL.

[HarnageaGabriel]

Thanks for the report — taking a look at this.

My understanding of the repro: you call QueryFirstOrDefaultAsync<Guid?>(sql, queryParams) where queryParams is an anonymous object with three non-null string properties (FirstName, LastName, Email) and one Guid? property (AccountId) whose value is null. SQL Server (via the Dataverse TDS endpoint) then reports that @AccountId was never supplied, implying Dapper's parameter binder skipped adding a parameter for the property because its value was null, rather than adding it as DBNull.Value.

I dug into CreateParamInfoGenerator in SqlMapper.cs, which is the IL-emitting code that turns anonymous-object/POCO properties into IDbDataParameters, since that's the most likely place a null value could cause a parameter to be silently dropped.

Before making any changes, I built a minimal repro against the current main branch: a fake DbConnection/DbCommand/DbParameterCollection that records exactly what parameters get added, called with the same shape of anonymous object (3 non-null strings + 1 null Guid?) via both Query<Guid?> (sync) and QueryFirstOrDefaultAsync<Guid?> (async), against SQL text referencing all four @-prefixed parameters. In both cases, all four parameters were added, with AccountId correctly set to DBNull.Value — so I have not yet been able to reproduce the "parameter not supplied" behavior on current main.

Given that, a couple of things I'd like to confirm before continuing to dig:

• Is FindContactsForRegistration a plain SQL CommandType.Text query, or a stored procedure (CommandType.StoredProcedure)?
• Could you share the literal parameterized SQL text (or a redacted version) for FindContactsForRegistration, particularly how @AccountId is referenced?
• Does the same repro happen against a plain SQL Server / LocalDB target, or only through the Dataverse TDS endpoint? That would help isolate whether this is a Dapper regression versus a quirk specific to that provider.

If you (or anyone else) can confirm reproduction against a non-Dataverse SQL Server target with Dapper's current main, or share more of the exact query, that'll help narrow this down. Will keep investigating in the meantime.

[ChristofColle-VIVES]

1. My Dataverse query was a plain SQL CommandType.Text query

2. My current query contains a workaround :

-- b/c. Dapper doesn't materialize the @AccountId parameter when NULL, we send an empty GUID instead
DECLARE @GUID_EMPTY UNIQUEIDENTIFIER = CAST(0x0 AS UNIQUEIDENTIFIER);

SELECT contactid
	, score		= results.match_firstname 
				+ results.match_lastname 
				+ results.match_email 
				+ results.match_company
				+ results.has_company
				+ results.is_customer
	, ranking	= ROW_NUMBER() OVER(
		ORDER BY results.match_firstname 
				+ results.match_lastname 
				+ results.match_email 
				+ results.match_company
				+ results.has_company
				+ results.is_customer
			DESC
			, createdon ASC
	)
FROM (
	SELECT c.contactid
		, c.firstname
		, match_firstname	= IIF(c.firstname = @FirstName, 1, 0)
		, c.lastname
		, match_lastname	= IIF(c.lastname = @LastName, 1, 0)
		, c.emailaddress1
		, c.emailaddress2
		, match_email		= IIF(c.emailaddress1 = @Email OR c.emailaddress2 = @Email, 100, 0)
		, c.parentcustomeridname
		, match_company		= IIF(@AccountId <> @GUID_EMPTY AND c.parentcustomerid = @AccountId, 50, 0)
		, has_company		= IIF(c.parentcustomerid IS NOT NULL, 3, 0)
		, c.vivnavor_accountancycustomercode
		, is_customer		= IIF(c.vivnavor_accountancycustomercode IS NOT NULL, 25, 0)
		, c.createdon
  
	FROM dbo.contact c

	WHERE c.statecode = 0
		AND (
			-- exact match in DB
			(c.firstname = @FirstName AND c.lastname = @LastName)
			-- swapped names in DB
			OR (c.firstname = @LastName AND c.lastname = @FirstName)
			-- concatenated names in DB
			OR (REPLACE(c.firstname, ' ' + c.lastname, '') = @FirstName AND REPLACE(c.lastname, c.firstname + ' ', '') = @LastName)
			-- swapped & concatenated names in DB
			OR (REPLACE(c.firstname, ' ' + c.lastname, '') = @LastName AND REPLACE(c.lastname, c.firstname + ' ', '') = @FirstName)
			-- equal/concatenated names from input (e.g. company name as both firstname & lastname)
			OR (
				@FirstName = @LastName
				AND (
					c.firstname + ' ' + c.lastname LIKE @FirstName + '%'
					OR c.lastname + ' ' + c.firstname LIKE @FirstName + '%'
				)
			)
		)
		AND (
			@AccountId = @GUID_EMPTY
			OR c.parentcustomerid IS NULL
			OR c.parentcustomerid = @AccountId
		)

) AS results

ORDER BY ranking ASC;

implemented as :

				var queryParams = new
				{
					FirstName = PersonExtensions.NormalizeNameForLookup(person.FirstName),
					LastName = PersonExtensions.NormalizeNameForLookup(person.LastName),
					Email = PersonExtensions.NormalizeEmailForLookup(person.Email),
					AccountId = request.CompanyId ?? Guid.Empty     // forces Dapper to materialize the @AccountId parameter when NULL
				};
				
				var contactId = await _dataAccess.QueryFirstOrDefault<Guid?>(Data.Queries.FindContactsForRegistration, queryParams);

3. I only needed this on our Dataverse DB, so haven't tried on a plain SQL Server or LocalDB instance.

[HarnageaGabriel]

Thanks for the detail — that's very helpful.

I re-ran my repro harness using your exact SQL (the DECLARE @GUID_EMPTY, the multiple @AccountId references, @FirstName/@LastName/@Email reused inline, everything) with the same anonymous-object shape (FirstName, LastName, Email non-null strings, AccountId a null Guid?), against QueryFirstOrDefaultAsync<Guid?>. Same result as before: Dapper correctly adds @AccountId with Value = DBNull.Value. So with the real query text in the mix, I still can't get Dapper's parameter binder to drop @AccountId on a generic ADO.NET provider.

Since:

• the command type is plain CommandType.Text (confirmed),
• the parameter-binding IL (CreateParamInfoGenerator in SqlMapper.cs) doesn't branch on the runtime value of a property when deciding whether to add a parameter — only on its declared type — and that code path hasn't materially changed since 2.1.66,
• and it reproduces correctly against a standard DbConnection/DbCommand/DbParameterCollection implementation with your literal query text,

this looks like it's not a defect in Dapper's parameter binding itself, but something specific to the Dataverse TDS endpoint's handling of a uniqueidentifier parameter set to DBNull.Value (or possibly how it parses/validates the batch before execution) — separate from the .NET client-side parameter collection that Dapper populates correctly.

If anyone can reproduce the "@accountid was not supplied" error against a plain SQL Server / LocalDB instance (not through Dataverse), that would confirm a real Dapper-side bug and I'll pick this back up immediately. Absent that, I'd lean toward this being a Dataverse-specific quirk rather than something to fix in Dapper — happy to be corrected if I'm missing something.