How to get the current month date wise records using linq to sql?

Question

I want to get the count of rows by date for each day this month, like this:

Date          count
1/03/2013     18
2/03/2013     41
28/03/2013    12
29/03/2013    14

How to write the query for that?

May be this is related: http://stackoverflow.com/questions/2105208/linq-group-by-month-question — Habib, Mar 29 '13 at 05:59
if user wants to see january month datewise records then how to write the linq to sql query? — Sravanti, Mar 29 '13 at 06:08
You want to get the number of occurences of the specific date? — Sajeetharan, Mar 29 '13 at 06:51

Corak · Answer 1 · 2013-03-29T07:33:30.427

2

So, I assume you have some table with a datetime field in which you have the date "1/03/2013" 18 times, meaning you have 18 rows in that table with that date.

Then you should get the count of each day of a month in a year by something like this:

var year = 2013;
var month = 3;
var q = from t in DBContext.TableName
        where t.DateField.Year == year && t.DateField.Month == month
        group t by t.DateField.Date
           into g
           select new
           {
               dateField = g.Key,
               countField = g.Count()
           };

See also the LINQ to SQL Samples

edited Mar 29 '13 at 07:33

answered Mar 29 '13 at 07:19

Corak

2,688
2
21
26

Getting Exception:Exception occured while validating visitor. The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. – Sravanti Mar 29 '13 at 09:10
@Venkat Is your "Date" field a real `DateTime` field? Are you using `t.Date.Date`? Is the name of the field really "Date" (I try to avoid naming fields to reserved words; hence `DateField`)? Did you by any chance adapt it differently to your needs? – Corak Mar 29 '13 at 09:56
we are maintaining date field as datetime in database.var query = (from t in cxt.visitor where t.singin.Year == year && t.singin.Month == month group t by t.singin.Date into g select new { dateField = g.Key, countField = g.Count() }).ToList(); that was my query but getting above exception. – Sravanti Mar 29 '13 at 10:46
@Venkat huh, according to some other questions on SO that error seems to indicate, that the `signin` datetime field cannot be directly mapped to a database field. See http://stackoverflow.com/questions/11584660/the-specified-type-member-is-not-supported-in-linq-to-entities-only-initializer or http://stackoverflow.com/questions/12036210/ef-4-the-specified-type-member-is-not-supported-in-linq-to-entities for example. Is it perhaps a custom property? – Corak Mar 29 '13 at 19:24

How to get the current month date wise records using linq to sql?

1 Answers1