在实体框架上运行RAW SQL查询的KeyValuepair[英] KeyValuePair from running Raw SQL query on Entity Framework

本文是小编为大家收集整理的关于在实体框架上运行RAW SQL查询的KeyValuepair的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有以下查询和代码运行,我希望两列在keyvaluepair上返回.我看到返回的总排是正确的,但是所有的keyValuepairs都是nul!

string query = @"Select id,name from persons";

var persons = context.Database.SqlQuery<KeyValuePair<string,string>>(query);

我已经看过答案说我必须创建一个课程才能获得结果;但是我的问题是我不能在KeyValuepair上得到结果吗?否则我必须有一个匹配属性的类?

推荐答案

问题是 <<<<没有无参数构造函数. EF首先创建一个(通过其无参数构造函数),然后设置其属性来实现一个对象.

其他推荐答案

创建您的对类

public class KeyIntValueString {
    public int Key { get; set; }
    public string Value { get; set; }
}

然后

string sql = "SELECT RoleId AS [Key], RoleName AS [Value] FROM dbo.webpages_Roles";
List<KeyValuePair<int, string>> roles = db.Database.SqlQuery<KeyIntValueString>(sql)
              .AsEnumerable()
              .Select(f => new KeyValuePair<int, string>(f.Key, f.Value))
              .ToList();

,例如,在MVC视图的情况下,请使用KeyValuePair

@model IEnumerable<KeyValuePair<int, string>>
...
@foreach (var item in Model) {
...
    @Html.DisplayFor(modelItem => item.Key)
    @Html.DisplayFor(modelItem => item.Value)
...
}

其他推荐答案

我相信列名需要匹配您试图将其分配给的类型的某些属性.

您可以尝试将查询更改为@"Select id as Key, name as Value from persons";,尽管我认为仅创建POCO类以将结果投影到

中可能会更容易

编辑 您不能以keyvaluepair的方式使用:

The type 'System.Collections.Generic.KeyValuePair`2[System.Int32,System.String]' must declare a default (parameterless) constructor in order to be constructed during mapping.

您应该问自己几个问题:

  • 使用实体框架时,为什么我要编写内联SQL?
  • 为什么我没有可以用来存储此查询结果的类/结构?

我认为真正的答案至少是创建一个要存储到:

的类
public class Person
{
    public int id { get; set; }
    public string name { get; set; }
}

var persons = context.Database.SqlQuery<Person>(@"Select id, name from persons");

本文地址:https://www.itbaoku.cn/post/1556827.html

问题描述

I have the following query and code running and I want the two columns returned on a KeyValuePair. I see the total row returned is right but all the keyvaluepairs are nul !

string query = @"Select id,name from persons";

var persons = context.Database.SqlQuery<KeyValuePair<string,string>>(query);

I have seen an answer saying that I have to create a class to get the result; but my question is can't I get the result on KeyValuePair ? Or I must have a class defined with properties matched ?

推荐答案

The problem is that KeyValuePair doesn't have a parameterless constructor. EF materializes an object by first creating one (by its parameterless constructor) and then setting its properties.

其他推荐答案

create your pair class

public class KeyIntValueString {
    public int Key { get; set; }
    public string Value { get; set; }
}

then

string sql = "SELECT RoleId AS [Key], RoleName AS [Value] FROM dbo.webpages_Roles";
List<KeyValuePair<int, string>> roles = db.Database.SqlQuery<KeyIntValueString>(sql)
              .AsEnumerable()
              .Select(f => new KeyValuePair<int, string>(f.Key, f.Value))
              .ToList();

and, for example in case of mvc view, use KeyValuePair

@model IEnumerable<KeyValuePair<int, string>>
...
@foreach (var item in Model) {
...
    @Html.DisplayFor(modelItem => item.Key)
    @Html.DisplayFor(modelItem => item.Value)
...
}

其他推荐答案

i believe the column names need to match some property of the type you are attempting to assign it to.

can you try changing the query to @"Select id as Key, name as Value from persons"; although i think it might be easier to just create a POCO class to project the results into

edit You cant use KeyValuePair in the manner because:

The type 'System.Collections.Generic.KeyValuePair`2[System.Int32,System.String]' must declare a default (parameterless) constructor in order to be constructed during mapping.

you should ask yourself a few questions:

  • why am i writing inline sql when using entity framework?
  • why can i not have a class/struct that can be used to store the results of this query?

I think the real answer is create at least a class to store into:

public class Person
{
    public int id { get; set; }
    public string name { get; set; }
}

var persons = context.Database.SqlQuery<Person>(@"Select id, name from persons");