Unable to create a constant value of type (type) Only primitive types ('such as Int32, String, and Guid') are supported in this context

Question

I've read ALL of:

• Unable to create a constant value of type 'System.Object' in Entity Framework

• Entity Framework - "Unable to create a constant value of type 'Closure type'..." error

• Entity Framework - Union causes "Unable to create a constant value of type.."

• Only primitive types ('such as Int32, String, and Guid') are supported in this context

and searched a bit more, but still no solution. I've seen that this happens on EF 3.5 and in 4.0 the Contains method should be supported, but I'm in EF 4 but I'm getting this error. I have a photo gallery, where albums can have any number of different photos, and each photo can belong to any number of albums. So this is a many-to-many relationship.

I've got a VisibleObjects property which is used by about 100 other methods that work well, but I'm pasting it anyway: (I'm definitely sure the problem is not caused by something here)

static IQueryable<GlobalObject> VisibleObjects
    {
        get
        {
            return from obj in db.GlobalObjectSet where obj.IsVisible && !obj.SiteUser.IsDeactivated orderby obj.ID descending select obj;
        }
    }

I've tried several different queries:

I have a VisiblePhotos property:

This wasn't working:

static IQueryable<Photo> VisiblePhotos(this Album a)
    {
        return from p in VisibleObjects.OfType<Photo>() where a.Photos.Contains(p) select p;
    }

Changed to this:

static IQueryable<Photo> VisiblePhotos(this Album a)
    {
        return from p in VisibleObjects.OfType<Photo>() where a.Photos.Any(other => p.ID == other.ID) select p;
    }

Still didn't work.

Here is the calling method:

public static List<Photo> GetLatestPhotosByAlbum(Album alb, int count = 3)
    {
        lock (sync)
        {
            return alb.VisiblePhotos().OrderByDescending(p => p.ID).Take(count).ToList();
        }
    }

Wasn't working, changed to this:

public static List<Photo> GetLatestPhotosByAlbum(Album alb, int count = 3)
    {
        lock (sync)
        {
            return (from p in VisibleObjects.OfType<Photo>()
                    where alb.Photos.Any(ph => ph.ID == ph.ID)
                    select p).ToList();
        }
    }

Still isn't working. Complaining about not being able to create a constant of my Photo object type, which is an Entity object with an ID property, if it helps. I am not sure of the real cause of the error and I don't have any other ideas of queries in my mind. I think the method name is pretty self explanatory: I'm trying to get the photos in a given album. Loading album entries into memory is not a solution, the query should run on database, not memory. I need an explanation of this exception, why it is occuring here, and how can I get my query to work.

Answer 1

It will not work because you want to use local Album in linq-to-entities query. You must either use navigation property on p to get its album:

var query = from p in VisibleObjects.OfType<Photo>()
            where p.Album.Id == alb.Id
            select p;

or you must build complex query with some join between photos and albums. You cannot pass local object and any its relation to the query. Only simple properties can be passed.

Answer 2

I think that EF is trying to convert where a.Photos.Contains(p) into SQL like WHERE p IN (a.Photos), but it doesn't know how to express a.Photos in SQL. The SQL you want probably looks like WHERE p.Id IN (1, 2, 3), so you could try doing that in C#:

static IQueryable<Photo> VisiblePhotos(this Album a)
{
    var photoIds = a.Photos.Select(p => p.Id).ToArray();
    return from p in VisibleObjects.OfType<Photo>() where photoIds.Contains(p.Id) select p;
}