GROUP BY&前1个问题 [英] GROUP BY & Top 1 Question

问题描述

我正在尝试重写一个已经存在的查询，但是我无法将Linq与SQL的不同功能组合在一起

I'm trying to rewrite a query that already exists but I'm having trouble combining different functionality for Linq to SQL

var query = from dataContext.CustomerDbEntities中的c

                           在c.CustomerId上的dataContext.MessageDbEntities中加入消息等于messages.CustomerId

                           在messages上的dataContext.TransactionTypeDbEntities中加入transactionTypes .TransactionTypeId等于transactionTypes.TranactionTypeId

                           在c.SalesmenId上的dataContext.SalesmenDbEntities中加入salesmen。等于salesmen.SalesmenId

                           其中SalesRepIds.Contains（salesmen.SalesmenId）

                           在c.CustomerId上的dataContext.CustomerContractDbEntities中加入合约等于contract.CustomerId

                            contract.ContractRateTypeId等于contracttype.ContractRateTypeId的$ data $。                          其中transactionTypes.Code ==" TOFF"

                              &安培;&安培; messages.CreatedOn> = fromDate.Date

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &安培;&安培; messages.CreatedOn< = toDate.Date

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &安培;&安培; c.ServiceTypeId ==（ServiceType ==" Residential"？0：1）

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP;选择新的TOFFReportDetails

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; {

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; CustomerName = c.InvoiceBillingName，

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; AGLCAccountNumber = c.AGLCAccountNumber，

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; MarketerCustomerNumber = c.MarketerCustomerNumber，

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; IsSeasonal = c.IsSeasonal.HasValue？ c.IsSeasonal.Value：false，

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; DDDC = c.AGLCDDDC，

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; DateTOFFReceived = messages.Date，

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; SalesRep = salesmen.FullName，

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; ContractType = contracttype.Description

  &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; &NBSP; };

var query = from c in dataContext.CustomerDbEntities
                            join messages in dataContext.MessageDbEntities on c.CustomerId equals messages.CustomerId
                            join transactionTypes in dataContext.TransactionTypeDbEntities on messages.TransactionTypeId equals transactionTypes.TranactionTypeId
                            join salesmen in dataContext.SalesmenDbEntities on c.SalesmenId equals salesmen.SalesmenId
                            where SalesRepIds.Contains(salesmen.SalesmenId)
                            join contract in dataContext.CustomerContractDbEntities on c.CustomerId equals contract.CustomerId
                            join contracttype in dataContext.ContractRateTypeDbEntities on contract.ContractRateTypeId equals contracttype.ContractRateTypeId
                            where transactionTypes.Code == "TOFF"
                              && messages.CreatedOn >= fromDate.Date
                              && messages.CreatedOn <= toDate.Date
                              && c.ServiceTypeId == (ServiceType == "Residential" ? 0 : 1)
                            select new TOFFReportDetails
                            {
                                CustomerName = c.InvoiceBillingName,
                                AGLCAccountNumber = c.AGLCAccountNumber,
                                MarketerCustomerNumber = c.MarketerCustomerNumber,
                                IsSeasonal = c.IsSeasonal.HasValue ? c.IsSeasonal.Value : false,
                                DDDC = c.AGLCDDDC,
                                DateTOFFReceived = messages.Date,
                                SalesRep = salesmen.FullName,
                                ContractType = contracttype.Description
                            };

上述问题是客户可能有多个与其帐户相关的合同，所以我只想要最近的合同每个客户。

The problem with the above is that a customer could have multiple contracts associated to their account so I only want the most recent contract for each customer.

推荐答案

也许可以尝试不用 GroupBy ：删除'加入联系人'和
'加入联系人类型行，然后使用其他查询调整 ContactType 的计算：

Maybe try without GroupBy too: remove the ‘join contact’ and ‘join contacttypes’ lines, and then adjust the calculation of ContactType using an additional query:

   。 。 。

   ContactType =（从合同在dataContext.CustomerContractDbEntities


<跨度>&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
加入在dataContext.ContractRateTypeDbEntities合约类型上contract.ContractRateTypeId等于contracttype.ContractRateTypeId

<跨度>&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;             
where contract.CustomerId == c.CustomerId

<跨度>&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
的OrderBy contact.SomeDateField降序

<跨度>&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBS磷;&NBSP;&NBSP;
<跨度>&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; 选择contacttype.Description）.FirstOrDefault （）

 };

   . . .
   ContactType = ( from contract in dataContext.CustomerContractDbEntities
                              join contracttype in dataContext.ContractRateTypeDbEntities on contract.ContractRateTypeId equals contracttype.ContractRateTypeId
                              where contract.CustomerId == c.CustomerId
                              orderby contact.SomeDateField descending
                              select contacttype.Description ).FirstOrDefault()
 };

这篇关于GROUP BY&amp;前1个问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！