KeyValuePair from running Raw SQL query on Entity

问题:

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 ?

回答1:

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.

回答2:

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");

回答3:

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)
...
}