CODEWITHSUNDEEP
sqllinqgroup-by
user1843078
user1843078

Reputation: 11

Linq to Entities Group By

I written the following MS SQL Server query but am having trouble making a linq to entities statement out of it:

Goal is to retrieve leads from the database that are from the past 7 days and then group by day of the week and count the leads per day

SELECT      DATEPART(day, DateCreated) AS Day, LEFT(DATENAME(dw, DateCreated), 3) AS DayOfWeek, COUNT(*) AS Count
FROM         dbo.[File]
WHERE     (DateCreated > GETDATE() - 7)
GROUP BY DATEPART(day, DateCreated), DATENAME(dw, DateCreated)
ORDER BY Day, DATENAME(dw, DateCreated)

Example results are:

Here is a link containing the example results: link

Any help with an example on how to write this using linq to entities would be very much appreciated. Vb (preferred) or c#?

Thank you

Upvotes: 1

Views: 804

Answers (2)

roman
roman

Reputation: 117337

Try this

var query =
    from f in File
    where f.DateCreated > DateTime.Now.AddDays(-7)
    group f by new { Day = f.DateCreated.Day, DayOfWeek = f.DateCreated.DayOfWeek } into g
    select new
    {
        Day = g.Key.Day,
        DayOfWeek = g.Key.DayOfWeek.ToString().Substring(0, 3),
        Count = g.Count()
    };

If you need culture specific name, you can use Date.ToString("dddd", System.Globalization.CultureInfo.CreateSpecificCulture("en-US")):

var query =
    from f in File
    where f.DateCreated > DateTime.Now.AddDays(-7)
    group f by new { Day = f.DateCreated.Day, DayOfWeek = f.DateCreated.ToString("dddd", System.Globalization.CultureInfo.CreateSpecificCulture("en-US")) } into g
    select new
    {
        Day = g.Key.Day,
        DayOfWeek = g.Key.DayOfWeek.Substring(0, 3),
        Count = g.Count()
    };

update: Ok, I'm not a VB expert, but for VB you can try something like this

    Dim query =
    From f In File
    Where f.DateCreated > DateTime.Now.AddDays(-7)
    Group f By Day = f.DateCreated.Day, DayOfWeek = f.DateCreated.DayOfWeek.ToString().Substring(0, 3)
    Into Group, Count = Count()

Upvotes: 2

D Stanley
D Stanley

Reputation: 152501

You'll be better off not grouping by day and day of week in the Linq-to-SQL but just grouping by date and then extracting the date parts outside of the query (since processing the dates later won't affect query performance):

var query = 
    (from f in Files 
        where EntityFunctions.AddDays(f.DateCreated, 7) >= DateTime.Today()
        group f by EntityFunctions.TruncateTime(f.DateCreated) into g
        select new { DateCreated = g.Key, Count = g.Count() })
    .ToList()  // materialize to switch from Linq-to-SQL to Linq-to-Objects
    .Select( g => new { 
                       Day = g.DateCreated.Day,, 
                       DayOfWeek = g.DateCreated.ToString("ddd"),
                       Count = g.Count
                       });

Upvotes: 1

Related Questions