search

bubibubi / JetEntityFrameworkProvider

Microsoft Access (Jet) Entity Framework provider
89 stars 26 forks source link

OrderBy() and OrderByDescending() return wrong results when used with bool properties #42

Open Ahmed-Abdelhameed opened 5 years ago

Ahmed-Abdelhameed commented 5 years ago

The following query: context.Items.OrderBy(x => x.IsActive); ..returns items with IsActive == true first then those with IsActive == false (should be the opposite).

Similarily, the following query: context.Items.OrderByDescending(x => x.IsActive); ..returns items with IsActive == false first then those with IsActive == true (should be the other way around).

This seems to be a bug in JetEntityFrameworkProvider because it works the right way with SQL Server and, of course, with any other collection. As a workaround, I'm currently using context.Items.ToList().OrderBy....

jeremy-morren commented 5 years ago

This is because SQL Server uses Bit (1 or 0) for boolean values. Therefore EntityFramework must use ORDER BY [field] ASC in the SQL Statement.

However, JetEntity uses the "Yes/No" Access field for boolean values. Here, "Yes" is a value of -1, therefore they appear first.

A better workaround is context.Items.OrderBy(x => !x.IsActive).

See https://stackoverflow.com/questions/8827447/why-is-yes-a-value-of-1-in-ms-access-database for more details.

Ahmed-Abdelhameed commented 5 years ago

@jeremy-morren I do know that a "Yes/No" field in Access is represented as 0 or -1 value. However, since it's mapped by EF as a .NET Boolean type, it should be consistent with the Boolean type of the .NET Framework. EF is an ORM after all and works as a separation layer, therefore, it should (and it does) handle such conflicts behind the scenes (one example off the top of my head is that EF translates null to DBNull and vice-versa).