How to join EF query with dictionary

Question

In global.asax defined and filled Dictionary

public static Dictionary<Guid, string> GlobalUserDictionary;

In controller some logics load data from db via EF DbContext

var db = new DbModel();

var visits = db.Visits.Select(d => new
{
    Id = d.Id,
    UserId = d.UserId,
    User = MvcApplication.GlobalUserDictionary[d.UserId]
});

but controller throw exception:

System.NotSupportedException: LINQ to Entities does not recognize the method 'System.String get_Item(System.Guid)' method, and this method cannot be translated into a store expression.

How to join dbcontext model with external data resourse like Dictionary or something similar to it?

Answer 1

To use the dictionary inside the Select you need to work with Linq to Object instead Linq to Entities. AsEnumerable extension method is going to help you to do that:

    var visits = db.Visits.AsEnumerable().Select(d => new
    {
        Id = d.Id,
        UserId = d.UserId,
        User = MvcApplication.GlobalUserDictionary[d.UserId];
    });

Update

It's true is not a good idea call AsEnumerable without filtering your data first, but after doing that, it could be a good idea call it:

var visits = db.Visits.Select(d => new
{
    d.Id,
    d.UserId
})
.AsEnumerable()
.Select(d => new
{
    Id = d.Id,
    UserId = d.UserId,
    User = MvcApplication.GlobalUserDictionary[d.UserId]
});

The advantage of using AsEnumerable instead ToList is that AsEnumerable does not execute the query until you consult the data, it preserves deferred execution.

Answer 2

First select the data from the DB you need, then do a select again to make your full model. Don't use AsEnumerable() on the entire DbSet because you'd retrieve columns that you don't need.

var visits = db.Visits.Select(d => new
{
    d.Id,
    d.UserId
})
.ToList()
.Select(d => new
{
    Id = d.Id,
    UserId = d.UserId,
    User = MvcApplication.GlobalUserDictionary[d.UserId]
});