Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> null-coalescing operator bug
 

Pages: 1
Bugs & Issues
null-coalescing operator bug
Page:1/1 

  Print all messages in this thread  
Poster Message
WayneBrantley
User



Location:
USA
Joined on:
10-Mar-2006 16:20:08
Posted:
1112 posts
# 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:
Code:

            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:

Code:

            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. Regular Smiley)


SelfServicing, .Net 4.5, Web Applications, SqlServer 2014  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8076 posts
# 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'ing (articles and code snippets) | linkedin | twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37561 posts
# Posted on: 09-Nov-2015 14:08:00.  
Reproduced.
Code:

[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:
Code:

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:
Code:

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
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37561 posts
# Posted on: 09-Nov-2015 15:49:45.  
Fixed in next build. It was surprisingly hard to do!

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.