Dapper.Net: IEnumerable<long> parameter throws exception: No mapping exists from object type System.Int64[] to a known managed provider native type

Question

I am using Dapper.Net against SQL Server 2008 R2 in the following code to pass a List<long> parameter to run a SQL query that has a WHERE IN clause, but I get the exception:

No mapping exists from object type System.Int64[] to a known managed provider native type.

By the way, company_name_id and industry_id are bigint types.

var parameters = new DynamicParameters();
parameters.Add("@companyNameId", entry.Id);
parameters.Add("@industryIds", deletedIndustryIds);
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["server1"].ConnectionString))
{
    connection.Open();
    connection.Execute("DELETE FROM company_name_industry_map WHERE company_name_id = @company_name_id and industry_id IN @industryIds", param: parameters);
    connection.Close();
}

I did see in the github documentation that Dapper has List support, but I was wondering List<long> is supported.

According to the older documentation on https://code.google.com/p/dapper-dot-net/ it does seem like only int[] is supported.

Answer 1

It seems like the exception happens you use try to pass a list if the parameter object is of type DynamicParameters.

My workaround was create set param object as an anonymous object; the following code worked instead.

using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["server1"].ConnectionString))
{
    connection.Open();
    connection.Execute("DELETE FROM company_name_industry_map WHERE company_name_id = @company_name_id and industry_id IN @industryIds",
new { company_name_id = entry.Id, industryIds = deletedIndustryIds });
    connection.Close();
}