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

5
votes

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!

2
c#linqentity-framework
If it is of any matter: .DistinctBy() is afaik no Out of the box method. Google says it comes with MoreLinq. – Marco
True, it's an extension on the LINQ. – Yustme
DistinctBy is probably only an extension 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 which causes the exception in the second query at posts.Any.... Moreover it causes that sorting, Skip and Take are performed in memory and not in the database with much more loaded data than you need. I'd say, avoid DistinctBy. – Slauma
If i lose the DistinctBy I'll get duplicate posts. What can I use in stead of DistinctBy? – Yustme
Maybe something like: GroupBy(p => new { p.Medium, p.ID }).Select(g => g.FirstOrDefault()). – Slauma

2 Answers

4
votes

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);
1
votes

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