C# – Boolean column in Microsoft Access and filtering data using linq

c++filterlinqms-accessms-access-2007

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

Best Solution

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

Related Question