LINQ LEFT JOIN where clause not working

Question

I need to return a list of all the events and any rsvps a user may have for an event. However, regardless of the username I pass, It returns every single rsvp. My linq Query ->

   return (from events in this._context.Context.Events
           join rsvps in (this._context.Context.RSVPs
                          .Where(o=> o.UserName == userName))
           on events equals rsvps.Event into re
           from rsvps in re.DefaultIfEmpty()
           select events);

Relationship is
Events.EventID = RSVPs.EventID

Something seems wrong here - specifically the part `on events equals rsvps.Event` A rewrite may be necessary. What field relates the Events table to RSVPs table? — John Gathogo, Feb 26 '13 at 06:42
@user1106741 write `on events.EventID equals rsvps.EventID into re` — Sergey Berezovskiy, Feb 26 '13 at 06:56

score 17 · Accepted Answer · answered Feb 26 '13 at 07:00

17

from e in _context.Context.Events
join r in _context.Context.RSVPs.Where(o => o.UserName == userName)
    on e.EventID equals r.EventID into g
select new {
    Event = e,
    Rsvps = g
};

answered Feb 26 '13 at 07:00

Sergey Berezovskiy

232,247
41
429
459

I don't want to create an anonymous type – user1106741 Feb 26 '13 at 07:06
@user1106741 then use navigation property of event – Sergey Berezovskiy Feb 26 '13 at 08:03

score 1 · Answer 2 · answered Feb 26 '13 at 07:11

Do it this way:

return (from events in this._context.Context.Events
        join rsvps in this._context.Context.RSVPs
        on events.EventIDequals equals rsvps.EventID into re
        from c in re.DefaultIfEmpty()
        where c.UserName == userName
        select new {events,rsvps});

score 0 · Answer 3 · answered Feb 26 '13 at 07:12

If you just want to filter the RSVPs property of your Events in-place, then I guess you probably could do use something like

var events = _context.Context.Events;

foreach(var event in events)
{
    // Assuming the property is named RSVPs
    event.RSVPs = event.RSVPs.Where(o => o.UserName.Equals(userName));
}

return events;

I wouldn't consider it that neat though.

LINQ LEFT JOIN where clause not working

3 Answers3

Linked