c# - LINQ 查询连接两个表并从表 B 中选择表 A 对应的最新记录

Question

我有两张 table 。表一包含一个区域列表，表二包含一个样本列表，每个样本行包含 Area_ID 作为外键。

我需要检索 Area 表中的所有记录，其中只有最新的对应样本状态。我有这个查询，但它只返回一个 Area，其中包含 Sample 表中的最新样本:

var result = (
    from a in db.area
    join c in db.sample
    on a.location_id equals c.location_id

    select new 
    {
        name = a.location_name,
        status = c.sample_status,
        date = c.sample_date
    }).OrderByDescending(c => c.date).FirstOrDefault();

Answer 1

一个解决方案可能是过滤您的第二个 DbSet:

 var result =  from a in db.area
               join c in db.sample.Where(s=>s.location_id==a.location_id).OrderByDescending(c => c.sample_date).Take(1)
               on a.location_id equals c.location_id 
               select new 
                    {
                         name = a.location_name,
                         status = c.sample_status,
                         date = c.sample_date
                    };

另一种解决方案可能是应用 group join :

 var result =  from a in db.area
               join c in db.sample
               on a.location_id equals c.location_id into samples
               let sample=samples.OrderByDescending(c => c.sample_date).FirstOrDefault()
               select new 
                    {
                          name = a.location_name,
                          status = sample.sample_status,
                          date = sample.sample_date
                    };

如果你使用导航属性会更容易。假设您在 Area 和 Sample 之间存在一对多关系:

var result =from a in db.area
            let sample= a.Samples.OrderByDescending(c => c.sample_date).FirstOrDefault()
            select new 
                    {
                          name = a.location_name,
                          status = sample.sample_status,
                          date = sample.sample_date
                    };