C# ASP.NET MVC 5 如何执行原始查询？

Question

我正在尝试使用 C# 执行原始查询。

这是我做的

var accounts = conn.Database.SqlQuery<IEnumerable<string>>("SELECT TOP 1 a.* FROM zipcodes_to_accounts AS c " +
"INNER JOIN accounts AS a ON a.id = c.account_id " +
"WHERE c.zip_code = @p0 "+
"ORDER BY a.completed_ll + a.completed_cp ASC", zipcode).ToArray();

然后我想把第一条记录转成json对象。

if (accounts.Count() > 0)
{
    return JsonConvert.SerializeObject( accounts.First() ); 
}

但是查询给我一个错误

The result type 'System.Collections.Generic.IEnumerable`1[System.String]' may not be abstract and must include a default constructor.

accounts 表有一些列是 varchar、datetime、integers。我怎样才能让这个查询起作用？

已更新将 IEnumerable 转换为列表，如答案中建议的那样。但现在 JsonConvert 返回一个空对象。这是我当前的代码

       //getAccount
        public string GetAccount()
        {


            string zipcode = StringHelpers.StringOrNull(Request["zip_code"]);

            if (zipcode != null)
            {

                var accounts = conn.Database.SqlQuery<List<string>>("SELECT TOP 1 a.* FROM zipcodes_to_accounts AS c " +
"INNER JOIN accounts AS a ON a.id = c.account_id "+
"WHERE c.zip_code = @p0 "+
"ORDER BY a.completed_ll + a.completed_cp ASC", zipcode).ToList();
                var firstAccount = accounts.FirstOrDefault();

                if (firstAccount != null)
                {
                    return JsonConvert.SerializeObject(firstAccount);
                }

            }

            return "{}";

        }

当我调试我的代码时，这是我看到的

Answer 1

不确定您使用的是什么 ORM，但警告告诉您无法构造 IEnumerable，因为它是一个接口(interface)。因此 SqlQuery 方法无法知道您期望的返回类型。

尝试用具体类型替换通用约束:

var accounts = conn.Database.SqlQuery<string>("SELECT TOP 1 a.* FROM zipcodes_to_accounts AS c " +
"INNER JOIN accounts AS a ON a.id = c.account_id " +
"WHERE c.zip_code = @p0 "+
"ORDER BY a.completed_ll + a.completed_cp ASC", zipcode).ToArray();