Using SqlQuery<Dictionary> in Entity Framework 6

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.

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