Odd behavior in LINQ to SQL with anonymous objects and constant columns
During the enumeration in the together.ToList() constructor, we try to move to the next element in the deferred query, that is now resolved.
MoveNext is going to create some object from the database results.The database query is turned into a DataReader and a row is extracted from the DataReader.Now get_Boolean is implemented in a way that it does a VerifyType of the object and casts an exception if it is not valid.
What you are missing to show in your question is the SqlText of together's query (as well as the _sqlText of your ctx.TransferJobs), so I'm forced to make a reasonable assumption.
TRUE is converted to 1 and FALSE is converted to 0. Converting to bit promotes any nonzero value to 1.
The Linq to Sql Data Source will trasform the Select for a true parameter in something like
([table].[column] = 1)and for a false parameter in
NOT ([table].[column] = 1)So - when your first filter is not based on a true boolean condition - the above line of code is where a cast exception might come into play if the Linq Provider is getting an object that is not 0 (or what the false boolean corresponds to), my guess is a null.
-- footnote --
A helper to log the actual sql under a Linq query (besides the Log Property, of course)
Debug.WriteLine(together.ToString());(or the GetQueryText(query) as described in the debugging support)
UPDATE
After having seen the SQL, a working fix is simply to map the bit field as int like below, using the DbType Property
[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_IsFromAutoRebalance", DbType="INT NOT NULL")] public bool IsFromAutoRebalance { get { return this._IsFromAutoRebalance; }Related (old) VS feedback link where the bug was closed as Won't Fix with a suggested workaround
This is a L2S bug. This is clear from the following facts:
- It is a crash in L2S internal code. It is not a controlled/expected exception.
- This should just work.
- Random changes to the query make the crash disappear.
Modify the query in random ways until it happens to work. You already have a good workaround. Leave a C# comment to document that this query depends on a workaround to a L2S bug.
I have found probably a dozen L2S bugs over the years (when issuing unusual or complicated queries). The product is abandoned so eventually we'll all have to switch to EF. I'm reading the EF commit logs and they have query translation bugs as well.
What's actually happening under the covers?
I cannot answer that without much investigation. It is possible to debug the L2S source code but that is a lot of work. This question is for reasons of curiosity only since you already have a workaround to this bug.
How could I go about proving it to myself?
Proving that it is a bug? I have given some reasons above.
it looks like the programmers of LINQ to SQL were considering performance and are not actually having the generated SQL pull the constant value in the case with the explicit setting of true in the withConstant version.
This does not seem plausible to me. If this was true I'd expect all pulled objects to have a value of true. I would not expect an invalid cast if that column isn't even pulled from the database as you suggest. I think this is a query translation bug.
An idea for another workaround would be:
IsAuto = x.IsFromAutoRebalance == x.IsFromAutoRebalanceNow this is no longer a constant but will always be true at runtime. The SQL Server query optimizer is capable of simplifying this code to 1. Hopefully, L2S will no longer perform the broken rewrite.
Update:
From the T-SQL code you posted the bug is apparent. The parameter @p0 is an int, not a bool. This causes the resulting column to promote to int according to the rules. It is int in both cases. Apparently, in one of the cases L2S tries to fetch it as a bool, in the other as an int. Fetching it as a bool doesn't work and crashes. So another workaround is converting the query to use ints (e.g. x.IsFromAutoRebalance ? 1 : 0 and 1).