c# - 如何在linq中使用sql函数

Question

我想将此过程更改为 Entity Framework 的 linq:

SELECT COUNT([dbo].[Events].[Request]) as requestCount, 
MONTH([dbo].[Events].[Request]) as months
FROM [dbo].[Events]
GROUP BY MONTH([dbo].[Events].[Request])

我用了很多解决方案，但没有一个有效!提前致谢!

编辑:我使用的解决方案: 解决方案1: The type or namespace name 'Objects' does not exist in the namespace 'System.Data'

但我从来没有找到 SqlFunctions!!

解决方案 2:

var events = db.Events.GroupBy(x => Convert.ToDateTime(x.Request).Month)
                      .Select(g => new { max = g.Max(), Count = g.Count() })
                      .ToList();

给我这个错误:

LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.DateTime)' method, and this method cannot be translated into a store expression.

Answer 1

var events = db.Events.GroupBy(x => x.Request.Month)
                      .Select(g => new { Month = g.Key, RequestCount = g.Count() })
                      .ToList();

如果 Request 不是像您的示例可能推断的那样的 DateTime，那么您可以:

var events = db.Events.Select(item => new 
                            { 
                                Month = DateTime.Parse(item.Request).Month,             
                                Event = item
                            })
                      .GroupBy(x => x.Month)
                      .Select(g => new { Month = g.Key, RequestCount = g.Count() })
                      .ToList();

为了好玩，您可以使用 GroupBy 的不同重载(其中 select lambda 是另一个参数:

var events = Events
    .Select(item => new
        {
            Month = DateTime.Parse(item.Request).Month,
            Event = item
        })
    .GroupBy(x => x.Month, 
        (key, collection) => new 
        { 
            Month = key, 
            RequestCount = collection.Count() 
        }).ToList();