question

anilkumar-7193 avatar image
0 Votes"
anilkumar-7193 asked anilkumar-7193 commented

Linq Query join with multiple table

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

                                  };



132288-error.png






Thanx & Regards.


dotnet-aspnet-core-mvc
error.png (87.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ZhiLv-MSFT avatar image
0 Votes"
ZhiLv-MSFT answered anilkumar-7193 commented

Hi @anilkumar-7193,

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<EmpAttendanceViewModel>();

         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:

132643-5.gif

The empAttendances has no data:

132598-6.gif


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


5.gif (529.3 KiB)
6.gif (497.2 KiB)
· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for such quick and perfect solution

0 Votes 0 ·

Dear
One doubt in above solution is it possible if I have data in employee table and don't have data in attendance table
and both filter where t2?.AId == 1 and t1.date==datetime.today I want to display at least employee data
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 and t1.date==datetime.today //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);

0 Votes 0 ·

Hi @anilkumar-7193,

One doubt in above solution is it possible if I have data in employee table and don't have data in attendance table
and both filter where t2?.AId == 1 and t1.date==datetime.today

Please check my reply, it contains the scenario which the employee has data, and the attendance table don't have data. If you set a break point to the query statement, you can see that in this scenario, the t1 is empty, so the result is empty.

I want to display at least employee data

Do you mean you want to keep display the employee data, no matter the attendance table and OffMas table has data or not? If that is the case, you can use the following query statement:

         var filterresult = (from e in employees
                             select new EmpAttendanceViewModel
                             {
                                 AId = e.AId,
                                 EmpId = e.Empid,
                                 Name = e.name,
                                 offid = (from att in empAttendances where att.EmpAId == e.AId select e.OffMasId).FirstOrDefault(),
                                 Time = (from att in empAttendances where att.EmpAId == e.AId select (att.TOut - att.TIn).ToString()).FirstOrDefault(),//(t1.TOut - t1.TIn).ToString() //
                             }).ToList();

The result as below:

133700-1.gif


0 Votes 0 ·
1.gif (353.9 KiB)

Thanks it's work for me
iIn this where should be orderby clouse with offname then attdate

0 Votes 0 ·

In this where should be orderby clouse with offname then attdate

0 Votes 0 ·
Show more comments