11

I've read ALL of:

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.

Community
  • 1
  • 1
Can Poyrazoğlu
  • 33,241
  • 48
  • 191
  • 389

4 Answers4

12

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.

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • As I said, Photo-Album is a many-to-many relationship, so no `p.Album.ID` is available, `p` has collection of `Album`s – Can Poyrazoğlu Aug 28 '11 at 12:53
  • 1
    @CanPoyrazoğlu you probably figured this out but for anyone else in the same position: `VisibleObjects.OfType().Where(p => p.Albums.Any(a => a.Id == album.Id))` – Alex Oct 19 '15 at 08:27
5

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;
}
Douglas
  • 36,802
  • 9
  • 76
  • 89
  • Well wouldn't calling `ToArray` first load the photos to memory? I want database execution. – Can Poyrazoğlu Aug 28 '11 at 12:52
  • So it would need to be expressed as a join or a sub query, try this answer for more ideas: http://stackoverflow.com/questions/1991993/entity-framework-sub-query/1992090#1992090 – Douglas Aug 28 '11 at 13:05
  • it says "If you absolutely must do it the "IN" way, I suggest going over the answers in http://stackoverflow.com/questions/374267/contains-workaround-using-linq-to-entities " the answer there says EF4 supports Contains directly, but Contains is not working for me. weird. as I have EF4, I should be going with Contains, but I can't. – Can Poyrazoğlu Aug 28 '11 at 13:10
0

I ran into a similar problem, and instead of IQueryable, I tried using List, and it worked. May be of some help.

Anshul
  • 1,302
  • 3
  • 15
  • 36
  • when you use List (or array, or IEnumerable, or any collection type other than IQueryable as far as I know), the query is instantly executed and the results are fed from the source (if it wraps around an SQL query, it does a full trip to the SQL server and back, with all the results). this may not be the desired case, i.e. you want to build a single query using multiple IQueryables, without connecting to the server, and get the results with a final/single query. it is a design decision, and in my example, I wanted to keep the IQueryable without a trip to the actual data source. – Can Poyrazoğlu Jul 19 '12 at 13:02
  • that makes sense.. in my case the round trip was not a concern therefore using the list was appropriate.. but i agree that this might not be the proper way to do it if round trips are a concern.. btw, were you able to solve your problem? – Anshul Jul 19 '12 at 18:32
  • yes I've solved it. I don't remember what I exactly did then (it was almost about a year ago) but using an ID primary key for EVERYTHING in the database usually avoids these issues. so I probably gave an ID to everything and also the relationships, and used this: a.x == b.y instead of a.x.ID == b.y.ID. something like that. I honestly don't remember exactly (and I currently don't have the source with me) – Can Poyrazoğlu Jul 21 '12 at 10:18
  • 1
    I see... i guess EF maybe does some internal operations when you refer to the object rather than the ID.. thanks for the response, this may be of use to me one day – Anshul Jul 21 '12 at 13:57
0

I tried another way around and it worked: 

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

Quite weird to see this works but the other one not. But I'm still wondering why Contains is not working.

Shimmy Weitzhandler
  • 101,809
  • 122
  • 424
  • 632
Can Poyrazoğlu
  • 33,241
  • 48
  • 191
  • 389
  • 1
    As I said in my answer. Your former solution cannot work because you cannot pass instance of Album to the query. You must use data which are received by the query like you did in your answer. Contains also works only with scalar values not objects. – Ladislav Mrnka Aug 28 '11 at 16:51
  • i see.. but i don't understand why my second `VisiblePhotos` didn't work (the one with `Any`) – Can Poyrazoğlu Aug 28 '11 at 17:10
  • Still the same story. You are combining local object with linq-to-entities query. – Ladislav Mrnka Aug 28 '11 at 20:03
  • one thing I don't get, what EF can't translate... `p.Albums` should map to a relationship table between photos albums, the `Contains` method should map to a `WHERE IN` query checking the table values by looking at the foreign keys of albums' IDs, but somehow it doesn't work. am i missing something? the implementation could be different, but the idea should work. – Can Poyrazoğlu Aug 28 '11 at 20:47
  • 1
    The idea should work but simply EF is not clever enough to translate `p.Albums.Contains(a)` to checking of Id. It tries to use whole `a` which is not possible. – Ladislav Mrnka Aug 28 '11 at 20:52