need to convert sql query to linq and return list of records-CodePudding

Hai using sql query its working database side, directly did not database because it code first approach. so i need following sql query convert to linq query. please any one suggest me .

SELECT  Sy.SystemUserName,
    MIN(Sc.CreatedOn) as StartedTime,   
    MAX(Sc.CreatedOn) as ExitTime,
    datediff(MINUTE, 
    MIN(Sc.CreatedOn) , 
    MAX(Sc.CreatedOn)) as WorkingHours  
 from SystemDetails Sy   
 LEFT JOIN Screenshots Sc on Sy.id = Sc.SystemId   
 where Sy.CompanyGUID =  '25' 
 AND  Sy.IsDeleted = 0
 and (datediff(dd,Sc.CreatedOn,getdate()) = 0
 Or SC.CreatedOn IS NULL) 
 GROUP By Sy.SystemUserName

SystemDetails Model :

 public class SystemDetails 
  {
    public int Id { get; set; }

    [Required]
    public string GuidID { get; set; }

   
    [Required]
    public string SystemUserName { get; set; }

  
    [Required]
    public string CompanyGUID { get; set; }

    [Required]
    public int TeamId { get; set; }

    public User User { get; set; }

    public ICollection<Screenshot> Screenshot { get; set; }
 

   }

Screenshot Model :

  public class Screenshot 
    {
    public int Id { get; set; }

    [Required]
    public string GuidId { get; set; }

    [Required]
    public int SystemId { get; set; }

    [Required]
    public string Screenshotname { get; set; }

    public DateTime CreatedOn { get; set; }

    public string CreatedBy { get; set; }

    public SystemDetails System { get; set; }

}

Return collection Class :

   public class UserAttendance
     {
    public string StaffName { get; set; }

    public DateTime StartTime { get; set; }

    public DateTime EndTime { get; set; }

    public string WorkingHours { get; set; }

     }

Here SystemDetails table Systemusername and Screenshot table CreatedOn fields we needed, SystemDeatils Id field and Screenshot SystemId field are key constraint. Need to get SystemUserName and Minimum CreatedOn date as StartTime, Maximum CreatedOn Date as EndTime. we get both datetime duration as WorkingHours. we have get all values using ef core. Incase CreatedOn date null or no date avilable then return '1900-01-01' default date. Please suggest me

CodePudding user response：

Try this query:

var query = 
    from sy in dbContext.SystemDetails
    from sc in sy.Screenshot
    where sy.CompanyGUID == "25" && !sy.IsDeleted
       && (sc.CreatedOn == null || EF.Functions.DateDiffDay(sc.CreatedOn.Value, DateTime.Now) == 0)
    group sc by new { sc.SystemUserName } into g 
    select new UserAttendance
    {
        StaffName = g.Key.SystemUserName,
        StartTime = g.Min(x => x.CreatedOn.Value),
        EndTime = g.Max(x => x.CreatedOn.Value),
        WorkingHours = EF.Functions.DateDiffMinute(g.Min(x => x.CreatedOn.Value), g.Max(x => x.CreatedOn.Value)).ToString()
    };