EntityFramework Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are supported in this context

Question

I'm trying to do a ToDictionary() on my entities but I keep getting this error or another one similar like this one but with my entities shown in the message:

Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are supported in this context.

Or this one with my entity in the error msg:

Unable to create a constant value of type 'DataAccess.Posts'. Only primitive types or enumeration types are supported in this context.

I broke the query down to some smaller peace's but still getting either of these error msgs:

var posts = dbContext
    .Posts
    .Where(x => channels.Contains(x.Connection))
    .DistinctBy(p => new { p.Medium, p.ID })
    .OrderByDescending(x => x.Date)
    .Skip(skip)
    .Take(take);

var asociatedTags = dbContext
    .PostTagRelation
    .Where(x => posts.Any(g => g.ItemId == x.ItemId && g.Medium == x.Medium)
        && x.Company == companyId)
    .Select(x => new { x.ItemId, x.Tags })
    .ToList();

Dictionary<string, Tags> dicTags = new Dictionary<string, Tags>();
dicTags = asociatedTags.ToDictionary(g => g.ItemId, g => g.Tags);

I came across a few posts about this, but I can't place them with my situation.

Any help would really be appreciated!

Answer 1

DistinctBy (is it this one?) is probably only an extension method for LINQ-to-Objects (i.e. for IEnumerable<T>, not for IQueryable<T>). That means, calling it executes the DB query to this point and the result is a posts collection in memory (not an IQueryable<Post>) which causes the exception in the second query at posts.Any... because with respect to the second SQL query posts is now a collection of "constant" objects which is not supported by LINQ-to-Entities. Moreover it causes that sorting, Skip and Take are performed in memory and not in the database with possible unnecessary overhead and much more loaded data than you need.

You can try to avoid DistinctBy and replace it by the following which should return posts as IQueryable<Post>:

var posts = dbContext
    .Posts
    .Where(x => channels.Contains(x.Connection))
    .GroupBy(p => new { p.Medium, p.ID })
    .Select(g => g.FirstOrDefault()) // gives the first Post in each group
    .OrderByDescending(x => x.Date)
    .Skip(skip)
    .Take(take);

Answer 2

Do the ToList() call before the creation of the anonymous class (Select(x => new { x.ItemId, x.Tags }))

var dicTags= dbContext.PostTagRelation
   .Where(x => posts.Any(g => g.ItemId == x.ItemId && g.Medium == x.Medium)
       && x.Company == companyId)
   //force execution of the query
   .ToList() 
   //now you have an IEnumerable instead of IQueryable
   .ToDictionary(g => g.ItemId, g => g.Tags);