C# Dapper problems with IN statement using string array

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

It might help to post the definitions of ListMembersQuery and ActorDto. — Jedidja
– Jedidja, Commented Jun 27, 2016 at 11:50
Hi, dapper author here; can you show what query.Tags is, and ideally confirm that it does actually have multiple values? There's nore really any need for QueryMultiple here - return cnx.Query<ActorDto>(sql, new { query.Tags }); should be fine - it is possible that this is related to the problem? could you try it and see? Also, what version (number) of dapper are you using? — Marc Gravell
– Marc Gravell, Commented Jun 27, 2016 at 12:29
Hi, I am using the last version of Dapper and I already tried simple query too — Seriocha
– Seriocha, Commented Jun 27, 2016 at 13:36

Marc Gravell · Accepted Answer · 2016-06-27 12:42:18Z

2

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

answered Jun 27, 2016 at 12:42

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Marc Gravell Over a year ago

(for reference, the IN @Tags (instead of IN @tags) is just because my local install is configured to be case-sensitive)

Marc Gravell Over a year ago

@ChrisE it should work with List<T> - it even works with just IEnumerable<T> - are you 100% sure? do you have an example that is failing? (I'd love to see it, so I can fix it!)

ChrisE Over a year ago

Hi Mark, sorry, you're right, it does work with List<T> in a more simple example. Must be something else in my code.

Collectives™ on Stack Overflow

C# Dapper problems with IN statement using string array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related