LINQ to SQL problem with bit/booleans

Question

I'm having an issue with LINQ2Sql where the value in the database is not getting to my code correctly. Everything is OK except for columns defined as a bit field in the database. In my code after the LINQ query, the value for bit fields is always false. What the heck is going on?

I've already tried deleting the table from the SQL desigener and then adding it back in, but that doesn't make a difference.

I look at the table data in SQL Server Mgmt Studio, and the values for my bit columns are correct: 1 for true, 0 for false. If I edit the table in Mgmt Studio, it has True/False in the right places. If I run my query in LINQ Pad, it returns the correct values. If I go into the debugger and grab the SQL from the LINQ results, I get the correct results when I run that!

Here is my LINQ code ...

        var theplan = (from plans in _data.Plans
                       where plans.PlanId.ToString() == sId
                       select plans).FirstOrDefault();

Here are my table definitions ...

CREATE TABLE [dbo].[Things](
    [ThingId] [uniqueidentifier] NOT NULL,
    [ValidEnvelope] [bit] NOT NULL,
    [Xmax] [float] NOT NULL,
    [Xmin] [float] NOT NULL,
    [Ymax] [float] NOT NULL,
    [Ymin] [float] NOT NULL,
    [IsValid] [bit] NOT NULL,
 CONSTRAINT [PK_Things] PRIMARY KEY CLUSTERED .... etc.

CREATE TABLE [dbo].[Plans](
    [PlanId] [uniqueidentifier] NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Created] [datetime] NOT NULL,
    [Modified] [datetime] NOT NULL,
    [ThingId] [uniqueidentifier] NOT NULL,
    [ViewStateId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_plans] PRIMARY KEY CLUSTERED ... etc.

Not that the Plans.ThingId column points to Things.ThingId as FK. The problem columns are Things.ValidEnvelope and Things.IsValid.

Here is the XML from the dbml file ....

<?xml version="1.0" encoding="utf-8"?>
<Database Name="XXXX" Class="XXXXDbDataContext" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007"&gt;
  <Connection Mode="AppSettings" ConnectionString="......" .... />
  <Table Name="dbo.ViewStates" Member="ViewStates">
    <Type Name="ViewState">
      <Column Name="ViewStateId" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
      <Column Name="Xmax" Type="System.Double" DbType="Float NOT NULL" CanBeNull="false" />
      <Column Name="Xmin" Type="System.Double" DbType="Float NOT NULL" CanBeNull="false" />
      <Column Name="Ymax" Type="System.Double" DbType="Float NOT NULL" CanBeNull="false" />
      <Column Name="Ymin" Type="System.Double" DbType="Float NOT NULL" CanBeNull="false" />
      <Association Name="ViewState_Plan" Member="Plans" ThisKey="ViewStateId" OtherKey="ViewStateId" Type="Plan" />
    </Type>
  </Table>
  <Table Name="dbo.Plans" Member="Plans">
    <Type Name="Plan">
      <Column Name="PlanId" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
      <Column Name="Name" Type="System.String" DbType="VarChar(50) NOT NULL" CanBeNull="false" />
      <Column Name="Created" Type="System.DateTime" DbType="DateTime NOT NULL" CanBeNull="false" />
      <Column Name="Modified" Type="System.DateTime" DbType="DateTime NOT NULL" CanBeNull="false" />
      <Column Name="ThingId" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" CanBeNull="false" />
      <Column Name="ViewStateId" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" CanBeNull="false" />
      <Association Name="ViewState_Plan" Member="ViewState" ThisKey="ViewStateId" OtherKey="ViewStateId" Type="ViewState" IsForeignKey="true" />
      <Association Name="Thing_Plan" Member="Thing" ThisKey="ThingId" OtherKey="ThingId" Type="Thing" IsForeignKey="true" />
    </Type>
  </Table>
  <Table Name="dbo.Things" Member="Things">
    <Type Name="Thing">
      <Column Name="ThingId" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
      <Column Name="ValidEnvelope" Type="System.Boolean" DbType="Bit NOT NULL" CanBeNull="false" />
      <Column Name="Xmax" Type="System.Double" DbType="Float NOT NULL" CanBeNull="false" />
      <Column Name="Xmin" Type="System.Double" DbType="Float NOT NULL" CanBeNull="false" />
      <Column Name="Ymax" Type="System.Double" DbType="Float NOT NULL" CanBeNull="false" />
      <Column Name="Ymin" Type="System.Double" DbType="Float NOT NULL" CanBeNull="false" />
      <Column Name="IsValid" Type="System.Boolean" DbType="Bit NOT NULL" CanBeNull="false" />
      <Association Name="Thing_Plan" Member="Plans" ThisKey="ThingId" OtherKey="ThingId" Type="Plan" />
    </Type>
  </Table>
</Database>

Answer 1

Run SQL Server Profiler, check the exact query that's being generated, copy that exact query, and run it in Management Studio.

That procedure should definitely yield the same result. What I think you'll find, before even getting that far, though, is that you might, perhaps, not be seeing the query at all. It might be cached somewhere on the way, or the database you're connecting to in your project might not be the one you think it is (check which connectionstring is being used in the dbml-properties, and then check that string in the project's settings, and also check for potential overwrites in your web.config)

Answer 2

Found the problem in another part of the code. Stupid mistake.