title: Linq To SQL Specified cast is not valid error with Union
body: <p>I have the following code which is using Linq to SQL in .NET 4.0 agains a SQL Server 2008 R2 server:</p>
  
  <pre><code>    public void Patients()
      {
          var documents = GetEMRDocumentsByPatientId("231966");
          if (documents.Count() &gt; 0)
          {
              foreach (var doc in documents)
              {
                  Console.WriteLine(doc.PatientId);
              }
          }
      }
  
      public static IQueryable&lt;EMRDocument&gt; GetEMRDocumentsByPatientId(string inPatientId)
      {
          return GetEMRDocuments().Where(d =&gt; String.Equals(d.PatientId, inPatientId));
      }
  
      public static IQueryable&lt;EMRDocument&gt; GetEMRDocuments()
      {
          var dataContext = InitializeDataContext();
          IQueryable&lt;EMRDocument&gt; retVal = null;
  
          retVal = (from e in dataContext.EMREvaluations
                    select new EMRDocument
                    {
                        PatientId = e.PatientId,
                        IsDeleted = e.Deleted,
                    }).Union(
                   from e2 in dataContext.EMRPatientDailyNotes
                   select new EMRDocument
                   {
                       PatientId = e2.PatientID,
                       IsDeleted = false,
                   });
          return retVal;
      }
  </code></pre>
  
  <p>The start of the app calls Patients();  I receive a <strike>"Object reference not set to an instance of an object"</strike>"Specified cast is not valid" error on the foreach line in Patients() the first time in.  The documents.Count() works correctly and returns the correct number of records from the database.  I can also take the generated SQL of documents and run it in SSMS and the results return correctly.</p>
  
  <p>In the query that runs, the select, from dataContext.EMRPatientDailyNotes, returns no records, because there is no record in that table for the PatientId of 231966.</p>
  
  <p>In GetEMRDocuments(), if I comment out the IsDeleted = e.Deleted and IsDeleted = false in both selects, then the entire code below will execute without error.  If I change IsDeleted = e.Deleted to IsDeleted = false, then the code runs without error.  If I remove the entire Union and just run 
  the following...</p>
  
  <pre><code>retVal = (from e in dataContext.EMREvaluations
            select new EMRDocument
            {
                PatientId = e.PatientId,
                IsDeleted = e.Deleted,
            });
  </code></pre>
  
  <p>...then this code executes without error.  Also, e.Deleted is a bool from the database and not a bool?(nullable bool) and IsDeleted is a bool.  Has anyone seen this type of issue before?  I have no idea what to do to fix this.  Also, I was previously using Linq To Entities with this same query and did not receive this error.  Any help would be greatly appreciated.</p>
  
  <p>Thanks,
  Dan</p>
  
  <p><strong>EDIT:</strong> 
  Here's the stack trace.  I had some other code that seemed to be hiding the actual error:</p>
  
  <pre><code>[InvalidCastException: Specified cast is not valid.]
  System.Data.SqlClient.SqlBuffer.get_Boolean() +5057281
  System.Data.SqlClient.SqlDataReader.GetBoolean(Int32 i) +38
  Read_EMRDocument(ObjectMaterializer`1 ) +313
  System.Data.Linq.SqlClient.ObjectReader`2.MoveNext() +32
  ATI.TherapyConnect.Service.Patients() in C:\SVN\Application\branches\dan.cagney\ATI.TherapyConnect\Service.asmx.cs:57
  ATI.TherapyConnect.Content.UserControls.Shared.Patients.MyPatientsList.BindGrid(String inSortExpression, Int32 inCurrentPage) in C:\SVN\Application\branches\dan.cagney\ATI.TherapyConnect\Content\UserControls\Shared\Patients\MyPatientsList.ascx.cs:129
  ATI.TherapyConnect.Content.UserControls.Shared.Patients.MyPatientsList.Page_Load(Object sender, EventArgs e) in C:\SVN\Application\branches\dan.cagney\ATI.TherapyConnect\Content\UserControls\Shared\Patients\MyPatientsList.ascx.cs:68
  System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
  System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
  System.Web.UI.Control.OnLoad(EventArgs e) +91
  System.Web.UI.Control.LoadRecursive() +74
  System.Web.UI.Control.LoadRecursive() +146
  System.Web.UI.Control.LoadRecursive() +146
  System.Web.UI.Control.LoadRecursive() +146
  System.Web.UI.Control.LoadRecursive() +146
  System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2207
  </code></pre>
  
  <p>So, apparently there's a casting issue into the bool value of IsDeleted.  Could it be that it's trying to map a null value into the IsDeleted of the Union since there are no results returned from dataContext.EMRPatientDailyNotes?  I don't think this would be the issue since if I just change IsDeleted = e.Deleted to IsDeleted = false, there is no error.  So, it seems that e.Deleted is returning a non-bool value.  But how would that be possible when e.Deleted is defined as a bool from the Linq To SQL generated code and it is a bit, not null field in the SQL database?</p>