How to use IN Clause for list of strings or GUID's in Dapper

Question

I am trying to write a dapper query for IN clause, but it's not working throwing casting error saying "Conversion failed when converting the nvarchar value 'A8B08B50-2930-42DC-9DAA-776AC7810A0A' to data type int." . In below query fleetAsset is Guid converted into 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 });

Answer 1

Dapper performs expansion, so if the data types match, you should just need to do:

LogicalFleetId IN @FleetId

(note no parentheses)

Passing in a FleetId (typically via an anonymous type like in the question) that is an obvious array or list or similar.

If it isn't working when you remove the parentheses, then there are two questions to ask:

• what is the column type of LocalFleetId?
• what is the declared type of the local variable fleetAsset (that you are passing in as FleetId)?

---

Update: test case showing it working fine:

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