代码之家 › 专栏 › 技术社区 › SamuraiJack

如何获取LINQ中每个日期的最大和最小进出时间?

linq postgresql .net sql c#

SamuraiJack · 技术社区 · 6 年前

我是LINQ新手,一直在尝试编写一个LINQ查询,该查询相当于以下SQL语句:

SELECT linking."RfidNumber",
    linking."VehicleID",
    acs."Date",
    Min(acs."acs_datetime" ) as INTIME,
    Max(acsout."acs_datetime" )  
FROM dbo."acs_transaction_new" acs
INNER JOIN dbo."acs_transaction_new" acsout 
    ON acs."Date" = acsout."Date"  AND acs."acs_operations" = 'in' AND
        acsout."acs_operations" = 'out'
INNER JOIN dbo."PassLinkings" linking ON acs."acs_rfid_no" = linking."RfidNumber" AND
    acs."acs_vehicle_id" = linking."VehicleID"   
WHERE acsout."acs_operations" = 'out' AND acs."acs_operations" = 'in'
GROUP BY linking."RfidNumber", linking."VehicleID",  acs."Date";

基本上我想要敏 acs."acs_datetime" 哪里 acs."acs_operations"='in' 同样地,麦克斯 acs.“acs_datetime” 哪里 acs."acs_operations"='out' 表中每个日期的唯一组合 RfidNumber 和 VehicleID

我在林肯迷路了:

    var query1 = (from acs in db.acs_transaction
                             join acsout in db.acs_transaction on acs.acs_datetime equals acsout.acs_datetime     
                              join linking in db.PassLinkings on new { rfid = acs.acs_rfid_no, vehicleid = acs.acs_vehicle_id } equals new { rfid = linking.RfidNumber, vehicleid =  (linking.VehicleID == null ? -1 : (int)linking.VehicleID) }
                              where acs.acs_operations == "in" && acsout.acs_operations == "out"
                              select new { acs.acs_rfid_no,
 acs.VehicleID ,
acs."Date",
Min(acs."acs_datetime" ),// something like that
Max(acs."acs_datetime" )
}).ToList(); ;

2 回复 | 直到 6 年前

Mrinal Kamboj 6 年前

根据您发布的查询,我了解您的模型:

public class Acs_Transaction_New_class
{
    public DateTime Date {get; set;}
    public string acs_operations {get; set;}
    public int acs_rfid_no {get; set;}
    public int acs_vehicle_id {get; set;}
    public DateTime acs_datetime {get; set;}
}

public class PassLinking_Class
{
    public int RfidNumber { get; set; }
    public int VehicleID { get; set; }
}

流利的语法:

// Data Placeholders to compile linq query

   var result = Acs_Transaction_New
                   .Join(Acs_Transaction_New, acs => acs.Date, acsout => acsout.Date, (acs, acsout) => new { acs, acsout })
                   .Where(x => x.acs.acs_operations == "in" && x.acsout.acs_operations == "out")
                   .Join(PassLinkings, x => new { rfid = x.acs.acs_rfid_no, vehicleid = x.acs.acs_vehicle_id }, linking => new { rfid = linking.RfidNumber, vehicleid = linking.VehicleID}, (x, linking) => new { x.acs, x.acsout,linking })
                   .GroupBy(x => new { x.linking.RfidNumber, x.linking.VehicleID, x.acs.Date })
                   .Select(x => new
                   {
                       x.Key.RfidNumber,
                       x.Key.VehicleID,
                       x.Key.Date,
                       acs_min_datetime = x.Min(y => y.acs.acs_datetime),
                       acs_max_datetime =  x.Max(y => y.acsout.acs_datetime)
                   });

要点:

流利的语法比查询语法要详细得多,但是提供了清晰的数据级联,因此更容易理解
从Sql查询,我已经翻译了, acs_operations == "in" / "out" 作为Where条件,不需要连接,理想情况下我们不需要与“in”和“out”进行两次比较

查询语法:

var result = from acs in Acs_Transaction_New
             join acsout in Acs_Transaction_New on acs.Date equals acsout.Date
             where acs.acs_operations == "in" && acsout.acs_operations == "out"
             join link in PassLinkings on new { rfid = acs.acs_rfid_no, vehicleid = acs.acs_vehicle_id } equals new { rfid = link.RfidNumber, vehicleid = link.VehicleID}
             group new {acs,acsout } by new { link.RfidNumber, link.VehicleID, acs.Date } into group1
             select new
             {
                 group1.Key.RfidNumber,
                 group1.Key.VehicleID,
                 group1.Key.Date,
                 acs_min_datetime = group1.Min(y => y.acs.acs_datetime),
                 acs_max_datetime = group1.Max(y => y.acsout.acs_datetime)
             };

两个版本的查询都在编译中,因此语法上是正确的,您只需要进行修改以适合您的用例

NetMage 6 年前

另一种方法(在SQL和LINQ中)是将行分组,而不考虑输入/输出操作,然后在每个组中筛选和最小/最大值:

var ans = from acs in dbo.acs_transaction
          join linking in dbo.PassLinkings on new { RfidNumber = acs.acs_rfid_no, VehicleID = acs.acs_vehicle_id } equals new { linking.RfidNumber, linking.VehicleID }
          group acs by new { linking.RfidNumber, linking.VehicleID, acs.Date } into acsg
          select new {
              acsg.Key.RfidNumber,
              acsg.Key.VehicleID,
              acsg.Key.Date,
              InTime = acsg.Where(acs => acs.acs_operations == "in").Min(acs => acs.acs_datetime),
              OutTime = acsg.Where(acs => acs.acs_operations == "out").Max(acs => acs.acs_datetime)
          };

在我的LINQ to SQL测试中,使用了类似的数据库查询,生成了一个SQL查询。