EF 5.0 Linq 分组后获取每个分组的第一条数据

首先想到如下写

var query=_packageRepository.GetAll()

var list = (from p in query
            group p by p.PackageNo into g
            select new GetDingPackageCheckResultDto
            {
               IsConfig = g.Count() > 1,
               CustName = g.Select(v => v.CustName).First(),
               Status = g.Select(v => v.Status).FirstOrDefault(),
               PicturePath = g.Select(v => v.PicturePath).First(),
               OrderDate = g.Select(v => v.OrderDate).First(),
               PackageNo = g.Select(v => v.PackageNo).First(),
             }).ToList();

运行后会抛出异常：

提示无法翻译First()

后面查找资料发现EF5.0分组后不支持复杂的聚合函数，只支持基础的聚合函数如上的Count()是支持的

后面使用了如下方式

            var queryA = _packageRepository.GetAll()
                            .WhereIf(input.OrderDate.HasValue, p => p.OrderDate.Date 
                            == input.OrderDate.Value.Date);

            //获取每个组下的第一条数据
            var queryP = queryA.Select(t => t.PackageNo).Distinct()
                          .SelectMany(x => queryA.Where(y => y.PackageNo == x).Take(1));
            //获取分组数据（PackageNo为分组条件）
            var queryC = queryA.GroupBy(c => c.PackageNo).Select(t => new { PackageNo = 
                         t.Key, IsConfig = t.Count() > 0 });

            var query =(from a in queryC
                    join q in queryP on a.PackageNo equals q.PackageNo
                    select new PackageDto()
                    {
                        IsConfig = a.IsConfig,
                        CustName = q.CustName,
                        Status = q.Status,
                        Type=q.Type,
                        OrderDate = q.OrderDate,
                        PackageNo = q.PackageNo,
                    });

            //转成sql语句
            var c = query.ToQueryString();

翻译后的sql

SET @__input_OrderDate_Value_Date_0 = '2022-03-02 00:00:00';

SELECT `t`.`c` AS `IsConfig`, `t3`.`CustName`, `t3`.`Status`, `t3`.`Type`, `t3`.`OrderDate`, `t3`.`PackageNo0` AS `PackageNo`
FROM (
    SELECT `p`.`PackageNo`, COUNT(*) > 0 AS `c`
    FROM `packages` AS `p`
    GROUP BY `p`.`PackageNo`
) AS `t`
INNER JOIN (
    SELECT `t2`.`CustName`, `t2`.`OrderDate`, `t2`.`PackageNo` AS `PackageNo0`, `t2`.`Type`, `t2`.`Status`
    FROM (
        SELECT DISTINCT `p0`.`PackageNo`
        FROM `packages` AS `p0`
    ) AS `t0`
    INNER JOIN (
        SELECT `t1`.`CustName`, `t1`.`OrderDate`, `t1`.`PackageNo`, `t1`.`Type`, `t1`.`Status`
        FROM (
            SELECT `p1`.`CustName`, `p1`.`OrderDate`, `p1`.`PackageNo`, `p1`.`Type`, `p1`.`Status`, ROW_NUMBER() OVER(PARTITION BY `p1`.`PackageNo` ORDER BY `p1`.`Id`) AS `row`
            FROM `packages` AS `p1`
        ) AS `t1`
        WHERE `t1`.`row` <= 1
    ) AS `t2` ON `t0`.`PackageNo` = `t2`.`PackageNo`
) AS `t3` ON `t`.`PackageNo` = `t3`.`PackageNo0`
WHERE CONVERT(`t3`.`OrderDate`, date) = @__input_OrderDate_Value_Date_0