how to use sql function in linq

Question

I want to change this procedure to linq for entity framework:

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

I used many solution but none of them works! Thanks in advance!

Edit: Solutions that I use them: solution 1: The type or namespace name 'Objects' does not exist in the namespace 'System.Data'

but I never find SqlFunctions!!

solution 2:

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

give me this error:

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

You don't need a sql function for `MONTH`, you can use the `Month` property of `DateTime`. By the way, you ignore the year in this query. — Tim Schmelter, Jul 20 '16 at 11:44
_I used many solution but none of them works!_ - add one to the question so we can help you fix it — Gilad Green, Jul 20 '16 at 11:48

Gilad Green · Accepted Answer · 2016-07-20T12:12:50.380

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

If Request isn't a DateTime like your example might infer then you can:

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();

And just for the fun of it you can use a different overload of GroupBy (where the select lambda is another parameter:

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();

@amirstack - Added another way of doing :) – Gilad Green Jul 20 '16 at 12:13 — Gilad Green, Jul 20 '16 at 12:13

how to use sql function in linq

1 Answers1