將集合傳至 SQL Server 中查詢

簡單型別集合

• 以下範例已在 DataBase [dbo].[TestDto] 加上測試資料

Dapper

• 目前似乎只支援轉成 (value1, value2, ...) 這樣型式的語法

public TestDto[] Simple(int[] ints)
{
    var sql = @"
        SELECT *
        FROM [dbo].[TestDto]
        WHERE Id IN @Ints
";
    var param = new DynamicParameters();
    param.Add("@ints", ints);

    var result = _sqlConnection.Query<TestDto>(sql, param).ToArray();
    return result;
}

實際執行的 SQL

exec sp_executesql N'
SELECT *
FROM [dbo].[TestDto]
WHERE Id IN (@Ints1,@Ints2)
',N'@ints1 int,@ints2 int',@ints1=1,@ints2=2

Entity Framework Core

public TestDto[] Simple(int[] ints)
{
    var result = _testDbContext.TestDtos
                               .Where(dto => ints.Contains(dto.Id))
                               .ToArray();
    return result;
}

實際執行的 SQL

SELECT [t].[Id], [t].[Name]
FROM [TestDto] AS [t]
WHERE [t].[Id] IN (1, 2)

---

複雜型別集合

就是以 Table Valued Parameter 方式傳入至 SQL Server 中，傳入後，就是 User Defined Table Types !

• ToDataTable() 這個 Extension Method 就是將 IEnumerable<T> 轉為 DataTable
• 以下範例就是把 DataTable 傳至 DataBase 再進行查詢，將整個資料表回傳 !

Dapper

範例：

• AsTableValuedParameter() 引數就是給定 SQL Server 中的 User Defined Table Types

public TestDto[] Complicated(TestDto[] dtos)
{
    var sql = @"
        SELECT *
        FROM @tables
    ";
    var param = new DynamicParameters();
    param.Add("@tables", dtos.ToDataTable().AsTableValuedParameter("[dbo].[ut_TestDto]"));

    var result = _sqlConnection.Query<TestDto>(sql, param).ToArray();
    return result;
}

實際執行的 SQL

declare @p3 dbo.ut_TestDto
insert into @p3 values(1,N'A')
insert into @p3 values(2,N'B')
insert into @p3 values(3,N'C')

exec sp_executesql N'
SELECT *
FROM @tables
',N'@tables [dbo].[ut_TestDto] READONLY',@tables=@p3

Entity Framework Core

public TestDto[] Complicated(TestDto[] dtos)
{
    var sql = @"
        SELECT *
        FROM @tables
    ";
    var param = new SqlParameter("@tables", dtos.ToDataTable())
                {
                    TypeName = "[dbo].[ut_TestDto]",
                    SqlDbType = SqlDbType.Structured
                };

    var result = _testDbContext.TestDtos.FromSqlRaw(sql, param).ToArray();
    return result;
    }

實際執行的 SQL

declare @p3 dbo.ut_TestDto
insert into @p3 values(1,N'A')
insert into @p3 values(2,N'B')
insert into @p3 values(3,N'C')

exec sp_executesql N'
SELECT *
FROM @tables
',N'@tables [dbo].[ut_TestDto] READONLY',@tables=@p3