LINQ如何返回上一个日期以及第一个和最后一个计数之间的差 [英] LINQ how to return last date and difference between first and last count

问题描述

我有一个包含以下列和示例数据的表:

I have a table with the following column and sample data:

acteename   updated_at              count (count is not sorted in db)

dev-52      2/7/2020 5:56:43 PM     1
dev-52      2/7/2020 5:56:52 PM     2
dev-52      2/7/2020 5:57:00 PM     3
dev-52      2/7/2020 5:57:49 PM     4
dev-52      2/7/2020 5:59:19 PM     5
dev-52      2/7/2020 6:01:51 PM     6
dev-52      2/7/2020 6:06:21 PM     7
dev-52      2/7/2020 6:14:51 PM     8
dev-52      2/7/2020 6:31:23 PM     9
dev-52      2/7/2020 6:47:54 PM     10
dev-52      2/7/2020 7:04:26 PM     11
dev-52      2/7/2020 7:20:58 PM     12

我想返回按 updated_at 和最后 updated_at 排序的 first 和 last 计数之间的差异其中 acteename 等于我从不同表中获得的某个名称

I want to return diff between first and last count sorted by updated_at and last updated_at where acteename is equal to some name that I get from different table

IQueryable<Events> list = _DBcontext.Events.GroupBy(p => p.ActeeName)
                            .Select(g => new
                            {
                                acteename = g.Key,
                                lastupdated = g.OrderByDescending(p => p.UpdatedAt).First(),
                                firstcount = g.OrderBy(p => p.UpdatedAt).First().Count,
                                lastcount = g.OrderByDescending(p => p.UpdatedAt).First().Count
                            })
                            .Select(x => new { x.acteename,x.lastupdated, diff = x.lastcount - x.firstcount })
                            .Where(a => a.acteename == item.AppName)
                            .ToList();

出现以下错误:

无法隐式转换类型'System.Collections.Generic.List<<<匿名类型:字符串acteename，TestApp.Models.Events最后更新，int diff>>''System.Linq.IQueryable< TestApp.Models.Events>'.一个明确的转换存在(您是否缺少演员表?)

Cannot implicitly convert type 'System.Collections.Generic.List<<anonymous type: string acteename, TestApp.Models.Events lastupdated, int diff>>' to 'System.Linq.IQueryable<TestApp.Models.Events>'. An explicit conversion exists (are you missing a cast?)

预期输出:

dev-52      2/7/2020 7:20:58 PM     11

对此不确定，LINDA查询是否正确，任何帮助都将是有用的:)

kinda stuck on this not sure if the LINQ query is correct or not, any help will be useful :)

更新:

我运行的确切查询:

var list = from p in _DBcontext.AppProcessCrashEvents
                                   where p.Actee == item.AppGuid
                                   group p by p.Actee
            into gList
                                   select new
                                   {
                                       acteenamex = gList.Key,
                                       lastUpdated_at = gList.OrderBy(x => x.UpdatedAt).Last().UpdatedAt,
                                       diff = gList.OrderBy(x => x.UpdatedAt).Last().CrashCount -
                                                             gList.OrderBy(x => x.UpdatedAt).First().CrashCount
                                   };

推荐答案

一个人可以在最开始就应用过滤器.将 group by 与 into 一起使用，然后最终选择所需的内容.由于它创建了一个匿名对象，因此最好从'var`开始.

One can apply the filter in very beginning itself. Use group by with into and then finally select what you need. Since its creating a anonymous object hence it would be better to start with 'var`.

已编辑:获取内存中的数据，然后应用 group by ，以避免在与数据库端分组进行映射时出现任何问题.

Edited: Fetch the data in memory and then apply group by to avoid any problem in mapping with database side grouping.

var list =  (from p in _DBcontext.Events
            where p.acteename == item.AppName select p).ToList();

var fList = (from p in list 
            group p by p.acteename
            into gList
            select new
            {
               acteenamex = gList.Key,
               lastUpdated_at = gList.OrderBy(x => x.updated_at).Last().updated_at,
               diff = gList.OrderBy(x => x.updated_at).Last().count - 
                                      gList.OrderBy(x => x.updated_at).First().count
            }).ToList();

这篇关于LINQ如何返回上一个日期以及第一个和最后一个计数之间的差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！