Boolean column in Microsoft Access and filtering data using linq

[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?

From stackoverflow

• 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

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

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

  Hemant : Active property of p is boolean. You cannot write that statement!

• 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.

  Hemant : Hey brother no offense but 1. Equal sign is an assignment and cannot be used for comparisons. 2. As you can see from class definition, Active property is boolean and you cannot compare it with integer.

  CodeSlave : No offense taken. I'm not a linq/c# programmer by trade (but spent a lot of years in C, so I see your point - pun intended). See my update for my other thoughts on this.