score:3

Accepted answer

The generated sql includes the following line, where @p4 corresponds to the Blahblahblah=false line in your projection:

DECLARE @p4 Int = 0

And the int that is returned from the query can't be converted to a bool. I don't know whether or not this is a linq to sql bug (seems like it), but there is a workaround. Basically you need to drop the Blahblahblah=false from the anonymous type projected, then .ToList() or .ToArray() the result, and finally add the bool field in a linq to objects projection:

var one =
    (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,
        Blahblahblah = cs.Blahblahblah
    }).ToList();

var two =
    (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
    }).ToList();

var three = 
    from x in two
    select new
    {
        x.CHARTID,
        x.POSITION,
        x.QUERYID,
        x.Otherblah,
        Blahblahblah = false
    };

var four = one.Union(three).Distinct();

Note that this results in two sql queries, not one.

EDIT

Also, Distinct() can be left out, since union doesn't include duplicates. I should have actually read the code that I copied and pasted!


Related Articles