Filter data from a foreign key on an SQL query


I am relatively new to Lambda/Linq, however I want to retrieve all events from a specific calendar where the events are still in the future...

If I use:

EventCalendar eventCalendar;
eventCalendar = db.Events_Calendars.Find(id);

I can get all events and filter by the current date in the view, but I don't think that is the best method.

The Model is as follows:

public class EventCalendar
    public int Id { get; set; }
    public string Calendar { get; set; }
    public virtual List<Event> Events { get; set; }

Event Model is:

public class Event
    public int Id { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public DateTime Start { get; set; }
    public DateTime End { get; set; }
    public int? Capacity { get; set; }

One of my failed attempts at this issue is:

eventCalendar = db.Events_Calendars.Where(x => x.Events.Any(y => y.End >= DateTime.Today));

But it is giving me "Cannot implicitly convert type 'System.Linq.IQueryable<...Models.EventCalendar>' to '...Models.EventCalendar'

EDIT: Added the declaration line... EventCalendar eventCalendar;

Your Events_Calendars.Where returns the IQueryable<Models.EventCalendar> that means a some "query source" of Models.EventCalendar items but Events_Calendars.Find return the one Models.EventCalendar, so you cannot convert a some set of Models.EventCalendar to Models.EventCalendar. You can declarate the new varibale eventCalendars and store the filtering items to it:

var eventCalendars = db.Events_Calendars.Where(x => x.Events.Any(y => y.End >= DateTime.Today)).ToList();

Also you can read about difference between IQueryable and IEnumerable