I have two lists called ‘Events’ (custom list) and ‘Timeslots’ (calendar list), Timeslots has a lookup in to the id column of Events. Each event can have multiple timeslots.

I need to show columns from the Events list that have timeslots that occur between 2 user specified dates. I am trying to use Linq to get this information.

If I start at timeslots list I am able to get the titles and ids for all the events that I want:

var events = from timeslot in context.EventTimeslots
                where timeslot.StartTime > new DateTime(2012, 06, 14)
                select new {
                            EventTitle = timeslot.EventID.Title,
                            TimeslotTitle = timeslot.Title,
                            TimeslotStart = timeslot.StartTime

However many of the columns I want to get from the Events list are of types that cannot be projected across the join so this method is not suitable.

If I go and the get the list of timeslots first and then try to query based on that, I can only do so with Linq to objects for the 2nd query so this will not perform efficiently when the system is full of data. This is what I tried:

        var timeslots = (from t in context.EventTimeslots
                         where t.StartTime > new DateTime(2012, 06, 14)
                         select t.EventID.Id).ToList();

        var events = from e in context.Events
                     where timeslots.Contains(e.Id)
                     select new { e.Id, e.EventTitle };

The second part returns all of the items from the Events list and then filters them, so it won’t scale.

So I think I need to do a query on the Events list, with a reverse lookup to check the start date of the associated timeslots, however I cannot get this working and cannot find any examples on the internet, is this possible? The reverse lookup field that SPMetal generated for the Events list is of type EntitySet, but whatever I try to do with it, I cannot get at the underlying entities as I get an exception every time.

I want to do something along the lines of:

var events = from e in context.Events
                where e.EventTimeslotsEvent.StartDate > new DateTime(2012, 06, 14)
                select e.EventTitle;

but this is not even compiling.

Any ideas?

  • Can you post a .wsp that contains the list definitions? – Brian Jul 4 '12 at 2:28
  • Unfortunatly I don't have a solution for them, they are essentially: A Custom list called Events, with a few columns such as Event Owner (person) and Event Type (choice) & a calender list called EventTimeslots, with StartDate(date) EndDate(date) columns, as well as a lookup to the ID of the Events list. I want to return the Title and Event Owner from the Events list, along with the StartDate from the EventTimeslots list. I also need to be able to filter on the StartDate. Hope you can help :-) – toby Jul 5 '12 at 8:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.