从日期时间列到实体不同年份的Linq [英] Linq to entities distinct year from datetime column

问题描述

我有一张包含付款记录的表.这些记录有一个datetime列，其中包含购买的实际日期时间.现在，我想在页面顶部放置一个动态填充的下拉列表，以便用户可以选择年份，然后查看该年份的付款记录.

I have a table containing payment records. Those records have a datetime column which contains the actual datetime of the purchase. Now I want to put a dynamically populated dropdown on top of the page so users can select a year and then view the payment records of that year.

为此，我尝试从付款记录中区分出年份.这就是我所拥有的，但是失败了

In order to do so I try to distinct the years out of the payment records. This is what I have but it fails

    List<DateTime> lstYears = new List<DateTime>();
    lstYears = context.Payments
               .Select(
                       p => new { p.TimeOfPurchase.GetValueOrDefault().Year }
                      )
               .Distinct()
               .ToList()
               .Select(x => new DateTime(x.Year, 1, 1))
               .ToList(); 

我收到的错误是"LINQ to Entities无法识别方法GetValueOrDefault".但是，当我删除该方法时，无法键入" p.TimeOfPurchase.Year ".

The error I receive is 'LINQ to Entities does not recognize the method GetValueOrDefault'. But when I remove that method I cannot type "p.TimeOfPurchase.Year".

是否可以仅选择所有这些付款记录的不同年份?

Is it possible to only select the distinct years of all those payment records?

推荐答案

尝试 p.TimeOfPurchase.Value.Year.由于已将其转换为SQL，因此应处理null.您可以添加 Where 子句以过滤空值.另外，您无需将其放入匿名类.您也可以使用 AsEnumerable 而不是 ToList 来将Linq to Objects转换为Linq，这将避免创建不需要的列表.最后，只需将查询分配给您的变量即可，不要使用只会被丢弃的空列表来初始化查询.

Try p.TimeOfPurchase.Value.Year. Since it's translated to SQL the nulls should be handled. And you can add a Where clause to filter the nulls. Also you don't need to put it into an anonymous class. Also you can use AsEnumerable to translate to Linq to Objects instead of ToList and it will avoid creating an unneeded list. Finally just assign the query to your variable and don't initialize it with an empty list that just gets thrown away.

List<DateTime> lstYears = context.Payments
    .Where(p => p.TimeOfPurchase != null)
    .Select(p => p.TimeOfPurchase.Value.Year)
    .Distinct()
    .AsEnumerable()
    .Select(x => new DateTime(x, 1, 1))
    .ToList(); 

这篇关于从日期时间列到实体不同年份的Linq的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！