CODEWITHSUNDEEP
c#linqentity-framework
Sanjay Patel
Sanjay Patel

Reputation: 983

filter data based on day, week and month

I want to filter data of today, week and month.

var data = new DashboardViewModel()
           {
               Today = new State() { Download = db.Download.Where(x => x.Time == DateTime.Today).Count(), Visit = db.Visit.Where(x => x.Time == DateTime.Today).Count() },
               Week = new State() { Download = db.Download.Where(x => x.Time.DayOfYear <= Day && x.Time.DayOfYear >= Day - 7).Count(), Visit = db.Visit.Where(x => x.Time.DayOfYear <= Day && x.Time.DayOfYear >= Day - 7).Count() },
               Month = new State() { Download = db.Download.Where(x => x.Time.DayOfYear <= Day && x.Time.DayOfYear >= Day - 30).Count(), Visit = db.Visit.Where(x => x.Time.DayOfYear <= Day && x.Time.DayOfYear >= Day - 30).Count() },
               Total = new State() { Download=db.Download.Count(),Visit=db.Visit.Count()}
           };

It gives the error:

The specified type member 'DayOfYear' is not supported in LINQ to Entities.

How can I resolve this error, or is there any other better way to solve this problem?

Upvotes: 5

Views: 3053

Answers (2)

DavidN
DavidN

Reputation: 5197

The issue is that when LINQ to Entities is parsing your lambda expression and converting it into SQL, it has no idea what 'DayOfYear' is, and how that maps to valid SQL. This is where DbFunctions (EntityFunctions in EF before v6) comes in, which allows you to use date manipulation functions in LINQ to Entities. For example, to filter by week, you could use:

Week = new State() { 
    Download = db.Download.Where(x => DbFunctions.DiffDays(DbFunctions.TruncateTime(x.Time), Day) <= 7).Count(), 
    Visit = db.Visit.Where(x => DbFunctions.DiffDays(DbFunctions.TruncateTime(x.Time), Day) <= 7).Count() }

TruncateTime removes the time component from a date, and DiffDays(d1, d2) gives you the difference between 2 days (d2 - d1).

Upvotes: 2

user1968030
user1968030

Reputation:

See this answer:

var ruleDate = Convert.ToDateTime(rule.data).Date;
return jobdescriptions.Where(j.Deadline.Year == ruleDate.Year 
                       && j j.Deadline.Month == ruleDate.Month 
                       && j.Deadline.Day == ruleDate.Day);

Upvotes: 1

Related Questions