3

Here is my situation:

I've got a m:n-relation between artists and events. What I'm trying to do is to get a IQueryable containing only events that include a certain artist. I'm using a repository for my data access layer. Obviously the following approach doesn't work:

_db.EventSet.Include("Location").Where(e => (e.Artists.Contains(artist)) && (e.StartDate > System.DateTime.Now)).OrderBy(e => e.StartDate);

Since I'm new to Entity Framework I don't really understand why, but I can guess. So in case someone could explain this in a very easy way I would be grateful too. Anyway, my solution I came up with looks as follows:

IQueryable<Event> events = _db.EventSet.Include("Location").Where(e => (e.EndDate > System.DateTime.Now));
List<Event> eventsList = new List<Event>();
foreach (var ev in events)
{
    eventsList.Add(ev);
}
return (IQueryable<Event>)eventsList.AsQueryable().Where(e => e.Artists.Contains(artist)).OrderBy(e=>e.StartDate);

That this is not a good solution at all, since ALL the events are retrieved and processed which is a massive overhead. Is there anybody out there who could tell me a better solution for my problem? I'm also aware of the fact that I didn't really understand what an ObjectQuery is and how to cast it to a IQueryable (if that's even possible).

I'm grateful for any suggestions.

Kendall Frey
  • 43,130
  • 20
  • 110
  • 148
stephane beer
  • 31
  • 2

1 Answers1

2

You can use Any with the Artist ID field, here assuming a name of ArtistId:

_db.EventSet.Include("Location")
            .Where(e => (e.Artists.Any(a => a.ArtistId == artist.ArtistId)) && (e.StartDate > System.DateTime.Now))
            .OrderBy(e => e.StartDate);
Jeff Ogata
  • 56,645
  • 19
  • 114
  • 127
  • @Saeed, why don't you think it works? Granted, I do not know the exact schema of the OP's database, but I set up a db with Events, Artists, and Locations, tested it, and it worked. – Jeff Ogata Nov 16 '10 at 18:38
  • I think, `(e.Artists.Any(a => a.ArtistId == artist.ArtistId)` couldn't be handled, because you don't have a fetched data to do search on it, just guess, It's `EF` if it was AsEnumerable() without differ execution like what I said it should to work but in this case i think it can't makes sql query. – Saeed Amiri Nov 16 '10 at 18:58
  • Whats difference between Any and contains in this case? – Saeed Amiri Nov 16 '10 at 19:00
  • @Saeed, the `Contains` will throw an exception, `Only primitive types ('such as Int32, String, and Guid') are supported in this context.`. I believe this is because EF cannot translate the `artist` object into sql. So instead, use `Any` and give EF the `ArtistId` (for me I used a `guid`). – Jeff Ogata Nov 16 '10 at 19:18
  • You used `e.Artists` and I think it doesn't work, but if it's working is amazing – Saeed Amiri Nov 17 '10 at 04:46