Passing a list of int to dapper with mySQL-CodePudding

I have a current working solution that is doing a new db call for each project Id in a list and I am trying to do a single call instead that returns data from multiple projects.

To do this I am trying to pass a list of project Id's into a Dapper Query that hits a MySQL database. I either get an error of operand should contain 1 column(s) or I get the first result back and not one per projectId that is in the database. The current c# code I am using is

public List<ProjectPortalManager> GetPPTech(IEnumerable<int> projIds)
{
 string sql = @"SELECT tProject.ProjectID, 
                       tProject.ProjectName, 
                       tProject.PMUserID, 
                       if(cast(tproject.dateinit as char) = '0000-00-00 00:00:00',null,tproject.dateinit) as DateInit,
                       tproject.comments, 
                       tproject.ProjectNumber,
                       c.LName, 
                       c.FName, 
                       c.orgid, 
                       c.orgname as organization, 
                       c.Email, 
                       c.Phone 
                From tProject left Join tContacts c on tProject.PMUserID = c.UserId
                Where tProject.ProjectID in (@ProjIds);";
            try
            {
                List<ProjectManager> pms = Conn.Query<ProjectManager>(sql, new { ProjIds = new[] { projIds } }).ToList();
                return pms;
            }
            catch (Exception ex)
            {
                ErrorReport.ReportError(ex);
            }
 return new List<ProjectPortalManager>();
}

This does not error out but returns 0 results. When running the query in MySQL Workbench I do get one result back. However I am expecting several results. The SQL I run in workbench is:

SET @projIds = ('28, 99, 9');
SELECT tProject.ProjectID, 
       tProject.ProjectName, 
       tProject.PMUserID, 
       if(cast(tproject.dateinit as char) = '0000-00-00 00:00:00',null,tproject.dateinit) as DateInit,
       tproject.comments, tproject.ProjectNumber,
       c.LName, 
       c.FName, 
       c.orgid, 
       c.orgname as organization, 
       c.Email, 
       c.Phone 
From tProject left Join tContacts c on tProject.PMUserID = c.UserId
where tProject.ProjectID IN (@projIds);

I have verified that all the Id numbers used do exists in the database. There seems to be conflicting information online about how to do this but I have not found a solution that seems to work.

CodePudding user response：

Don't put parentheses around the IN if you want Dapper to expand it to a list of parameters and populate them

where tProject.ProjectID IN @PIDs

Suppose you'd passed an array of size 3 in new { PIDs = projIds.ToArray() } - Dapper would effectively transform your SQL to:

where tProject.ProjectID IN (@PIDs1, @PIDs2, @PIDs3)

then behave as if you'd passed new { PIDs1 = projIds[0], PIDs2 = projIds[1], PIDs3 = projIds[2] }