C# Dapper problems with IN statement using string array

Question

C# Dapper problems with IN statement using string array

arrays c# dapper

Question

I have an string array (query.Tags) for filter a list of values and each time, procces just take the first value of string array during the query execution. I tried several combinations but nothing changed. Of course, I tested all of these SQL statements in SQL SERVER View.

Can you tell me what I doing wrong?

public IEnumerable<ActorDto> SearchMembersInLists(ListMembersQuery query)
  {
    IEnumerable<ActorDto> result = null;
    var sql = @"select DISTINCT t.ActorId,
                            a.Id, a.TypeId, a.Name, a.Identifier
                            FROM [ActorTag] t 
                            INNER JOIN [Actor] a ON t.ActorId = a.Id
                            where t.Name IN @tags
     ";
    using (var cnx = DbConnectionFactory.GetDefault().GetConnection())
       {
           cnx.Open();
           var query_result = cnx.QueryMultiple(sql, new { query.Tags});                
           result = query_result.Read<ActorDto>();               
        }           

       return result;        
    }

the original code is this, i just tried to simplify as I could

public IEnumerable<ActorDto> SearchMembersInLists(ListMembersQuery query)
        {
            IEnumerable<ActorDto> result = null;
            var sql = @"
                SELECT DISTINCT a.Id, a.TypeId, a.Name, a.Identifier,a.Description, a.Email, a.PictureUrl, a.DisplayName --Actor
                  FROM [RoleMember] lm
                  INNER JOIN [Actor] a ON lm.ActorId = a.Id
                  WHERE {tag_filter} {lists_filter}
                  ORDER BY a.DisplayName DESC OFFSET @pageIndex ROWS FETCH NEXT @pageSize ROWS ONLY
            ";


            bool has_tags = true;
            bool has_lists = true;
            if (query.Tags != null && query.Tags.Any())
            {
                sql = sql.Replace("{tag_filter}", "a.Id IN (SELECT t.ActorId FROM [ActorTag] t WHERE t.Name IN @tags)");
                has_tags = true;
            }
            else
            {
                sql = sql.Replace("{tag_filter}", "");
                has_tags = false;
            }

            if (query.Lists != null && query.Lists.Any())
            {
                if (has_tags)
                {
                    sql = sql.Replace("{lists_filter}", "AND lm.RoleId IN @lists");
                }
                else
                {
                    sql = sql.Replace("{lists_filter}", "lm.RoleId IN @lists");
                }
                has_lists = true;
            }
            else
            {
                sql = sql.Replace("{lists_filter}", "");
                has_lists = false;
            }

            if (!has_tags && !has_lists){
                sql = sql.Replace("WHERE", "");
            }

            var values = new
            {
                lists = query.Lists,
                tags = query.Tags,               
                pageIndex = query.PageIndex * query.PageSizeOrDefault,
                pageSize = query.PageSizeOrDefault
            };

            using (var cnx = DbConnectionFactory.GetDefault().GetConnection())
            {
                cnx.Open();                 
                result = cnx.Query<ActorDto>(sql, values);             
            }           

            return result;        
        }

Seriocha

View more on Stack Overflow

Marc Gravell · Accepted Answer · 6/27/2016 12:42:18 PM

There is nothing wrong in the code shown, assuming you're using the latest version of dapper. A similar example is shown below (that can be run in a console exe etc). Please check your data is what you expect.

Note; the query code can actually be significantly simplified, but I wanted to keep it as similar to your example as possible. The simple alternative is here:

public static IEnumerable<ActorDto> SearchMembersInLists(ListMembersQuery query)
{
    using (var cnx = GetConnection())
    {
        return cnx.Query<ActorDto>(
            @"select Id, Name from FooActors where Name IN @Tags", new { query.Tags });
    }
}

The full program with the more complex query layout is shown below. The output is:

2: Barney
4: Betty

using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        // reset and populate
        using (var conn = GetConnection())
        {            
            conn.Open();
            try { conn.Execute(@"drop table FooActors;"); } catch { }
            conn.Execute(@"create table FooActors (
                Id int not null primary key identity(1,1),
                Name nvarchar(50) not null);");
            conn.Execute(@"insert FooActors(Name) values(@Name);", new[]
            {
                new { Name = "Fred" },
                new { Name = "Barney" },
                new { Name = "Wilma" },
                new { Name = "Betty" },
            });
        }

        // run a demo query
        var tags = new[] { "Barney", "Betty" };
        var query = new ListMembersQuery { Tags = tags };
        var actors = SearchMembersInLists(query);
        foreach(var actor in actors)
        {
            Console.WriteLine("{0}: {1}", actor.Id, actor.Name);
        }
    }
    public static IDbConnection GetConnection()
    {
        return new SqlConnection(
            @"Initial Catalog=master;Data Source=.;Integrated Security=SSPI;");
    }
    public class ActorDto
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
    public class ListMembersQuery
    {
        public string[] Tags { get; set; }
    }
    public static IEnumerable<ActorDto> SearchMembersInLists(ListMembersQuery query)
    {
        IEnumerable<ActorDto> result = null;
        const string sql = @"select Id, Name from FooActors where Name IN @Tags";
        using (var cnx = GetConnection())
        {
            cnx.Open();
            var query_result = cnx.QueryMultiple(sql, new { query.Tags });
            result = query_result.Read<ActorDto>();
        }

        return result;
    }
}

C# Dapper problems with IN statement using string array

Question

Fastest Dapper Plus Extensions

Expert Answer

Performance

Expression Evaluator