Boolean column in Microsoft Access and filtering data using linq

Question

[Apologies for long question but I thought it would be clearer for others to answer]

I have a Microsoft Access database and it contains a table "Customers" with following columns:

• ID (auto number)
• Name (Text)
• Active (yes/no)

I created the database table class in C# as below:

[Table (Name = "Products")]
public class Product
{
    [Column (IsPrimaryKey = true, Name = "ProductID")]
    public int ID;

    [Column (Name = "ProductName")]
    public string Name;

    [Column (Name = "Active")]
    public bool Active;
}

And I am using following code snippet to get the products which are active:

using (var con = new OleDbConnection 
      (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\LearnLinq.accdb")) {
    var db = new DataContext (con);
    var productTable = db.GetTable<Product> ();
    var allProducts = from p in productTable
                      where p.Active
                      select p;

    foreach (var p in allProducts) {
        AddLine ("ID: " + p.ID.ToString () +
            ", Name: " + p.Name +
            ", Active: " + p.Active.ToString ());
    }
}

The problem is that above query results in "NO RECORDS". I tried to analysed the SQL generated and it says something like below:

SELECT [t0].[ProductID] AS [ID], [t0].[ProductName] AS [Name], [t0].[Active]
FROM [Products] AS [t0]
WHERE [t0].[Active] = 1

Any clues why it should be happening?

Answer 1

Try just using the OleDbCommand object with the generated SQL and iterating through what is returned using OleDbDataReader.

Then try with this SQL string

SELECT [t0].[ProductID] AS [ID], [t0].[ProductName] AS [Name], [t0].[Active] 
FROM 
[Products] AS [t0] 
WHERE [t0].[Active] = YES

I believe it may have something to do with the underlying values used for the Yes/No datatype in Jet database.

Yes/No

A Logical field can be displayed as Yes/No, True/False, or On/Off. In code, use the constants True and False, equivalent to -1 and 0 respectively.

Have a look at BLS site under Developer Community- There is source code for Linq to Access solution

Answer 2

As Russ noted it looks like a problem with different representation of booleans.

Try changing the "Where p.active" into "Where p.active <> 0"

Answer 3

Look and see what the productTable.active field contain the Access database, I bet it's probably not 0 or 1. It might be -1.

What you might want is something like this:

var allProducts = from p in productTable
                  where p.Active = -1
                  select p;

Update:
(
O.P. tells me this won't work in linq/C#; but there must be a way to do the equivalent of the following SQL in linq

Select * 
from Customers
where active = -1

)

Failing that (e.g., it shows up as yes/no | true/false) try reading the value of Active in and output it through a msgbox just to see what it really contains.