How to use mod function for linq to ms access?

user_713847 0
Viewed 591

I am trying to query day of week using linq and I have ms access as the database. Now I have used the following code using % (mod) to check for day of week since linq to query doesn't support DayOfWeek.

planQuery = planQuery.Where(x => DbFunctions.DiffDays(firstSunday, x.Date) % 7 
                                                                == (int)DayOfWeek.Monday && x.Date != null);

But the problem occurs when this code is translated to sql it looks like this:

SELECT *
FROM myTable
where (DateDiff("d", [Datum], 1) % 7) = 1

Which in turn throws syntax error when I try to run it because of the %. After looking around I realized % equivalent of ms access is mod

So the following code runs fine. But this is not what the translated SQL look like.

SELECT *
FROM myTable
where (DateDiff("d", [Datum], 1) mod 7) = 1

How can I use linq to query ms access using mod function?

dayofweek ms-access linq c#
2 Answers

Modulus is an operation that's easily reproduced with some division, rounding and subtraction.

DbFunctions.DiffDays(firstSunday, x.Date) - (((int)DbFunctions.DiffDays(firstSunday, x.Date) / 7) * 7)

This should be equal to DbFunctions.DiffDays(firstSunday, x.Date) % 7, at slightly higher overhead.

user_72968930

I dont see why you say DayOfWeek is not functional, i wrote:

planQuery = planQuery.Where(x => (x.Date != null) &&
                              (DbFunctions.DiffDays(firstSunday, x.Date) % 7 == (int)DayOfWeek.Monday));

because if x.Date is null, you dont test the second part. In your code, you could have an error if x.Date is null, because you test it at the end of test.

since linq to query doesn't support DayOfWeek

DayofWeek is C#, not a special linq directives

so if you want to use Linq Query syntax, the answer could be:

var result = from x in planQuery
             where (x.Date != null) &&
                  (DbFunctions.DiffDays(firstSunday, x.Date) % 7 == (int)DayOfWeek.Monday)
                     select x;
user_73807790
Related