Uses Contains in ObjectQuery

Question

Here is my situation:

I have an m: n connection between performers and events. What I'm trying to do is get an IQueryable containing only events that include a particular artist. I use the repository for my data access level. Obviously, the following approach does not 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 the Entity Framework, I really don't understand why, but I can guess. Therefore, if someone can explain this very easily, I would also be grateful. Anyway, my solution that I came up with is 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);

This is not a good solution, since ALL events are retrieved and processed, which is a significant cost. Is there anyone who could tell me the best solution for my problem? I also know that I really did not understand what ObjectQuery is and how to pass it to IQueryable (if possible).

I am grateful for any suggestions.

Answer 1

You can use "Any" with the "Identifier" field, here, provided that the name ArtistId:

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