dapper - 如何将 IN 子句用于 Dapper 中的字符串或 GUID 列表

Question

我正在尝试为 IN 子句编写一个 dapper 查询，但它不起作用，抛出转换错误，提示“将 nvarchar 值‘A8B08B50-2930-42DC-9DAA-776AC7810A0A’转换为数据类型时，转换失败。” .在下面的查询中，fleetAsset 将 Guid 转换为字符串。

public IQueryable<MarketTransaction> GetMarketTransactions(int fleetId, int userId, int rowCount)
{
    //Original EF queries which I am trying to convert to Dapper

    //var fleetAsset = (from logicalFleetNode in _context.LogicalFleetNodes
    //                  where logicalFleetNode.LogicalFleetId == fleetId
    //                  select logicalFleetNode.AssetID).ToList();

    ////This query fetches guid of assetprofiles for which user having permissions based on the assets user looking onto fleet
    //var assetProfileIds = (from ap in _context.AssetProfileJoinAccounts
    //                       where fleetAsset.Contains(ap.AssetProfile.AssetID) && ap.AccountId == userId
    //                       select ap.AssetProfileId).ToList();

    var fleetAsset = _context.Database.Connection.Query<string>("SELECT CONVERT(varchar(36),AssetID) from LogicalFleetNodes Where LogicalFleetId=@Fleetid",
        new { fleetId }).AsEnumerable();

    //This query fetches guid of assetprofiles for which user having permissions based on the assets user looking onto fleet
    var sql = String.Format("SELECT TOP(@RowCount) AssetProfileId FROM [AssetProfileJoinAccounts] AS APJA WHERE ( EXISTS (SELECT " +
                            "1 AS [C1] FROM  [dbo].[LogicalFleetNodes] AS LFN " +
                            "INNER JOIN [dbo].[AssetProfile] AS AP ON [LFN].[AssetID] = [AP].[AssetID]" +
                                " WHERE ([APJA].[AssetProfileId] = [AP].[ID])  " +
                                " AND ([APJA].[AccountId] = @AccountId AND LogicalFleetId IN @FleetId)))");
    var assetProfileIds = _context.Database.Connection.Query<Guid>(sql, new { AccountId = userId, FleetId = fleetAsset, RowCount=rowCount });

Answer 1

Dapper 执行扩展，因此如果数据类型匹配，您只需要执行以下操作:

LogicalFleetId IN @FleetId

(注意没有括号)

传入 FleetId (通常通过问题中的匿名类型)这是一个明显的数组或列表或类似的。

如果删除括号后它不起作用，那么有两个问题要问:

LocalFleetId的列类型是什么?

局部变量的声明类型是什么fleetAsset (你作为 FleetId 传入)？

更新:测试用例显示它工作正常:

public void GuidIn_SO_24177902()
{
    // invent and populate
    Guid a = Guid.NewGuid(), b = Guid.NewGuid(),
         c = Guid.NewGuid(), d = Guid.NewGuid();
    connection.Execute("create table #foo (i int, g uniqueidentifier)");
    connection.Execute("insert #foo(i,g) values(@i,@g)",
        new[] { new { i = 1, g = a }, new { i = 2, g = b },
        new { i = 3, g = c },new { i = 4, g = d }});

    // check that rows 2&3 yield guids b&c
    var guids = connection.Query<Guid>("select g from #foo where i in (2,3)")
                                                          .ToArray();
    guids.Length.Equals(2);
    guids.Contains(a).Equals(false);
    guids.Contains(b).Equals(true);
    guids.Contains(c).Equals(true);
    guids.Contains(d).Equals(false);

    // in query on the guids
    var rows = connection.Query(
        "select * from #foo where g in @guids order by i", new { guids })
        .Select(row => new { i = (int)row.i, g = (Guid)row.g }).ToArray();
    rows.Length.Equals(2);
    rows[0].i.Equals(2);
    rows[0].g.Equals(b);
    rows[1].i.Equals(3);
    rows[1].g.Equals(c);
}