join multiple table with where condition

Question

I have some questions here. This is the code which displayed all the meeting details. and now i would like to add one more condition, which is where ar.Response == "ConfirmedAtVenue" this response attribute will be stored in AttendeeResponse table. and i could not make it work, i always get an error or null exception . I had tried to put in the last row or even after the join j2, but still not working.

     private MeetingIndexViewmodel GetMeetingDetailsForIndexView(int id)
    {
        //Names
        string AttendeeNameMatch = GetAttendeeName(id);
        string ConvenerNameMatch = GetConvenerName(id);
        string OrganiserNameMatch = GetOrganiserName(id);
        //Telephone Numbers
        int AttendeeTelNoMatch = GetAttendeeTelNo(id);
        int ConvenerTelNoMatch = GetConvenerTelNo(id);
        int OrganiserTelNoMatch = GetOrganiserTelNo(id);
        //Email
        string AttendeeEmailMatch = GetAttendeeEmail(id);
        string ConvenerEmailMatch = GetConvenerEmail(id);
        string OrganiserEmailMatch = GetOrganiserEmail(id);

        var meetingDetails = (from p in db.Persons
                             join ts in db.Timeslots on p.PersonID equals ts.PersonID into j1
                             from ts in j1.DefaultIfEmpty()
                             join ar in db.AttendeeResponses on ts.TimeslotID equals ar.TimeslotID into j2
                             from ar in j2.DefaultIfEmpty()
                             join pm in db.PersonMeetings on ar.PersonMeetingID equals pm.PersonMeetingID into j3
                             from pm in j3.DefaultIfEmpty()
                             join m in db.Meetings on pm.MeetingID equals m.MeetingID into j4
                             from i in j4.DefaultIfEmpty()
                             where i.MeetingID == id && pm.MeetingRole.Equals("Attendee") //&& ar.Response.Equals("NoResponse")
                             //The above two lines were needed for the null MeetingIDs to be reported
                              select new MeetingIndexViewmodel
                              {
                                  MeetingID = id,
                                  AttendeeName = AttendeeNameMatch,
                                  ConvenerName = ConvenerNameMatch,
                                  OrganiserName = OrganiserNameMatch,
                                  StartTime = ts.StartTime.ToString() ?? "null",
                                  Duration = ts.Duration.ToString() ?? "null",
                                  AttendeeResponseID = ar.AttendeeResponseID,
                                  Response = ar.Response,
                              }
                              ).FirstOrDefault();
        return meetingDetails;
    }

Not what you are asking, but you can simplify your "left joins" by using this: `from p in db.Persons join ts in db.Timeslots.Where(t => p.PersonID == t.PersonID).DefaultIfEmpty() join ar in db.AttendeeResponses.Where(a => ts.TimeslotID == a.TimeslotID).DefaultIfEmpty()`. More about this stragegy: http://stackoverflow.com/a/23558389/2321042 — Andrew, Jul 16 '16 at 03:24
About `ts.StartTime.ToString() ?? "null"`, the `ToString` method will never return `null`, if `StartTime` is null, you will get an exception there. Also, having a string with "null" value may be confusing; probably setting it to empty or even actual `null` is a better idea. — Andrew, Jul 16 '16 at 03:28
@Andrew i dont think the problem will be occurs in the bottom part. as im improving someone's code, the code I posted is working. but now i want to sort the response = confirmed .... but this does not work at all if i add one more condition after the where clause — itdd, Jul 16 '16 at 04:13
I was analyzing your query further to give you a full response and I found several issues. For example, you are left joining entities, but then you have them in your where condition. What is the intended result? Do you want a record if there is no join with `AttendeeResponses`, `PersonMeetings` or `Meetings`? Perhaps that's what the original author had in mind when he added those incorrect `??`. For example, if there are no joins with `AttendeeResponses`, you would get an exception in `ar.AttendeeResponseID`, and if there are no joins with `Meetings`, you would get one in `where i.MeetingID`. — Andrew, Jul 16 '16 at 21:03
@Andrew my intended result is , all meeting with response of confirmed will be displayed. and the response attribute is stored in the attendeeresponse table. im new to linq, so i cant really understand the left join and ifdefaultempty... — itdd, Jul 17 '16 at 05:44
A left join means that if you don't have any match with any of the tables, you still get a row from the previous tables and null in the joined ones (i.e.: someone who never was in any meeting). With a inner join, you only get rows which have data in all the tables. From what you say it seems you need an inner join by removing the lines with the `from .... DefaultIfEmpty`, and only use the alias from the `join` line above (`m` for `Meetings`, not `i`). — Andrew, Jul 17 '16 at 12:22
@andrew tried this but not working, removed all the lines and "into j1" "into j2"... ``where ar != null && m.MeetingID == id && pm.MeetingRole.Equals("Attendee") && ar.Response.Equals("ConfirmedAtVenue")`` — itdd, Jul 17 '16 at 14:13
i realised something, will the query be affected by this FirstOrDefault()? — itdd, Jul 17 '16 at 15:12
`FirstOrDefault` is applied on a collection, and it returns its first item or the first item that satisfies the linq expression (if given), or it return null if there is no item to return. On the other hand, the method `First` does the same but it throws an exception if there is no item to return. — Andrew, Jul 18 '16 at 21:35

Andrew · Answer 1 · 2016-07-27T09:40:01.907

1

From your comments it seems that you need a standard inner join, so I think this should work:

var meetingDetails = from p in db.Persons
                     join ts in db.Timeslots on p.PersonID equals ts.PersonID
                     join ar in db.AttendeeResponses on ts.TimeslotID equals ar.TimeslotID
                     join pm in db.PersonMeetings on ar.PersonMeetingID equals pm.PersonMeetingID
                     join m in db.Meetings on pm.MeetingID equals m.MeetingID
                     where m.MeetingID == id
                     && pm.MeetingRole == "Attendee"
                     && ar.Response == "ConfirmedAtVenue"
                     select new MeetingIndexViewmodel
                     {
                         MeetingID = m.MeetingID,
                         AttendeeName = AttendeeNameMatch,
                         ConvenerName = ConvenerNameMatch,
                         OrganiserName = OrganiserNameMatch,
                         StartTime = ts.StartTime != null ? ts.StartTime.ToString() : "null", // or null or string.Empty
                         Duration = ts.Duration.ToString(), // Use this approach if StartTime and Duration can't be null
                         AttendeeResponseID = ar.AttendeeResponseID,
                         Response = ar.Response
                     };

EDIT

As this is not working, you should go table by table until you find what's giving you no results. For example:

// Joining Persons and Timeslots
var details1 = from p in db.Persons
               join ts in db.Timeslots on p.PersonID equals ts.PersonID
               select p;

// Joining all tables
var details2 = from p in db.Persons
               join ts in db.Timeslots on p.PersonID equals ts.PersonID
               join ar in db.AttendeeResponses on ts.TimeslotID equals ar.TimeslotID
               join pm in db.PersonMeetings on ar.PersonMeetingID equals pm.PersonMeetingID
               join m in db.Meetings on pm.MeetingID equals m.MeetingID
               select p;

// Joining all tables and filtering
var details3 = from p in db.Persons
               join ts in db.Timeslots on p.PersonID equals ts.PersonID
               join ar in db.AttendeeResponses on ts.TimeslotID equals ar.TimeslotID
               join pm in db.PersonMeetings on ar.PersonMeetingID equals pm.PersonMeetingID
               join m in db.Meetings on pm.MeetingID equals m.MeetingID
               where m.MeetingID == id
               && pm.MeetingRole == "Attendee"
               && ar.Response == "ConfirmedAtVenue"
               select p;

When do you start getting a new result collection?

edited Jul 27 '16 at 09:40

answered Jul 18 '16 at 21:34

Andrew

7,602
2
34
42

I wonder if you need the `Persons` table at all, it seems it can be removed. – Andrew Jul 19 '16 at 17:30
i tried, for both, not working as well. FYI, the code is ended with ...};).FirstOrDefault(); i dont know if this affect the result or not. – itdd Jul 20 '16 at 14:26
What does "not working well" mean? That's too little specific. `FirstOrDefault` does affect indeed, it returns the first item only, or null. You should try joining one table at a time to find at what point the problems being. Do you have a working equivalent T-SQL query? – Andrew Jul 20 '16 at 16:45
means not working. when the array tried to display in a view,it keep getting the null exception error. – itdd Jul 21 '16 at 02:20
I dont have it, cause i really dont have much knowledge regarding this. – itdd Jul 21 '16 at 02:21
I was referring to whether it returns null or incorrect data. Did you try removing the `where` conditions? Or removing the joins against both meetings tables? In this case, use `MeetingID = id` in the select, as you won't have the `Meetings` table. There must be a join or condition filtering out all your records. – Andrew Jul 21 '16 at 04:51
@itdd, still there? :) – Andrew Jul 26 '16 at 01:32
I updated my answer with different options so you can find what's going on. – Andrew Jul 27 '16 at 09:40
I am getting this error "System.NullReferenceException: Object reference not set to an instance of an object." please refer to my question again. i had send the full method code there. – itdd Jul 27 '16 at 10:35
In which line? All variables that come from a list with `DefaultIfEmpty` may be null, like `ar`, so if `ar` is null, you will get that error in `ar.AttendeeResponseID`. Can't you debug that error? Or at least find it by removing lines until the error is gone? – Andrew Jul 27 '16 at 17:14
Did you place a breakpoint and go step by step to see where it fails? – Andrew Jul 29 '16 at 06:15
@itdd, are you still there? Could you solve your problem? – Andrew Aug 16 '16 at 18:06

Allan F. Gagnon · Answer 2 · 2016-07-16T03:02:53.717

0

Update your where clause to the following.

where i.MeetingID == id && pm.MeetingRole.Equals("Attendee") && ar.Response == "ConfirmedAtVenue" && ar.Response != null

edited Jul 16 '16 at 03:02

answered Jul 16 '16 at 02:47

Allan F. Gagnon

320
2
7

I tried, not working. because not every person will have a response in attendeeResponse. – itdd Jul 16 '16 at 02:59
where i.MeetingID == id && pm.MeetingRole.Equals("Attendee") && ar.Response == "ConfirmedAtVenue" && ar.Response != null – Allan F. Gagnon Jul 16 '16 at 03:02
not working too. i get this error NullReferenceException – itdd Jul 16 '16 at 03:06
1

If `ar.Response == "ConfirmedAtVenue"`, then there's no sense in also adding the condition `!= null`; that last one will always be true if the previous one is true. You may want to do `ar != null && ar.Response == "ConfirmedAtVenue"`, because `ar` can be null as you are doing a left join (`DefaultIfEmpty`). It's important to first check for null and **then** for `Response`, because if the null check is false, it won't check the next condition, which would give a null reference exception. – Andrew Jul 16 '16 at 20:36
@Andrew had tried this, but still not working as well. will my joining table sequence a matter? – itdd Jul 17 '16 at 06:30
@andrew please refer to my question again. – itdd Jul 27 '16 at 10:37

join multiple table with where condition

2 Answers2