0

I have two tables "Event(EventId,EventName,....,UpdatedBy)", "User(UserId,Username...)" and a bridge table "EventAttendee(EventId,UserId)"

from c in context.Events
from u in c.users 
where c.EventId == eventId
select s

the above is doing an inner join so if there are no records in EventAttendee table then I am not getting any records.

I want to always get event details from the Event table and if there are any records in EventAttendee table for that event then get the UserId and Username. How to do this in linq to sql using Entity framework since my entity modal does not show bridge table?

praveen
  • 95
  • 15
  • Can this help? https://stackoverflow.com/questions/5537995/entity-framework-left-join – Renat Apr 29 '19 at 16:42
  • @Renat That link doesn't help me because my entity modal doesn't have bridge table. I've updated my description – praveen Apr 29 '19 at 16:47

1 Answers1

2

Simply add DefaultIfEmpty():

from e in context.Events
from u in e.users.DefaultIfEmpty()
where e.EventId == eventId
select ...

Not sure what s is in theselect, but it could be something like

select new { e.EventName, UserId = (int?)u.UserId, u.UserName }

This will give you a list of the event with all of its attendees, or null for the user-related data when there are no attendees.

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
  • The `c.users` table isn't empty, so that won't help. – NetMage Apr 29 '19 at 19:02
  • @NetMage `c.users` is the users collection of an event. The range variable names were a bit confusing. Renamed them. – Gert Arnold Apr 29 '19 at 19:07
  • @GertArnold If I select the userid and username into list (Ex: select new { e.EventName, EventAttendees = new List{new EventAttedee {UserId = (int?)u.UserId, UserName = u.UserName}}}), this is actually returning EventAttendees with 1 item with userid null and username empty string. How Can I filter this? – praveen Apr 30 '19 at 08:31
  • What do you want to filter? – Gert Arnold Apr 30 '19 at 08:32
  • @GertArnold I don't want to insert items in to list if userid is null – praveen Apr 30 '19 at 13:10
  • But that contradicts "I want to always get event details from the Event table" – Gert Arnold Apr 30 '19 at 14:26