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

[Ahmed-Abdelhameed]

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]

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]

@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).