时间戳

首页 > 解决方案 > 如何加入两个 linq 查询以查找它们之间的一些公共数据

linq - 如何加入两个 linq 查询以查找它们之间的一些公共数据

问题描述

我有两个如下的 linq 查询，它们的输出结果是正确的：

var mytotal = _context.Apiapplicant.Where(c => !c.ApiRequestDate.Equals("") && c.IsDeleted.Equals(false)).GroupBy(o => new
            {
                Month = o.ApiRequestDate.Substring(5, 2),
                Year = o.ApiRequestDate.Substring(0, 4)
            }).Select(g => new
            {
                Month = g.Key.Month,
                Year = g.Key.Year,
                Total = g.Count()
            }).OrderByDescending(a => a.Year).ThenByDescending(a => a.Month).ToList();

上面的查询给出了每个月注册的请求总数。

var numerator = from t1 in _context.Apiapplicant
                            join t2 in _context.ApiApplicantHistory on t1.Id equals t2.ApiApplicantId
                            join t3 in _context.EntityType on t2.LastReqStatus equals t3.Id
                            //join t4 in mytotal on new { t2.Date.Substring(0,4), t2.Date.Substring(2,5) } equals new { t4.Year, t4.Month }
                            // join t4 in mytotal on  t2.Date.Substring(2, 5) equals t4.Month

                            where t1.IsDeleted == false && t3.Name == "granted" && t2.Date != null && t1.ApiRequestNo != null

                            group t1
                            by new
                            {
                                lastReq = t2.LastReqStatus,
                                Year = t2.Date.Substring(0, 4),
                                Month = t2.Date.Substring(5, 2)
                            } into g

                            select new
                            {
                                Year = g.Key.Year,
                                Month = g.Key.Month,
                                lastReq = g.Key.lastReq,
                                GrantedCount = g.Count()
                            };


            var GrantedReqStatus = numerator.OrderByDescending(x => x.Year).ThenByDescending(a => a.Month).ToList();

上面的查询给出了每个月具有“已授予”状态的请求总数。现在我想查找每个月注册的所有请求，其中有多少具有“授予”状态。要获得它，我的意思是 mytotal 中每个月有多少条记录在分子中。为了找到这一点，我进行了如下查询，该查询应同时加入 GrantedReqStatus 和 mytotal 查询：

var LastGrantedStatus = (from t1 in mytotal
                                    from t2 in GrantedReqStatus
                                    where (t1.Month == t2.Month) && (t1.Year == t2.Year)
                                    group t1
                            by new
                            {
                                //  lastReq = t2.LastReqStatus,
                                Year = t1.Year,
                                Month = t1.Month
                            } into g
                                    select new {  Month=g.Key.Month, Year=g.Key.Year,grcount = g.Count()}).ToList();

但是当我运行项目时，以下表达式不会打印任何内容：

Console.WriteLine("LastGrantedStatus.Count:" + LastGrantedStatus.Count);
            foreach (var t in LastGrantedStatus)
            {
                Console.WriteLine("t.GrantedCount + t.Month:" + t.Month + "+" + t.grcount); //+ "+" + t. + "+" + t.Year);
            }

如果有人可以建议我一个解决方案，我将不胜感激。

标签： linqasp.net-core

解决方案

您没有得到任何结果的最可能原因是：

  1. 数据不符合您的预期。下面的代码确实返回了您的确切查询的数据。
  2. 之间的比较t2.LastReqStatus equals t3.Id可能是错误的。状态似乎不太可能等于 id。

无论如何，以下内容逐字使用您的查询代码，并返回带有提供的数据模拟的结果。

它正在返回：

2020 06 1 2

这是带有模拟数据的代码：

void Main()
{
    var _context = 
        new 
        {
            Apiapplicant = new[] 
            {
                new { Id = 1, ApiRequestDate = DateTime.Now.ToString("yyyy/MM/dd"), IsDeleted = false, ApiRequestNo = "1" }, 
                new { Id = 1, ApiRequestDate = DateTime.Now.ToString("yyyy/MM/dd"), IsDeleted = false, ApiRequestNo = "2" }, 
                new { Id = 2, ApiRequestDate = DateTime.Now.ToString("yyyy/MM/dd"), IsDeleted = false, ApiRequestNo = "3" }, 
                new { Id = 3, ApiRequestDate = DateTime.Now.ToString("yyyy/MM/dd"), IsDeleted = false, ApiRequestNo = "4" }, 
            },
            ApiApplicantHistory = new[] {
                new { ApiApplicantId = 1, LastReqStatus = 1, Date = DateTime.Now.ToString("yyyy/MM/dd") } 
            },
            EntityType = new[]
            { 
                new {Id = 1, Name = "granted" }
            } 
        };
        
    var mytotal = _context.Apiapplicant.Where(c => !c.ApiRequestDate.Equals("") && c.IsDeleted.Equals(false)).GroupBy(o => new
    {
        Month = o.ApiRequestDate.Substring(5, 2),
        Year = o.ApiRequestDate.Substring(0, 4)
    }).Select(g => new
    {
        Month = g.Key.Month,
        Year = g.Key.Year,
        Total = g.Count()
    }).OrderByDescending(a => a.Year).ThenByDescending(a => a.Month).ToList();

    var numerator = from t1 in _context.Apiapplicant
                    join t2 in _context.ApiApplicantHistory on t1.Id equals t2.ApiApplicantId
                    join t3 in _context.EntityType on t2.LastReqStatus equals t3.Id

                    where t1.IsDeleted == false && t3.Name == "granted" && t2.Date != null && t1.ApiRequestNo != null

                    group t1
                    by new
                    {
                        lastReq = t2.LastReqStatus,
                        Year = t2.Date.Substring(0, 4),
                        Month = t2.Date.Substring(5, 2)
                    } into g

                    select new
                    {
                        Year = g.Key.Year,
                        Month = g.Key.Month,
                        lastReq = g.Key.lastReq,
                        GrantedCount = g.Count()
                    };


    var GrantedReqStatus = numerator.OrderByDescending(x => x.Year).ThenByDescending(a => a.Month).ToList();
    GrantedReqStatus.ForEach(x => Console.WriteLine($"{x.Year} {x.Month} {x.lastReq} {x.GrantedCount}"));
}

推荐阅读