I have a problem with number of parameters

[sonkt]

I receive a message from MS SQL: "The server supports a maximum of 2100 parameters ". I have a list with many items (about 2480 items), and I use "contain" method like this: "m=> listStudentIds.Contain(m.StudentId)".

@phnx47 How can I resolve this problem?

[phnx47]

SQL limitation which does not allow more than 2100 parameters in a SQL statement.

You can split your query, use just 2100 parameters in 1 query, similar code used for BulkInsert:

dapper-repositories/src/DapperRepository.BulkInsert.cs

Line 49 in 3258450

var exceededTimes = (int)Math.Ceiling(totalInstances * properties.Count / 2099d);

[sonkt]

@phnx47
In Insert operation, we can execute many times. But in query with paging. Do I must call more once times and then join the results?

[phnx47]

Yes, join results after queries.

[sonkt]

@phnx47 . If DB have many rows, then that is not good solution.

[sonkt]

@phnx47 . I see that each item in list was converted to clause of sql query.
var listItems = {1,2,3,4};
listItems.Contain(m.Step)
it converts like this: "Step = @param_1 OR step = @param_2 OR Step = @param_3 OR Step = @param_4".
Can we use IN clause?

[sonkt]

@phnx47 I check In my code. It has one param when generate sql code (pic 2) , but when it executes Count (pic 4) or FinallAsync() then it call to SQL with 4 params. (pic 3). Class in Pic 2 is inherited from DapperRepository.
Pic 1:

Pic 2:

Pic 3:

Pic 4:

[phnx47]

@sonkt Reproduce the issue with a unit test, please.

[sonkt]

@phnx47 I see that, sql generate with GetSelectAll method is right. You can see in Pic 2 . The problem appears when FinAllAsync or Count method was executed.

[phnx47]

@sonkt GetSelectAll and Count don't modify SQL query:

dapper-repositories/src/ReadOnlyDapperRepository.FindAll.cs

Lines 92 to 96 in b4a7577

	public virtual async Task<IEnumerable<TEntity>> FindAllAsync(Expression<Func<TEntity, bool>>? predicate, IDbTransaction? transaction, CancellationToken cancellationToken)
	{
	var queryResult = SqlGenerator.GetSelectAll(predicate, FilterData);
	return await Connection.QueryAsync<TEntity>(new CommandDefinition(queryResult.GetSql(), queryResult.Param, transaction, cancellationToken: cancellationToken));
	}

dapper-repositories/src/ReadOnlyDapperRepository.Count.cs

Lines 146 to 150 in b4a7577

	public virtual int Count(Expression<Func<TEntity, bool>>? predicate, Expression<Func<TEntity, object>> distinctField, IDbTransaction? transaction)
	{
	var queryResult = SqlGenerator.GetCount(predicate, distinctField);
	return Connection.QueryFirstOrDefault<int>(queryResult.GetSql(), queryResult.Param, transaction);
	}

@sonkt Fork this repository, add a unit test with a simple class that reproduces the issue, if you need my help.

[sonkt]

@phnx47 Thanks a lot.
Maybe this problem cause by Dapper. This library generated to correct SQL string which I expected. But when I pass that Sql string to QueryAsync then is pass to SQL Profiler with many params.
Below is my code and Sql string returned in profiler.
var query = SqlGenerator.GetSelectAll(predicate, FilterData); var countQuery = SqlGenerator.GetCount(predicate); var count = Connection.QueryFirstOrDefault<int>(countQuery.GetSql(), countQuery.Param); base.SetOrderBy(strSort).SetLimit((uint)pageSize, (uint)offset); var items = await Connection.QueryAsync<TEntity>(new CommandDefinition(query.GetSql(), query.Param));
SQL string in profiler.
exec sp_executesql N'SELECT dbo.Courses.PK_CourseID AS Id, dbo.Courses.ClosingDate, dbo.Courses.CourseCode, dbo.Courses.CourseName, dbo.Courses.Description, dbo.Courses.ExamDate, dbo.Courses.FK_CompanyID, dbo.Courses.Level, dbo.Courses.OldLevel, dbo.Courses.IsTransfered, dbo.Courses.RegistrationType, dbo.Courses.Report1, dbo.Courses.OpeningDate, dbo.Courses.CreatedDate, dbo.Courses.CreatedUser, dbo.Courses.UpdatedDate, dbo.Courses.UpdatedUser, dbo.Courses.IsDeleted FROM dbo.Courses WHERE dbo.Courses.CourseCode IN (@CourseCode_p01,@CourseCode_p02,@CourseCode_p03,@CourseCode_p04)',N'@CourseCode_p01 nvarchar(4000),@CourseCode_p02 nvarchar(4000),@CourseCode_p03 nvarchar(4000),@CourseCode_p04 nvarchar(4000)',@CourseCode_p01=N'01',@CourseCode_p02=N'02',@CourseCode_p03=N'03',@CourseCode_p04=N'04'

Thank a lot for your support.

[sonkt]

@phnx47 . This problem cause by Dapper. I fixed by re-generate Sql query if have IN clause with too many (1500) params. I created some types TVP and call Dapper QueryAsync method with tvp param instead of array. I think this only fix for MSSQL, and there is problem about performance when using TVP.
base.SetOrderBy(strSort).SetLimit((uint)pageSize, (uint)offset); var queryResult = SqlGenerator.GetSelectAll(predicate, FilterData); var sqlQuery = queryResult.GetSql(); sqlQuery = ReGenerateQuery(queryResult, sqlQuery); var items = await Connection.QueryAsync<TEntity>(new CommandDefinition(sqlQuery, queryResult.Param));