Convert sql query to Entity Framework

Question

The following sql query works and receives 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 several days I have been trying to figure out how to do this with the Framework entity. So far my best approach is:

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, and not just by date. The result is this:

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

but it should look like

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

• How to change the date format used for grouping?
• How to filter only Days of the current month, as in an SQL query?

Answer 1

You could 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 .Dayto your "visit.Date" - does it work?

Mark

Answer 2

:

• DayOfMonth , 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

Now I use the 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 proposed solutions work perfectly!

I only had to change the litle bit

instead of visit.Date.Day I had to:

visit.Date.Value.Day

Now I have the result:

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

Thanks!