null-coalescing operator bug

Posts   
 
    
Posts: 1251
Joined: 10-Mar-2006
# Posted on: 06-Nov-2015 15:24:25   

I have a view that is mapped onto an entity. It has a nullable bit field in MS SQL. That translates to a bool field in .net.

When I do a query like the following, the bad SQL is generated:


            var query = (from someTable in metadata.SomeTable
                         join theView in metadata.ViewInQuestion on someTable.SomeTablePkey equals theView.SomeTablePkey
                         select new
                         {
                             SomeBool = theView.SomeBool ?? false,
                             someTable.SomeTablePkey
                         });


If I change the query to this, it works:


            var query = (from someTable in metadata.SomeTable
                         join theView in metadata.ViewInQuestion on someTable.SomeTablePkey equals theView.SomeTablePkey
                         select new
                         {
                             SomeBool = theView.SomeBool == null ? false : theView.SomeBool.Value,
                             someTable.SomeTablePkey
                         });


In the failure code, the SQL case statement that is generated fails to compare the bit to a 1/0.

We recently upgraded our LLBLGen library so I am not entirely sure what version this was against as it was a week or two ago when I found the bug and am just not getting around to reporting it. I think it was SqlServer 4.2.20151009, OrmSupport 4.2.20150210. Self servicing. Should be a pretty easy repo. (I didn't spend a ton of time researching - may not be related to the view, join, etc. As you see, I found a quick and easy workaround and moved along...but thought I would report it for next time. simple_smile )

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 09-Nov-2015 06:00:44   

Hi Wayne,

  • What is the generated SQL for that Linq code?
  • Does this happen if the entity is not based on a view?
David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Nov-2015 14:08:00   

Reproduced.


[Test]
public void CoalesceOnNullBoolTest()
{
    using(var adapter = new DataAccessAdapter())
    {
        var metaData = new LinqMetaData(adapter);
        var q = from c in metaData.Category
                join p in metaData.Product on c.CategoryId equals p.CategoryId
                select new
                {
                    Preferred = p.PreferredProduct ?? false,
                    Discontinued = p.Discontinued,
                    c.CategoryId
                };
        var results = q.ToList();
        foreach(var v in results)
        {
            if(v.Discontinued)
            {
                Assert.IsTrue(v.Preferred);
            }
            else
            {
                Assert.IsFalse(v.Preferred);
            }
        }
    }
}

Query:


SELECT CASE
         WHEN COALESCE([LPA_L2].[PreferredProduct],
                       0 /* @p2 */) THEN 1
         ELSE 0
       END                 AS [Preferred],
       [LPA_L2].[Discontinued],
       [LPA_L1].[CategoryID] AS [CategoryId]
FROM   ( [Northwind].[dbo].[Categories] [LPA_L1]
         INNER JOIN [Northwind].[dbo].[Products] [LPA_L2]
             ON [LPA_L1].[CategoryID] = [LPA_L2].[CategoryID])

should have been:


SELECT CASE
         WHEN COALESCE([LPA_L2].[PreferredProduct],
                       0 /* @p2 */)=1 THEN 1
         ELSE 0
       END                 AS [Preferred],
       [LPA_L2].[Discontinued],
       [LPA_L1].[CategoryID] AS [CategoryId]
FROM   ( [Northwind].[dbo].[Categories] [LPA_L1]
         INNER JOIN [Northwind].[dbo].[Products] [LPA_L2]
             ON [LPA_L1].[CategoryID] = [LPA_L2].[CategoryID])

Or even better: without the CASE at all (just the COALESCE).

Looking into it.

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Nov-2015 15:49:45   

Fixed in next build. It was surprisingly hard to do!

Frans Bouma | Lead developer LLBLGen Pro