How and where to use AddRange() method

entity-framework-core linq

Question

I want to display related data from second table with each value in first table

i have tried this query

public ActionResult Index()
    {            
        List<EmployeeAtt> empWithDate = new List<EmployeeAtt>();

        var employeelist = _context.TblEmployee.ToList();

        foreach (var employee in employeelist)
        {               
            var employeeAtt = _context.AttendanceTable
                .GroupBy(a => a.DateAndTime.Date)
                .Select(g => new EmployeeAtt
                   {
                     Date = g.Key,
                     Emp_name = employee.EmployeeName,
                     InTime = g.Any(e => e.ScanType == "I") ? g.Where(e => 
                     e.ScanType == "I").Min(e => 
                     e.DateAndTime.ToShortTimeString())
                     .ToString() : "Absent",
                     OutTime = g.Any(e => e.ScanType == "O") ? g.Where(e => 
                     e.ScanType == "O").Max(e =>
                     e.DateAndTime.ToShortTimeString())
                     .ToString() : "Absent"                                                         
                    });

            empWithDate.AddRange(employeeAtt);
        }
        return View(empWithDate);
    }  

Here is my attendance Table

AttendanceTable

Results

I want to display the shortest time with "I" Column value against each employee and last time with "O" Column value as out time. I think i am not using AddRange() at proper place. Where it should go then?

public partial class TblEmployee
{
    public TblEmployee()
    {
        AttendanceTable = new HashSet<AttendanceTable>();
    }

    public int EmpId { get; set; }
    public string EmployeeName { get; set; }

    public virtual ICollection<AttendanceTable> AttendanceTable { get; set; }
}

 public partial class AttendanceTable
{
    public int Id { get; set; }
    public int AttendanceId { get; set; }
    public int EmployeeId { get; set; }
    public string ScanType { get; set; }
    public DateTime DateAndTime { get; set; }

    public virtual TblEmployee Employee { get; set; }
}
1
0
1/27/2019 12:03:22 PM

Accepted Answer

The actual problem is not related to AddRange(), you need a where clause before GroupBy() to limit attendances (before grouping) to only records related to that specific employee, e.g.

_context.AttendanceTable
        .Where(a => a.Employee == employee.EmployeeName)
        .GroupBy(a => a.DateAndTime.Date)
        ...

Depended on your model, it is better to use some kind of ID instead of EmployeeName for comparison if possible.

Also you can use SelectMany() instead of for loop and AddRange() to combine the results into a single list. like this:

List<EmployeeAtt> empWithDate = _context.TblEmployee.ToList()
            .SelectMany(employee => 
                            _context.AttendanceTable
                                    .Where(a => a.Employee == employee.EmployeeName)
                                    .GroupBy(a => a.DateAndTime.Date)
                                    .Select(g => new EmployeeAtt
                                                 {
                                                     ...        
                                                 })
                       );
...
0
1/27/2019 11:52:01 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow