question

anilkumar-7193 avatar image
0 Votes"
anilkumar-7193 asked ZhiLv-MSFT edited

Linq query

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

dotnet-aspnet-core-mvc
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.

ZhiLv-MSFT avatar image
0 Votes"
ZhiLv-MSFT answered ZhiLv-MSFT edited

Hi @anilkumar-7193,

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:

125375-6.gif

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


6.gif (650.5 KiB)
· 9
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 a lot I have also apply same method.

time which are saved in table as time(7) format like 15:55:42
but when bind or retrieve it is not changing in 12 hours Am/Pm format to bind it mvc html display for

0 Votes 0 ·

Some issue with above thread
time which are saved in table as time(7) format like 15:55:42
but when bind or retrieve it is not changing in 12 hours Am/Pm format to bind it mvc html display for

0 Votes 0 ·

Hi @anilkumar-7193,

time which are saved in table as time(7) format like 15:55:42
but when bind or retrieve it is not changing in 12 hours Am/Pm format to bind it mvc html display for

You can use the DateTime.ToString method to format the result, for example:

Change the output value from TimeSpan type to string type:

 public class EmpAttendanceViewModel
 {
     public int EmpId { get; set; }
     public string Name { get; set; }
     public int offid { get; set; }
     public string Time { get; set; }
 }

Then, in the LINQ query statement, use the following code:

     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 = DateTime.Today.Add( c.att.TOut - c.att.TIn).ToString("hh:mm:ss tt"),
             }).ToList();

The result as below:

126747-25.gif


0 Votes 0 ·
25.gif (257.6 KiB)

Thanks a lot its work for me

0 Votes 0 ·

@Html.TextBoxFor(m => m.emp.name, new { @class = "form-control", @required = "required", @Value =
@HttpContextAccessor.HttpContext.Session.GetString("xyz")})
This is my code in cshtml page where I want to show session value to textbox, but syntax is not working .What will be the right syntax.
Thanks In Advance

0 Votes 0 ·

In above thread all query working perfectly but if my attendance table has no data than this query throw exception
nullable time not allow .How to fix plz.
Thanx in Advance

0 Votes 0 ·
Show more comments
AgaveJoe avatar image
0 Votes"
AgaveJoe answered AgaveJoe commented

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.



· 2
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 your quick response
here is my
problem
public class attendance

public int Empid{ get; set; }
public TimeSpan TIn { get; set; }
public TimeSpan TOut { get; set; }

public class emp

public int Empid{ get; set; }
public string name{ get; set; }
public int offid{get ;set;}


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

0 Votes 0 ·
AgaveJoe avatar image AgaveJoe anilkumar-7193 ·

I recommend fixing the general design to use DateTime rather than a TimeSpan. Using a TimeSpan will make filtering and calculating difficult when the records span a day.

Anyway, to answer your immediate question, use LINQ to get the results set. then simply subtract one TimeSpan from the other when rending the table. Why do you need a LINQ query to do this basic math?

 TimeSpan t1 = TimeSpan.FromHours(-2);
 TimeSpan t2 = TimeSpan.FromHours(-1);
 Console.WriteLine($"{t2 - t1}");
0 Votes 0 ·