代码之家 › 专栏 › 技术社区 › Colin Desmond

如何在Linq中的select查询中使用Count和Group?

linq-to-sql

Colin Desmond · 技术社区 · 17 年前

    var allRequests = model.GetAllRequests();

    var unsatisifedRequests = (from request in allRequests
                               where request.Satisfied == false
                               select request)
                               .OrderBy(r => r.RequestedOn)
                               .GroupBy(r => r.RequestedCountryId);

然后我做了一个 foreach 对于未满足的请求,为每个请求构建一个新的ToRequestListSummary对象。这意味着,如果我从查询中“返回”4项,它将对DB进行4次调用,每次foreach循环一次,以获取单个行。

这似乎是使用Linq的错误方式,因此我尝试将此查询转换为使用投影直接返回TOARequestListSummary对象的查询,并得出以下结论:

var summaries = (from request in allRequests
                             where request.Satisfied == false
                             group request by request.RequestedCountryId into requestGroups
                             select new TOARequestListSummary
                             {
                                 CountryName = requestGroups.First().RequestedCountry.Description,
                                 RequestCount = requestGroups.Count(),
                                 FirstRequested = requestGroups.First().RequestedOn
                             });

但当我运行此程序时,会出现以下异常:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

我已经知道相当于EXISTS的Linq是Contains,但我不知道如何将其引入查询中。

2 回复 | 直到 17 年前

Ryan Versaw 17 年前

这应该适合您:

var summaries = (from request in allRequests
                 where request.Satisfied == false
                 group request by request.RequestedCountry into g
                 select new TOARequestListSummary
                 {
                     CountryName = g.Key.Description,
                     RequestCount = g.Count(),
                     FirstRequested = g.Min(i => i.RequestedOn)
                 });

在这个查询的原始版本(您发布的第二个版本)中,组的键是 RequestedCountryId . 虽然这在技术上是分组的,但实际上您希望使用关联的对象。这样,您就可以轻松访问所需的属性,并且不必担心抓取第一个项目。

Colin Desmond 17 年前

抱歉,这是一个答案,而不是Ryan答案的附加注释,但太长了,不适合。。。

这很奇怪。在LinqPad中,以下工作是一种处理:

from request in TOARequests
where request.Satisfied == false
&& request.Active == true
orderby request.RequestedOn
group request by request.RequestedCountry into g
select new 
{
    CountryName = g.Key.Description,
    RequestCount = g.Count(),
    FirstRequested = g.First().RequestedOn
}

但是下面的代码在C中抛出了相同的翻译异常#

var summaries = (from request in context.Repository<TOARequest>()
                             where request.Satisfied == false
                             && request.Active == true
                             orderby request.RequestedOn
                             group request by request.RequestedCountry into g
                             select new
                             {
                                 CountryName = g.Key.Description,
                                 RequestCount = g.Count(),
                                 FirstRequested = g.First().RequestedOn
                             }).ToList();