3

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?

SZT
  • 1,771
  • 4
  • 26
  • 53

2 Answers2

1

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.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • That's so simple and "elegant". It's kind of a hack, but I don't have to fight the bug now. Thanks! – SZT Sep 27 '20 at 20:17
0

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;
Frenchy
  • 16,386
  • 3
  • 16
  • 39
  • How does this reflect: `After looking around I realized % equivalent of ms access is mod`? – Stefan Sep 27 '20 at 11:01
  • I don't see how's this is any different from my code. – SZT Sep 27 '20 at 11:42
  • if x.Date is null, you'll have an error because DBfunctions is called first in && test, in my code if x.Date is null, the test is false and the second part is not tested – Frenchy Sep 27 '20 at 11:55
  • That still doesn't answer the question I'm looking for. And I mentioned `linq to query` not just `linq`. – SZT Sep 27 '20 at 12:27
  • yes i have added the linq to query..and i dont see problem to use % with it.. So i suppose planQuery is IEnumerable or IQueryable collection.... – Frenchy Sep 27 '20 at 12:52
  • That's what I tried to explain in the question. When the above LinQ gets translated to SQL query it doesn't translate the query properly. It should've used `mod` instead of `%` – SZT Sep 27 '20 at 13:23
  • sorry i am lost with what you want..you want linq query syntax or SQL syntax...i dont see and i dont have error using linq with % (mod) – Frenchy Sep 27 '20 at 13:27
  • your title of question speaks about mod for linq.... – Frenchy Sep 27 '20 at 13:32
  • 1
    This is a limitation of Access EF Core provider not a Linq query itself. – Svyatoslav Danyliv Sep 27 '20 at 16:52