amir stack amir stack - 4 months ago 18
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() })
.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.

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