Question
I'm trying to execute a SQL query in EF 6. The select query returns two string columns, e.g. select 'a', 'b', and can have any number of rows.
I'd like to map the result to a dictionary, but I can't get ride of the following error.
Error 1 Cannot implicitly convert type 'System.Data.Entity.Infrastructure.DbRawSqlQuery>' to 'System.Collections.Generic.Dictionary'
This is the code:
using (var db = new EFDbContext())
{
Dictionary<string, string> perms = new Dictionary<string, string>();
perms = db.Database.SqlQuery<Dictionary<string, string>>(TheQuery);
}
I've tried various select and ToDictionary after the query, but none of them worked.
Accepted Answer
You can use SqlQuery to directly populate an object if the object has a default constructor and property setters. The result can then be used to create a dictionary. For example:
public class QueryResult
{
public string A { get; set; }
public string B { get; set; }
}
// the colulmn/alias names need to match property names
string query = "SELECT column1 AS [A], column2 AS [B] FROM ..."
using (var db = new EFDbContext())
{
var perms = db.Database.SqlQuery<QueryResult>(query)
.ToDictionary(r => r.A, r => r.B);
}
Popular Answer
I need to return a list of KeyValuePair from a parametered SQL call to Entity Framework. I built my code based on the previous posts. By having a generic dictionaryResult, you can reuse it to return a list of generic keyvaluePair from any query that has Key Value columns. Here it is if it's useful to anyone:
- a generic class:
private class DictionaryResult<K, V>
{
public K Key { get; set; }
public V Value { get; set; }
}
- define your SQL:
private const string SQL = "select Acct_No as Key, XX as Value from XXXXX where acct_no in ( :accts)";
- use the generic class to return a list of keyvalueapair:
public List<KeyValuePair<int, string>> GetXXX(string accts)
{
using (var securityEntities = ODPFactory.GetSecurityEntities(_ownerRef))
{
var returnValue = securityEntities.ExecuteStoreQuery<DictionaryResult<int, string>>(SQL, new object[] { accts })
.Select(item => new KeyValuePair<int, string>(item.Key, item.Value))
.ToList();
return returnValue;
}
}
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow