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