Linq query

Question

How to calculate differance of intime and outtime in hours min sec in time format using linq
I have three table employess,off and empattendance

Answer 1

Hi @anil kumar ,

now I want linq query to show difference of in and out time(which are saved in table as time format like 15:55:42) in hours ,min and second with join of both table
and bind it to view

Based on your model, I created a sample with the following test data:

public interface IDataRepository  
{   
    List<attendance> GetAttendances();  
    List<emp> GetEmps();  
}  

public class DataRepository : IDataRepository  
{   
    public List<attendance> GetAttendances()  
    {  
        return new List<attendance>()  
        {  
            new attendance(){ Empid=1001, TIn = new TimeSpan(1,2,22), TOut=new TimeSpan(5,32,33)},  
            new attendance(){ Empid=1002, TIn = new TimeSpan(8,52,30), TOut=new TimeSpan(18,5,33)},  
            new attendance(){ Empid=1003, TIn = new TimeSpan(9,2,12), TOut=new TimeSpan(18,10,21)},   
        };  
    }  

    public List<emp> GetEmps()  
    {  
        return new List<emp>()  
        {  
            new emp(){ Empid=1001, name="Tom", offid=101},  
            new emp(){ Empid=1002, name="John", offid=102},  
            new emp(){ Empid=1003, name="Vivian", offid=103},  
        };  
    }  
}  

Then, we could use the following LINQ statement to join table and calculate the time span:

public class TestController : Controller  
{  
    private readonly ApplicationDbContext _context;  
    private readonly IDataRepository _repository;   
    public TestController(ApplicationDbContext context, IDataRepository repository)  
    {  
        _context = context;  
        _repository = repository;   
    }  

    public IActionResult EmpIndex()  
    {  

       // var attendances = _repository.GetAttendances(); //get all attendances  
       // var emps = _repository.GetEmps(); //get all emps  

        var result = (from att in _repository.GetAttendances()  
                      join emp in _repository.GetEmps() on att.Empid equals emp.Empid  
                      select new EmpAttendanceViewModel()  
                      {  
                          EmpId = emp.Empid,  
                          Name = emp.name,  
                          offid = emp.offid,  
                          Time = att.TOut - att.TIn,  
                      }).ToList();  
        //using linq lambda expressions  
        var lamdaresult = _repository.GetAttendances()  
            .Join(_repository.GetEmps(), att => att.Empid, emp => emp.Empid, (att, emp) => new { att, emp })  
            .Select(c =>  
                new EmpAttendanceViewModel()  
                {  
                    EmpId = c.emp.Empid,  
                    Name = c.emp.name,  
                    offid = c.emp.offid,  
                    Time = c.att.TOut - c.att.TIn,  
                }).ToList();  
        return View(result);  
    }  

The result as below:

You can also change the TimeSpan to the DateTime format. Refer the TimeSpan Struct.

---

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

Answer 2

You did not share the models so we have no idea how your models are designed. If the in and out times are DateTime types then simply subtract the properties which will result in a TimeSpan. The TimeSpan has hours, minutes, seconds, and can be formatted however you like.

I usually do the math in the model class or an extension method rather than LINQ.

    public class InOutModel  
    {  
        public DateTime InDate { get; set; }  
        public DateTime OutDate { get; set; }  
        public TimeSpan WorkTime   
        {   
            get   
            {  
                return OutDate - InDate;  
            }   
        }  
    }  
    class Program  
    {  
          
        static void Main(string[] args)  
        {  
            List<InOutModel> results = PopulateModel();  
            foreach(InOutModel item in results)  
            {  
                Console.WriteLine($"{item.InDate}\t{item.OutDate}\t{item.WorkTime}");  
            }  
        }  
  
        static List<InOutModel> PopulateModel()  
        {  
            return new List<InOutModel>()  
            {  
                new InOutModel() {  
                    InDate = DateTime.Now.AddDays(-1).AddHours(-3),  
                    OutDate = DateTime.Now.AddDays(-1)  
                },  
                new InOutModel() {  
                    InDate = DateTime.Now.AddHours(-8),  
                    OutDate = DateTime.Now  
                }  
            };  
        }  
    }  

Results

8/20/2021 7:12:01 AM    8/20/2021 10:12:01 AM   03:00:00.0066075  
8/21/2021 2:12:01 AM    8/21/2021 10:12:01 AM   08:00:00.0000017  

You can write a projection query to create a new complex type that has the calculations.