Skip to content

Invalid parameter name in update query #4144

Description

@akhozov

Hello!

I updated linq2db from 2.9.6 to 5.2.1 and now it creates wrong sql statement for update query.

I have next classes:

public class OeeDimensional
{
    public string MeasureType { get; set; }
    public decimal NormalizedValue { get; set; }
}

[Column("GoodQuantity-NormalizedValue", "GoodQuantity.NormalizedValue")]
[Column("GoodQuantity-MeasureType", "GoodQuantity.MeasureType")]
public class OeeOperationPeriod
{
    [Column("RowKey"), PrimaryKey, Identity]
    public long RowKey { get; set; }

    public OeeDimensional GoodQuantity { get; set; }
}

Then I use next query to update row:

var table = connection.GetTable<OeeOperationPeriod>().DatabaseName(...).TableName(...).SchemaName(...);
table.Where(p => p.RowKey == 1)
    .Set(p => p.GoodQuantity.NormalizedValue, 123)
    .Set(p => p.GoodQuantity.MeasureType, "C")
    .Update();

Linq2db translates this query to next SQL statement:

UPDATE
	[p]
SET
	[p].[GoodQuantity-NormalizedValue] = @GoodQuantity-NormalizedValue,
	[p].[GoodQuantity-MeasureType] = @GoodQuantity-MeasureType
FROM
	[Table] [p]
WHERE
	[p].[RowKey] = @RowKey

This is invalid statement because contains parameters with wrong name: @GoodQuantity-NormalizedValue and @GoodQuantity-MeasureType.

In my previous version (2.9.6) it provided another SQL statement (with constants instead of parameters):

UPDATE
	[p]
SET
	[p].[GoodQuantity-NormalizedValue] = 123,
	[p].[GoodQuantity-MeasureType] = N'C'
FROM
	[Table] [p]
WHERE
	[p].[RowKey] = @RowKey

Currently I rewrite my query with another Set method:

var table = connection.GetTable<OeeOperationPeriod>().DatabaseName(...).TableName(...).SchemaName(...);
table.Where(p => p.RowKey == 1)
    .Set(p => p.GoodQuantity.NormalizedValue, () => 123)
    .Set(p => p.GoodQuantity.MeasureType, () => "C")
    .Update();

And it gets me next SQL statement:

UPDATE
	[p]
SET
	[p].[GoodQuantity-NormalizedValue] = @NormalizedValue,
	[p].[GoodQuantity-MeasureType] = @MeasureType
FROM
	[Table] [p]
WHERE
	[p].[RowKey] = @RowKey

Stack trace

Incorrect syntax near '-'.
Must declare the scalar variable "@GoodQuantity".
[Exception data]
[HelpLink.ProdName] = "Microsoft SQL Server"
[HelpLink.ProdVer] = "15.00.2000"
[HelpLink.EvtSrc] = "MSSQLServer"
[HelpLink.EvtID] = "102"
[HelpLink.BaseHelpUrl] = "http://go.microsoft.com/fwlink"
[HelpLink.LinkId] = "20476"

===

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at LinqToDB.Data.DataConnection.ExecuteNonQuery(DbCommand command) at LinqToDB.Data.DataConnection.ExecuteNonQuery() at LinqToDB.Linq.QueryRunner.NonQueryQuery(Query query, IDataContext dataContext, Expression expr, Object[] parameters, Object[] preambles) at LinqToDB.Linq.QueryRunner.<>c__DisplayClass32_0.<SetNonQueryQuery>b__0(IDataContext db, Expression expr, Object[] ps, Object[] preambles) at LinqToDB.Linq.ExpressionQuery1.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
at MyMethod()

Environment details

Linq To DB version: 5.2.1

Database (with version): SQL Server 2019

Operating system: Windows

.NET Version: 4.7.2

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

status: has-prThere is active PR for issue

Type

No type

Projects

No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions