Using SqlQuery<Dictionary<string, string>> in Entity Framework 6

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.

Answer 1

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

Answer 2

Are you trying to create a Dictionary with Key-Value pairings corresponding to the respective values in the two columns returned via your query?

If so you would be best served querying twice, and create two Lists of strings and creating a Dictionary using those.

List<string> keys = new List<string>();
List<string> values = new List<string>();

//Populate Lists with data from LINQ db call

Dictionary<string, string> dict = keys.ToDictionary(x => x, x => values[keys.IndexOf(x)]);

This requires that the Lists are of the same size and column you designate as keys holds unique values.