amir stack amir stack - 1 year ago 112
SQL Question

how to use sql function in linq

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

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.

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

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

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() 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download