.NET 6 + Dapper + User-Defined Table Type

大家都知道，对于SQL Server IN是有限制条件的，如果IN里面的内容过多，在执行的时候会被自动截断，因而导致查询到的结果不是实际需要的结果。

select * from Payments where Id in (1,2,3,4,...)

为了解决上面的限制，可以改为利用用户自定义数据类型解决。

具体内容如下：

1. Create User-Defined Data Types

CREATE TYPE [dbo].[IdTable] AS TABLE([Id] [int] NOT NULL
)
GO

2. Create Store Procedure which use IdTable as Input paramter.

CREATE PROCEDURE [dbo].[SP_GET_PAYMENTS]                  @PaymentIds [dbo].[IdTable] READONLY                 
AS   
BEGIN  select p.Id,p.DueDate   from @PaymentIds tp     join Payments p with (nolock) on tp.Id=p.Id 
END  

3. Parameter convert to IdTable. SQLMapper Extension

  public static class Extensions{/// <summary>/// This extension converts an enumerable set to a Dapper TVP/// </summary>/// <typeparam name="T">type of enumerbale</typeparam>/// <param name="enumerable">list of values</param>/// <param name="typeName">database type name</param>/// <param name="orderedColumnNames">if more than one column in a TVP, /// columns order must mtach order of columns in TVP</param>/// <returns>a custom query parameter</returns>public static SqlMapper.ICustomQueryParameter AsTableValuedParameter<T>(this IEnumerable<T> enumerable,string typeName, IEnumerable<string> orderedColumnNames = null){var dataTable = new DataTable();if (typeof(T).IsValueType || typeof(T).FullName.Equals("System.String")){dataTable.Columns.Add(orderedColumnNames == null ?"NONAME" : orderedColumnNames.First(), typeof(T));foreach (T obj in enumerable){dataTable.Rows.Add(obj);}}else{PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);PropertyInfo[] readableProperties = properties.Where(w => w.CanRead).ToArray();if (readableProperties.Length > 1 && orderedColumnNames == null)throw new ArgumentException("Ordered list of column names must be provided when TVP contains more than one column");var columnNames = (orderedColumnNames ??readableProperties.Select(s => s.Name)).ToArray();foreach (string name in columnNames){dataTable.Columns.Add(name, readableProperties.Single(s => s.Name.Equals(name)).PropertyType);}foreach (T obj in enumerable){dataTable.Rows.Add(columnNames.Select(s => readableProperties.Single(s2 => s2.Name.Equals(s)).GetValue(obj)).ToArray());}}return dataTable.AsTableValuedParameter(typeName);}}

4. Dapper code

Dapper version: 2.1.37

using System;
using System.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using Dapper;
using log4net;
using Microsoft.Data.SqlClient;
using static Dapper.SqlMapper;public List<T> QueryEntitiesNoUTC<T>(string sqlCommand, object parameters, CommandType commandType, int commandTimeout = 60){using (SqlConnection Connection = GetSqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString)){try{Connection.Open();List<T> returnValues = Connection.Query<T>(sqlCommand, parameters, commandType: commandType, commandTimeout: GetCommandTimeout(commandTimeout)).ToList<T>();Connection.Close();Connection.Dispose();return returnValues;}catch (Exception e){Log.Error(e);throw;}finally{Connection.Close();Connection.Dispose();}}}

5. C# call Demo

        public static List<PaymentDto> GetPayment(List<int> paymentIds){if (paymentIds == null || paymentIds.Count == 0){return new();}var param = new { PaymentIds = paymentIds.AsTableValuedParameter("dbo.IdTable", new List<string>() { "Id" }) };var result = dapperHelper.QueryEntitiesNoUTC<PaymentDto>("SP_GET_PAYMENTS", param, CommandType.StoredProcedure, SqlConstants.GetBiggerRecordsTimeoutSeconds);return result;}

如果是多个参数：

var param = new { PaymentIds= paymentIds.AsTableValuedParameter("dbo.IdTable", new List<string>() { "Id" }), Status= status};