Filter data from a virtual foreign key on an SQL query

Question

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:

[Table("Events_Calendars")]
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;

score 1 · Answer 1 · answered Aug 02 '17 at 23:07

1

Next to you where clause use .ToList() and also declare either var list = or list

answered Aug 02 '17 at 23:07

Qaiser Mehmood

202
2
10

Wayne Allen · Answer 2 · 2017-08-02T23:25:49.363

1

The Linq Where method returns a list of matching entities. You are trying to return it to a variable defined as a single entity. If you define eventCalendar as type IQueryable<Models.EventCalendar> instead of Models.EventCalendar the Linq statement will work.

Or, alternatively, you can define eventCalendar as the more robust List<Models.EventCalendar> and use the Linq method ToList() at the end of your query. ie. eventCalendar = db.Events_Calendars.Where(x => x.Events.Any(y => y.End >= DateTime.Today)).ToList();

edited Aug 02 '17 at 23:25

answered Aug 02 '17 at 23:09

Wayne Allen

1,605
1
10
16

Unfortunatly it would not be work, because `Events_Calendars.Where` returns `IQueryable`. The `where` result should be casted, for example, to `List` or to other IEnumerable. – George Alexandria Aug 02 '17 at 23:15
@GeorgeAlexandria Well spotted. I have fixed my answer. – Wayne Allen Aug 02 '17 at 23:26

George Alexandria · Accepted Answer · 2017-08-02T23:27:25.520

1

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

edited Aug 02 '17 at 23:27

answered Aug 02 '17 at 23:21

George Alexandria

2,841
2
16
24

Your explanation and example got me past that issue with a better understanding (thanks to the extra read), I still need to limit it by a specific calendar and only list future events. I am struggling with understanding how to filter the virtual Event(s) since it is essentially pulling data from a second table. – thefid Aug 03 '17 at 19:41
Update: I fixed the single calendar issue by adding "&& x.Id == id" to the where clause, but the filter by future date still doesn't work. – thefid Aug 03 '17 at 19:53
@thefid Put info about a new error or exception into the current question if you have it. Are you sure that `db.Events_Calendars` isn't empty and that you need to compare `y.End >= DateTime.Today` but `y.Start >= DateTime.Today`? – George Alexandria Aug 03 '17 at 20:15
The way I have my example query above, it will only return the calendar IF there are future events with that calendar Id and it will return all events regardless of future or past. There is no error. My problem is related to eager loading and wanting to filter that data efficiently in the controller (not the view). I need to move the filter out of the where clause as in our examples above. – thefid Aug 03 '17 at 20:35
`My problem is related to eager loading and wanting to filter that data efficiently` I think that it's a another question how to improve performance of data filtering, where you should post additionally a codes of your view and controller – George Alexandria Aug 03 '17 at 21:09

Filter data from a virtual foreign key on an SQL query

3 Answers3