LINQ to Entities：转换 SQL 子选择

Question

我明白了这一点。 无需回答。 系统说我必须等待 8 小时才能回答我自己的问题。但现在答案如下：

答案如下：

var startDate = DateTime.Today.AddDays(-30);    
var results = (from h in Histories
                join q in Quotes
                    on h.QuoteID equals q.QuoteID
                join a in Agencies
                    on q.AgencyID equals a.AgencyID             
                        where q.Status == "Inforce" &&   
                                q.LOB == "Vacant" &&        
                                q.EffectiveDate > startDate &&
                                h.Deleted == null &&
                                h.DeprecatedBy == null &&                                    
                                h.TransactionStatus == "Committed" &&                                        
                                a.DC_PLT_Roles.Any(r => r.Name == "Wholesaler")
                        group new {h} by new {h.PolicyNumber} into g        
                        select new {                                
                            MaxHistoryID = g.Max (x => x.h.HistoryID),
                            comment = (from h2 in Histories
                                    where h2.HistoryID == g.Max (x => x.h.HistoryID)
                                    select h2.Comment).FirstOrDefault() 
                            }).ToList();

关键代码是：

comment = (from h2 in Histories
                                    where h2.HistoryID == g.Max (x => x.h.HistoryID)
                                    select h2.Comment).FirstOrDefault() 

我们正在将 SQL/存储过程转换为 LINQ to Entities 语句。我无法弄清楚子选择的正确语法。

目前我正在转换此 SQL：

declare @startDate DateTime
set @startDate = DATEADD(DD, -30, GETDATE())

select * from history where historyid in(     
select  MAX(h.historyid) as HistoryId 
    from  History h (nolock) 
    inner join Quote q (nolock) on h.QuoteID = q.QuoteID 
    inner join Agency (nolock) a on q.AgencyID = a.AgencyID
    inner join DC_PLT_EntityRoles er (nolock) on a.AgencyID = er.EntityID
    inner join DC_PLT_Roles (nolock) r on er.RoleID = r.RoleID
    where
          q.Status = 'Inforce' 
          and q.LOB = 'Vacant'  
          and q.EffectiveDate > @startDate 
          and h.Deleted is null --
          and h.DeprecatedBy is null --
          and h.TransactionStatus = 'Committed'
          and r.Name = 'Wholesaler'
    group by h.PolicyNumber)

如您所见，上面的代码由两个 select 语句组成。主要选择（从历史记录中选择*）..和过滤器选择（选择MAX（h.historyid）...）

我让过滤器选择工作（见下文）：

var startDate = DateTime.Today.AddDays(-30);    
var results = (from h in Histories
                join q in Quotes
                    on h.QuoteID equals q.QuoteID
                join a in Agencies
                    on q.AgencyID equals a.AgencyID             
                        where q.Status == "Inforce" &&   
                                q.LOB == "Vacant" &&        
                                q.EffectiveDate > startDate &&
                                h.Deleted == null &&
                                h.DeprecatedBy == null &&                                    
                                h.TransactionStatus == "Committed" &&                                        
                                a.DC_PLT_Roles.Any(r => r.Name == "Wholesaler")
                        group new {h} by new {h.PolicyNumber} into g        
                        select new {                                
                            MaxHistoryID = g.Max (x => x.h.HistoryID)                           
                            }).ToList();

但是我无法找出正确的语法来设置主要选择。 （基本上使用过滤器选择中的 HistoryID 从历史表中获取记录。）

任何帮助将不胜感激。

感谢您的帮助。

Answer 1

我想通了，这是代码：

var startDate = DateTime.Today.AddDays(-30);    
var results = (from h in Histories
                        .Include("Quote")
                        .Include("Quote.Agency")                                    
                        where h.Quote.Status == "Inforce" &&     
                                h.Quote.LOB == "Vacant" &&      
                                h.Quote.EffectiveDate > startDate &&
                                h.Deleted == null &&
                                h.DeprecatedBy == null &&                                    
                                h.TransactionStatus == "Committed" &&                                        
                                h.Quote.Agency.DC_PLT_Roles.Any(r => r.Name == "Wholesaler")                            
                        group new {h} by new {h.PolicyNumber} into g        
                        select new {
                            XMLData = (from h2 in Histories
                                        where h2.HistoryID == g.Max (x => x.h.HistoryID)
                                        select h2.XMLData).FirstOrDefault() 
                            }).ToList();

关键逻辑是：

select new {
                            XMLData = (from h2 in Histories
                                        where h2.HistoryID == g.Max (x => x.h.HistoryID)
                                        select h2.XMLData).FirstOrDefault() 
                            }).ToList();

必须喜欢嵌套查询