MVC LINQ WITH MULTIPLE TABLE

Question

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() //
hfdate = (from holiday in holidayMasl where holiday.Hfromdate.Month.ToString() == DateTime.Now.Month.ToString() && holiday.Hfromdate.Year.ToString() == Currentyear select (holiday.Hfromdate)).FirstOrDefault(),
htodate = (from holiday in holidayMasl where holiday.Htodate.Month.ToString() == DateTime.Now.Month.ToString() && holiday.Htodate.Year.ToString() == Currentyear select (holiday.Htodate)).FirstOrDefault(),

                         }).ToList();  

In above query I have employee table ,offmas,leavetable,holidaymas by using these table I want to retrieve records and make attendance register for whole month as per attached snapshot .In this it works fine with one record but not working when holiday record more than ones and same for leave because we are using firstordefault(). I want each employee record should display as per condition

Thanks & Regard IN advance

Answer 1

Hi @anil kumar ,

It seems that you have post a duplicate thread: MVC Core with Linq, right?

I assume you have already got the attendance list with status, and now you want to convert the date list (or number list) from row to columns, like this:

convert

Name Number/Date Status
new 1 A
new 2 A
new 3 A
new 4 A
new 5 H
...

to

Name 1 2 3 4 5 ...
new A A A A H ...

If that is the case, you can refer to the following code, use group method to group the result by Name, then use a Dictionary to store the date/number and status.

public class HomeController : Controller  
{  
    private readonly ILogger<HomeController> _logger;   
    private readonly ApplicationDbContext _dbcontext;  
    public HomeController(ILogger<HomeController> logger,  ApplicationDbContext context)  
    {  
        _logger = logger;   
        _dbcontext = context;  
    }  

    public IActionResult Index()  
    {   
        //first, we join the Employees and EmployeeAttendances table to get the employee name and the daily_date,  
        // then, in the select statement, we could query the employeeleave table and holiday table to set the status.  
        var queryresult = (from empatt in _dbcontext.EmployeeAttendances  
                      join emp in _dbcontext.Employees  
                      on empatt.Empid equals emp.Empid  
                      select new EmployeeTemp()  
                      {  
                          Empid = emp.Empid,  
                          Name = emp.Name,  
                          Date = empatt.Daily_Date,  
                          Status = (  
                          empatt.Day == 1 ? "P" :  
                          _dbcontext.EmployeeLeaves.Any(c => c.Empid == emp.Empid && empatt.Daily_Date >= c.StartDate && empatt.Daily_Date <= c.EndDate) ? "CL" :  
                          _dbcontext.Holidays.Any(h => h.HolidayDate == empatt.Daily_Date) ? "H" :  
                          empatt.Day == 0 ? "A" : "Unknown"  
                          )  
                      }).OrderBy(e=>e.Name).ThenBy(c=>c.Date).ToList();  
        //the queryresult as below:  
        // Emp1 2019/1/1 p  
        // Emp1 2019/1/2 P  
        // Emp1 2019/1/3 CL  
        //...  

        //Then, we could use the following code convert result to your required  
        var result = queryresult.GroupBy(c => c.Name).Select(g =>  
        {  
            var empvm = new EmployeeViewModel();  
            empvm.Name = g.Key;  
            empvm.Attendance = new Dictionary<DateTime, string>();  
            foreach (var i in g)  
                empvm.Attendance.Add(i.Date, i.Status);  
            return empvm;  
        }).ToList();  

        return View(result);  
    }  

The EmployeeViewModel:

    public class EmployeeViewModel  
    {  
        public string Name { get; set; }  
        public Dictionary<DateTime,string> Attendance { get; set; }  
    }  

Finally, in the view page, we could use foreach statement to loop through the dictionary and display the date and status.

@model IEnumerable<CoreMVC5.Models.EmployeeViewModel>  
  
@{  
    ViewData["Title"] = "Index";  
}  
   
<table class="table table-bordered">  
    <thead>  
        <tr>  
            <th>   
                <label asp-for="@Model.FirstOrDefault().Name"></label>   
            </th>  
            @{  
                var firstdate = Convert.ToDateTime(Model.FirstOrDefault().Attendance.Keys.FirstOrDefault());  
                //base on the date to get the current month's days.  
                var days = DateTime.DaysInMonth(firstdate.Year, firstdate.Month);  
            }  
            @for (var numb = 1; numb <= days; numb++)  
            {  
                <th>@numb</th>  
            }  
        </tr>  
    </thead>  
    <tbody>  
@foreach (var item in Model) {  
        <tr>  
            <td>  
                @Html.DisplayFor(modelItem => item.Name)  
            </td>   
                @for (var numb = 1; numb <= days; numb++)  
                {   
                    //check whether the dictionary contains current day's data.  
                    var selectdate = item.Attendance.AsEnumerable().Where(c => c.Key.Day == numb).FirstOrDefault();  
                    if (selectdate.Key != DateTime.MinValue)  
                    {  

                        <td>@selectdate.Value</td>  
                    }  
                    else  
                    {  
                            <td></td>   
                    }  
                }  
        </tr>  
}  
    </tbody>  
</table>  

The result is below:

More detailed sample code, please refer MVC Core with Linq.

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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

Answer 2

Hi @anil kumar ,

for first employee from 1 to 30 than so on..............
But in your code you are printing from empattendance table but if some employee did not mark attendance than empattendance did not contain any data related to that employee

Based on your SQL table structure and the above description, if the EmpAttendance did not contain the special employee, I have modify the code as below:

Controller:

    public async Task<IActionResult> IndexAsync()  
    {   
        var firstDayOfMonth = new DateTime(2021, 11, 1);  

        var year = firstDayOfMonth.Year;  
        var month = firstDayOfMonth.Month;  
        List<DateTime> daysOfMonth = Enumerable.Range(1, DateTime.DaysInMonth(year, month)).Select(day => new DateTime(year, month, day)).ToList();  
   
        var queryresult = (from emp in _dbcontext.Employees.ToList()   
                           from t in daysOfMonth  
                           select new EmployeeTemp()  
                           {  
                               Empid = emp.aid,  
                               Name = emp.empname,  
                               Date = t.Date,  
                               Status = (  
                                 _dbcontext.Holidays.Any(h => h.Holidays == t.Date) ? "H" : // holiday  
                                 _dbcontext.EmployeeLeaves.Any(el => el.Empid == emp.aid && t.Date >= el.StartDate && t.Date <= el.EndDate) ? "L" : //leave  
                                 _dbcontext.EmployeeAttendances.Any(ea=> ea.EmpAid == emp.aid && ea.ADate == t.Date) ? "P" : // persent  
                                 "A" //absent  
                               )  
                           }).ToList();  

        //Then, we could use the following code convert result to your required  
        var result = queryresult.GroupBy(c => c.Name).Select(g =>  
        {  
            var empvm = new EmployeeViewModel();  
            empvm.Name = g.Key;  
            empvm.Attendance = new Dictionary<DateTime, string>();  
            foreach (var i in g)  
                empvm.Attendance.Add(i.Date, i.Status);  
            return empvm;  
        }).ToList();  

        return View(result);   
    }  

Index.cshtml

@model IEnumerable<CoreMVC5.Models.EmployeeViewModel>  
<table class="table table-bordered">  
    <thead>  
        <tr>  
            <th>   
                <label asp-for="@Model.FirstOrDefault().Name"></label>   
            </th>  
            @{  
                var firstdate = Convert.ToDateTime(Model.FirstOrDefault().Attendance.Keys.FirstOrDefault());  
                //base on the date to get the current month's days.  
                var days = DateTime.DaysInMonth(firstdate.Year, firstdate.Month);  
            }  
            @for (var numb = 1; numb <= days; numb++)  
            {  
                <th>@numb</th>  
            }  
        </tr>  
    </thead>  
    <tbody>  
@foreach (var item in Model) {  
        <tr>  
            <td>  
                @Html.DisplayFor(modelItem => item.Name)  
            </td>   
                @for (var numb = 1; numb <= days; numb++)  
                {   
                    //check whether the dictionary contains current day's data.  
                    var selectdate = item.Attendance.AsEnumerable().Where(c => c.Key.Day == numb).FirstOrDefault();  
                    if (selectdate.Key != DateTime.MinValue)  
                    {  

                        <td>@selectdate.Value</td>  
                    }  
                    else  
                    {  
                            <td></td>   
                    }  
                }  
        </tr>  
}  
    </tbody>  
</table>  

The result as below:

In the Holidays table, 5,6,7 is the holiday, so it will show H. [Note] This is the first priority in the linq select statement, so even if Diavid is present on the 6th, the status is H. If you want to change the priority of the status filter, you can change the order of the filters in the select statement .
In the EmployeeLeaves table, Diavid ask for leave at 2021-11-1 and 2021-11-20~2021-11-30, it will show L status;

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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