none
Specfied Cast is Invalid for BIT column in UNION RRS feed

  • Question

  • Hi

    I have a linq UNION query below that throws an error "Specified Cast is invalid". See here for the create table scripts and steps I have taken so far. If I change the BIT column "Blahblahblah" to a VARCHAR then it works. Is this a bug??? I am using .Net Framework 4.

    ((from ch in TEST_CHARTs
     join a in TEST_CHART_SERIES on ch.CHARTID equals a.CHARTID into a_join
     from cs in a_join.DefaultIfEmpty()
     join ycols in TEST_QUERY_COLS on new { key1 = cs.YAXIS, key2 = ch.QUERYID } equals new { key1 = ycols.COLNAME, key2 = ycols.QUERYID }
     where ch.CHARTID == 1
     select
     new
     {
       ch.CHARTID,
       POSITION = 0,
       ycols.QUERYID,
       ycols.Otherblah,
       cs.Blahblahblah
     })
     .Union(from ch in TEST_CHARTs
     join xcol in TEST_QUERY_COLS on new { key1 = ch.XAXIS, key2 = ch.QUERYID } equals new { key1 = xcol.COLNAME, key2 = xcol.QUERYID }
     where ch.CHARTID == 1
     select
     new
     {
       ch.CHARTID,
       POSITION = 0,
       xcol.QUERYID,
       xcol.Otherblah,
       Blahblahblah = false
     }))

    • Edited by GoTheGoat Thursday, March 31, 2011 1:09 AM fixed crappy auto-reformatting of code block from last edit
    Friday, March 11, 2011 12:49 AM

Answers

All replies

  • When you say this works, you mean it compile or that it gives you the correct result?

    Regards

    Friday, March 11, 2011 6:41 PM
  • It compiles and returns the correct result except that the Blahblahblah field is a string instead of boolean. I can workaround it, but it seems like a bug to me.
    Saturday, March 12, 2011 7:58 AM
  • My bet is that something has gone awry in the creation of the TSQL, can you check the generated TSQL (using Log perhaps)?

    Regards

    Monday, March 14, 2011 8:53 PM
  • My bet is that something has gone awry in the creation of the TSQL, can you check the generated TSQL (using Log perhaps)?

    Regards

    Monday, March 14, 2011 8:53 PM
  • Yes the generated SQL has int parameters for some reason as discovered in the stackoverflow link in the original post. Here is the generated SQL below. The generated SQL runs fine in SSMS. Definitely seems like a LINQ bug to me...

    DECLARE @p0 Int = 1
    DECLARE @p1 Int = 0
    DECLARE @p2 Int = 1
    DECLARE @p3 Int = 0
    DECLARE @p4 Int = 0
    
    SELECT [t5].[CHARTID], [t5].[value] AS [POSITION], [t5].[QUERYID], [t5].[otherblah] AS [Otherblah], [t5].[value2] AS [Blahblahblah]
    FROM (
      SELECT [t0].[CHARTID], @p1 AS [value], [t2].[QUERYID], [t2].[otherblah], [t1].[blahblahblah] AS [value2]
      FROM [TEST_CHART] AS [t0]
      LEFT OUTER JOIN [TEST_CHART_SERIES] AS [t1] ON [t0].[CHARTID] = [t1].[CHARTID]
      INNER JOIN [TEST_QUERY_COLS] AS [t2] ON ([t1].[YAXIS] = [t2].[COLNAME]) AND ([t0].[QUERYID] = [t2].[QUERYID])
      WHERE [t0].[CHARTID] = @p0
      UNION
      SELECT [t3].[CHARTID], @p3 AS [value], [t4].[QUERYID], [t4].[otherblah], @p4 AS [value2]
      FROM [TEST_CHART] AS [t3]
      INNER JOIN [TEST_QUERY_COLS] AS [t4] ON ([t3].[XAXIS] = [t4].[COLNAME]) AND ([t3].[QUERYID] = [t4].[QUERYID])
      WHERE [t3].[CHARTID] = @p2
      ) AS [t5]
    

     

     

    Tuesday, March 15, 2011 12:04 AM
  • Try to break that query into smaller queries to see if this happens and where exactly.  Sometimes LINQ or the programmer gets confused when the expresion is too complex.

    Regards

    Tuesday, March 15, 2011 4:42 PM
  • Hi Serguey

    That is how I solved the problem as you can see in the stackoverflow link in the original post.

    What I am asking is: is this a LINQ bug?
    Tuesday, March 15, 2011 11:30 PM
  • OK I've decided to file it as a bug anyway. Here it is if you're interested: https://connect.microsoft.com/VisualStudio/feedback/details/651648/linq-to-sql-union-query-with-bit-column-gives-specified-cast-is-not-valid-error

    Update: It is a bug and they won't fix it :P

    • Proposed as answer by Jackie-SunModerator Wednesday, March 30, 2011 8:03 AM
    • Marked as answer by GoTheGoat Thursday, March 31, 2011 1:10 AM
    • Edited by GoTheGoat Thursday, March 31, 2011 1:11 AM updated information
    Wednesday, March 16, 2011 1:48 AM
  • Hi Serguey

    That is how I solved the problem as you can see in the stackoverflow link in the original post.

    What I am asking is: is this a LINQ bug?


    Yes, well at least is something that happens when the parser tries to translate an overly complex expression, that is why I told you to break it into smaller queries.  As this has been brough up several times in sure they are aware of the general issue but I'll take time and probably a version upgrade to fix this.  The other problem with the parser is that sometimes it won't produce the optimal TSQL, there are way around that as well.

    I hope they fix this quickly.

    Regards

    Wednesday, March 16, 2011 1:22 PM