LINQ to SQL，选择具有最大日期的目标 [英] LINQ to SQL, select targets with max date

问题描述

我最终得到了这个可怕的代码，现在我得不到更好的结果。
这样做的更好方法是什么？

这是关于我的数据库的这部分内容：

编辑

$ b $ a 患者具有订阅多个 MonitoringObjects 。 目标记录涉及这些订阅。我想用给定的 Patient 和的每个订阅的最新日期检索目标记录> Category MonitoringObjects 。这些目标记录可能有不同的最大日期，因为目标可以被添加为订阅到监控对象独立。

  var subs = db.Subscriptions.Where（p => p.PatientID = = patID）.Where（p => p.MonitoringObject.Category.Name ==Medication）; 
 
 
 var targets1 =从db.Targets $ b中获取b $ b其中subs.Contains（t.Subscription）
 select t; 
 
 var maxTa =从db.Targets 
组t到t.SubscriptionID 
到g 
选择新的
 {
 Ky = g.Key，
 Date = g.Max（p => p.Date）
}; 
 var targets2 =来自目标1中的t 
其中maxTa.Select（p => p.Ky）.Contains（t.SubscriptionID）&& 
 maxTa.Select（p => p.Date）.Contains（t.Date）
 select t; 
  

解决方案

我不完全确定这是为了达到什么目的，或者你的数据模型看起来像什么，但是像这样？

  var subs = db.Subscriptions.Where（p => p.PatientID == patID）.Where（p => p.MonitoringObject.Category.Name ==Medication）; 
 
 var targets = subs 
 .SelectMany（s => s.Targets）
 .Where（t => t.Date == t.Subscription.Targets.Max （_t => _t.Date））
  

I ended up with this horrible code below, I can't get a better result now. What is a better way of doing that?

It's about this part of my database:

EDIT

A Patient has a Subscription to multiple MonitoringObjects. Target records refer to these Subscriptions. I want to retrieve the target records with the newest date per Subscription for a given Patient and a Category of MonitoringObjects. These target records may have different max dates, as Targets can be added for Subscriptions to MonitoringsObjects independently.

var subs = db.Subscriptions.Where(p => p.PatientID == patID).Where(p => p.MonitoringObject.Category.Name == "Medication");


var targets1 = from t in db.Targets
              where subs.Contains(t.Subscription)
              select t;

var maxTa = from t in db.Targets
            group t by t.SubscriptionID
                into g
                select new
                {
                    Ky = g.Key,
                    Date = g.Max(p => p.Date)
                };
var targets2 = from t in targets1
               where maxTa.Select(p => p.Ky).Contains( t.SubscriptionID ) && 
               maxTa.Select(p => p.Date).Contains( t.Date )
               select t;

解决方案

I am not exactly sure what this is trying to achieve, or what your datamodel looks like, but something like this?

var subs = db.Subscriptions.Where(p => p.PatientID == patID).Where(p => p.MonitoringObject.Category.Name == "Medication");

var targets = subs
    .SelectMany(s => s.Targets)
    .Where(t => t.Date == t.Subscription.Targets.Max(_t => _t.Date))

这篇关于LINQ to SQL，选择具有最大日期的目标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！