Hai使用sql查詢其作業資料庫端，直接沒有資料庫，因為它是代碼優先的方法。所以我需要將以下 sql 查詢轉換為 linq 查詢。請任何人建議我。
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
系統詳情型號：
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; }
}
截圖型號：
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; }
}
退貨收集類：
public class UserAttendance
{
public string StaffName { get; set; }
public DateTime StartTime { get; set; }
public DateTime EndTime { get; set; }
public string WorkingHours { get; set; }
}
這里我們需要的 SystemDetails 表 Systemusername 和 Screenshot 表 CreatedOn 欄位，SystemDeatils Id 欄位和 Screenshot SystemId 欄位是關鍵約束。需要獲取 SystemUserName 和最小 CreatedOn 日期作為 StartTime，Maximum CreatedOn Date 作為 EndTime。我們將兩個日期時間都作為 WorkingHours。我們已經使用 ef core 獲得了所有值。Incase CreatedOn 日期 null 或沒有可用日期，則回傳“1900-01-01”默認日期。請建議我
uj5u.com熱心網友回復：
試試這個查詢：
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()
};
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/454036.html