convert an sql query to Entity Framework

Question

following sql query is working and gets the visits per day for the current month

select  date(date) as DayDate, count(*) As visitsaday from Visits group by DayDate having DayDate between date('now','start of month') and date('now','start of month','+1 month','-1 day')

For days I try to figure out how to get this running with the entity Framework. My best aproach so far ist this:

ObjectQuery<Visit> visits = fitent.VisitMenge;
                var uQuery =
                    from visit in visits
                    group visit by visit.Date into g
                    select new
                    {
                        DayOfMonth = g.Key,
                        VisitsPerDay = g.Count()
                    };

The Problem here is, that it will be grouped by Date + time instead of just the Date. An result is like:

[0] = { DayOfMonth = {06.07.2009 12:38:59}, VisitsPerDay = 1 }

but it should look like

[0] = { DayOfMonth = {06.07.2009}, VisitsPerDay = 12 }

• How can the Date Format changed which is used for grouping ?
• How to filter Just the Days of the current Month like in the SQL Query ?

Answer 1

Could you do this:

ObjectQuery<Visit> visits = fitent.VisitMenge;
var uQuery = from visit in visits
             group visit by visit.Date.Day into g
             select new
             {
                 DayOfMonth = g.Key,
                 VisitsPerDay = g.Count()
             };

Just add a .Day to your "visit.Date" - does that work??

Marc

Answer 2

You need to do two things:

• Restrict results by the date with a where
• Make DayOfMonth just the date and not the full DateTime

-

var uQuery = from visit in visits
             where visit.Date.Month == DateTime.Now.Month 
                 && visit.Date.Year == DateTime.Now.Year
             group visit by visit.Date into g
             select new
             {
                 DayOfMonth = g.Key.Date,
                 VisitsPerDay = g.Count()
             };

Answer 3

I now use following:

ObjectQuery<Visit> visits = fitent.VisitMenge;
                var uQuery =
                    from visit in visits
                    where visit.Date.Value.Month == DateTime.Now.Month
                 && visit.Date.Value.Year == DateTime.Now.Year
                    group visit by visit.Date.Value.Day into g
                    select new
                    {
                        DayOfMonth = g.Key,
                        VisitsPerDay = g.Count()
                    };

The suggested Solutions working fine together!

I had to change just a litle bit

instead of visit.Date.Day I had to:

visit.Date.Value.Day

I now have an result like:

[0] = { DayOfMonth = 6, VisitsPerDay = 3 }

Thank you !