Using SqlQuery> in Entity Framework 6

Question

Using SqlQuery> in Entity Framework 6

.net c# dictionary entity-framework sql

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.

Akbari

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;
            }
        }

wxm146

View more on Stack Overflow

jjj · Accepted Answer · 6/11/2015 6:01:15 AM

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

Using SqlQuery> in Entity Framework 6

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related