参数为列表时出现 c# dapper 错误 对象<int>类型 <>f__AnonymousType20'1[[System.Int32[] 不存在映射

Question

string sqlQuery = "SELECT SellingPrice, MarkupPercent, MarkupAmount FROM ProfitMargins WHERE QuoteId in @QuoteId";
var profitMargin = await ctx.Database.SqlQuery<dynamic>(sqlQuery, 
    new { QuoteId = new[] { 1, 2, 3, 4, 5 } }
//String.Join(", ", QuoteIds.ToArray()))).ToListAsync();

有人能看到我做错了什么吗?

对象类型不存在映射& lt;> f__AnonymousType20 ' 1[[系统。Int32[]， mscorlib, Version=4.0.0.0，文化=中立，PublicKeyToken=]]到已知托管提供商本机类型。

我从这篇文章中得到了这个想法:SELECT * from X WHERE id IN(…)与Dapper ORM回答:@LukeH

更新:

我需要它返回到一个列表中。看到我的整个功能，我已经根据@JFM发布的答案更改了代码，但现在无法添加.ToListAsync

@JFM

public static async Task<List<dynamic>> GetProfitMargin(List<int> QuoteIds)
{
    using (var conn = new SqlConnection(new MYContext().Database.Connection.ConnectionString))
    {   
       string sqlQuery = "SELECT SellingPrice, MarkupPercent, MarkupAmount FROM ProfitMargins WHERE QuoteId in @QuoteId";
        {
            var profitMargin =  conn.Query<dynamic>(sqlQuery
               , new { QuoteId = new[] { 1, 2, 3, 4, 5 } }).ToListAsync());                    
        }

Answer 1

public static async Task<IEnumerable<dynamic>> GetProfitMargin(List<int> QuoteIds)
    {
        using (var conn = new SqlConnection(new MYContext().Database.Connection.ConnectionString))
        {   
           string sqlQuery = "SELECT SellingPrice, MarkupPercent, MarkupAmount FROM ProfitMargins WHERE QuoteId in @QuoteId";
            {
                IEnumerable<dynamic> profitMargin =  await conn.QueryAsync<dynamic>(sqlQuery
                   , new { QuoteId = new[] { 1, 2, 3, 4, 5 } });                    
            }

如果你不把它映射到一个列表或数组，默认情况下它将是一个IEnuerable

Answer 2

使用Dapper查询和映射动态对我来说很好:

string sqlQuery = "SELECT SellingPrice, MarkupPercent, MarkupAmount FROM ProfitMargins WHERE QuoteId in @QuoteId";
using(var conn = new SqlConnection(myConnString)
{
    var profitMargin = conn.Query<dynamic>(sqlQuery
       , new { QuoteId = new[] { 1, 2, 3, 4, 5 } });
}

Answer 3

不能100%确定问题是什么，但下面是一个如何使用Dynamics的例子:

    [Test]
    public void TestDynamicsTest()
    {
        var query = @"select 1 as 'Foo', 2 as 'Bar' union all select 3 as 'Foo', 4 as 'Bar'";
        var result = _connection.Query<dynamic>(query);
        Assert.That(result.Count(), Is.EqualTo(2));
        Assert.True(result.Select(x => x.Foo == 1).First());
        Assert.True(result.Select(x => x.Bar == 4).Last());
    }

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
}
var query = @"select 1 as 'Id', 'Foo' as 'Name' union all select 1 as 'Id', 'Bar' as 'Name'";
var result = _connection.Query<Person>(query);
foreach (var person in result)
{
    var output = string.Format("Id: {0}, Name: {1}", person.Id, person.Name);
}

有关更多示例，请查看Dapper文档