如何在Dapper中使用IN子句获取字符串或GUID的列表

Deepak 发表于 Dev

迪帕克

我正在尝试为IN子句编写一个精简查询，但它无法正常工作，并抛出强制转换错误，提示“将nvarchar值'A8B08B50-2930-42DC-9DAA-776AC7810A0A'转换为数据类型int时转换失败。” 。在下面的查询中，Guid转换为string。

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 });

马克·格雷韦尔

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);
}