Linq Query join with multiple table

Question

Hi
I want to get record form three table using linq with joining .
But if one of table has no record than it give null exception so, how to handle this
in which I have two field one is date and another is Timespan
can any one help for this problem.Below is my linq query it is works fine if both table has data but if in attendance has no data than it give error with date and time field

                              var empRecord=   from e in employees  
                                 join d in empAttendances on e.AId equals d.EmpAId into table1  
                                from d in table1.ToList().DefaultIfEmpty()  
                                 join i in offMas on e.OffMasId equals i.Id into table2  
                                 from i in table2.ToList()  
                                    
                                 where e.AId == 1  
                                 select new AttendanceViewModel  
                                 {  
                                     aid = e.AId,  
                                     stay = d.TOut - d.TIn,  
                                    tin = DateTime.Today.Add(d.TIn).ToString("hh:mm:ss tt"),  
                                      
                                    tout = DateTime.Today.Add(d.TOut).ToString("hh:mm:ss tt"),  
                                     emp = e,  
                                      empatnd = d,  
                                     empoff = i  

                                 };

Thanx & Regards.

Answer

Hi @anil kumar ,

If one of the tables has no record, the joined table is empty, then when you access the joined table property in the where clause or the select clause, it will show the NullReferenceException.

To solve this exception, you could check whether the joined table exist or not in the where clause and select clause, please refer the following sample code:

        var employees = _repository.GetEmps();//get all emps  
        var empAttendances  = _repository.GetAttendances();//get all attendances  
        var offMas = _repository.GetOffMas();  

        //define a variable to store the return data. It can prevent the NullReferenceException when returns null to the view page.  
        var empRecord = new List();  

        var filterresult = (from e in employees  
                            join d in empAttendances on e.AId equals d.EmpAId into table1  
                            from t1 in table1.ToList().DefaultIfEmpty()  
                            join i in offMas on e.OffMasId equals i.Id into table2  
                            from t2 in table2.ToList().DefaultIfEmpty()  
                            where t2?.AId == 1           //if t2 contains value, apply the filter.  
                            select new EmpAttendanceViewModel  
                            {  
                                AId = e.AId,  
                                EmpId = e.Empid,  
                                Name = e.name,  
                                offid = t1?.EmpAId ?? 0,   // use '?' to check if the table is empty or not, if the table is empty set the default value.  
                                Time = ((t1?.TOut ?? new TimeSpan(0, 0, 0)) - (t1?.TIn ?? new TimeSpan(0, 0, 0))).ToString(),//(t1.TOut - t1.TIn).ToString() //  
                            }).ToList();  

        //add the filter result to the return list.  
        empRecord.AddRange(filterresult);

The result as below:

The empAttendances and offMas has no data:

The empAttendances has no data:

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion

Linq Query join with multiple table

1 answer