Comparing nullable column with nullable variable causes exception.

Posts   
 
    
PatrickD
User
Posts: 65
Joined: 05-Sep-2006
# Posted on: 29-Sep-2009 13:06:06   

Hi,

Consider the following Linq query:


var count = (from cb in metaData.CountBlock
             where cb.CompanyId == Entity.CompanyId
             && (cb.SiteId == Entity.SiteId || !cb.SiteId.HasValue)
             select cb.CountBlockId).Count();

SiteId and Entity.SiteId are both of type int?. When executed this generates the following exception:


System.InvalidOperationException: The binary operator Equal is not defined for the types 'System.Nullable`1[System.Int32]' and 'System.Int32'.
   at System.Linq.Expressions.Expression.GetEqualityComparisonOperator(ExpressionType binaryType, String opName, Expression left, Expression right, Boolean liftToNull)
   at System.Linq.Expressions.Expression.Equal(Expression left, Expression right, Boolean liftToNull, MethodInfo method)
   at System.Linq.Expressions.Expression.MakeBinary(ExpressionType binaryType, Expression left, Expression right, Boolean liftToNull, MethodInfo method, LambdaExpression conversion)
   at System.Linq.Expressions.Expression.MakeBinary(ExpressionType binaryType, Expression left, Expression right, Boolean liftToNull, MethodInfo method)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleBinaryExpression(BinaryExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.PreProcessor.HandleBinaryExpression(BinaryExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleBinaryExpressionBooleanOperator(BinaryExpression expressionToHandle)
etc...

Which is strange, because both are of type int?, so where does the type int come from?

When I change my code to the following, it works:


int siteId = Entity.SiteId.GetValueOrDefault(0);
var metaData = new LinqMetaData();
var count = (from cb in metaData.CountBlock
     where cb.CompanyId == Entity.CompanyId
     && (cb.SiteId == siteId || !cb.SiteId.HasValue)
     select cb.CountBlockId).Count();

Note that I have to assign the result of GetValueOrDefault() to a variable, because when I would include the method call in the Linq query itself I get a conversion exception.

Clearly, this is not what I want because it forces me to add extra unnecessary code.

BTW, this only happens when the code (which is contained in the Business Layer) is called from an ASP.Net website. When called from my unit tests, it works correctly. Kinda strange, I know...

Kind regards,

Patrick

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 29-Sep-2009 13:25:42   

Instead of: cb.SiteId == Entity.SiteId

You may use: cb.SiteId == Entity.SiteId.Value

PatrickD
User
Posts: 65
Joined: 05-Sep-2006
# Posted on: 29-Sep-2009 13:50:45   

Walaa wrote:

Instead of: cb.SiteId == Entity.SiteId

You may use: cb.SiteId == Entity.SiteId.Value

Unfortunately not, because SiteId might be null, in which case this would raise a NullReferenceException. In my scenario a SiteId of null is valid and if both cb.SiteId and Entity.SiteId are null, I consider them equal and want them to be returned in the count as well.

I thought I could accomplish this with the following (ugly, i know) code (as I know the database will never have a value of 0 in this column):


var count = (from cb in metaData.CountBlock
     where cb.CompanyId == Entity.CompanyId
     && (cb.SiteId.GetValueOrDefault(0) == Entity.SiteId.GetValueOrDefault(0) || !cb.SiteId.HasValue)
     select cb.CountBlockId).Count();

but then I get a

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException: The binary expression '([508].GetValueOrDefault(0) = 4)' can't be converted to a predicate expression.

Of course, there are many work-arounds, like using 0 instead of null in the database, etc. But the problem is that this is the second time I encounter this error, and I rather have a good solution than creating a work-around. IMHO, it is normal practice to compare two int? values and get true when both are null. So why not in a Linq query?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 29-Sep-2009 20:52:33   

http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=12769

so first: download latest build -> retry

Frans Bouma | Lead developer LLBLGen Pro
PatrickD
User
Posts: 65
Joined: 05-Sep-2006
# Posted on: 30-Sep-2009 09:50:32   

Otis wrote:

http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=12769

so first: download latest build -> retry

I was already using the latest build (full package of August 26th), which contains the build of September 10th. You can trust me on first downloading the latest build before bothering you...disappointed

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 30-Sep-2009 10:06:09   

PatrickD wrote:

Otis wrote:

http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=12769

so first: download latest build -> retry

I was already using the latest build (full package of August 26th), which contains the build of September 10th. You can trust me on first downloading the latest build before bothering you...disappointed

Sorry, I saw August... and thought it was a year ago (I didn't look closely flushed ). I'm sure you download the latest version, but not everyone does.

I'll look into it.

(ps, I do recall fixing this bug some time ago, which made me not look closely)

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 30-Sep-2009 11:38:50   

I can't reproduce it:


[Test]
public void WhereClauseWithTwoNullableParametersInsideCount()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var order = metaData.Order.FirstOrDefault(o => o.OrderId == 10254);
        Assert.IsNotNull(order);

        var q = (from o in metaData.Order
                where o.CustomerId==o.CustomerId
                && (o.EmployeeId==o.EmployeeId || !o.EmployeeId.HasValue)
                select o.OrderId).Count();

        Assert.AreEqual(818, q);
    }
}

As you described in your startpost, you too can't reproduce it in unittests, but do see it happen in an asp.net website, be sure the linqsupportclasses and ormsupportclasses dlls in the ASP.NET site's bin folder are up to date. It's likely the site runs with outdated dll which cause the bug to occur in the website but not in unittests.

Frans Bouma | Lead developer LLBLGen Pro
PatrickD
User
Posts: 65
Joined: 05-Sep-2006
# Posted on: 30-Sep-2009 12:00:53   

Otis wrote:

I can't reproduce it:


[Test]
public void WhereClauseWithTwoNullableParametersInsideCount()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var order = metaData.Order.FirstOrDefault(o => o.OrderId == 10254);
        Assert.IsNotNull(order);

        var q = (from o in metaData.Order
                where o.CustomerId==o.CustomerId
                && (o.EmployeeId==o.EmployeeId || !o.EmployeeId.HasValue)
                select o.OrderId).Count();

        Assert.AreEqual(818, q);
    }
}

As you described in your startpost, you too can't reproduce it in unittests, but do see it happen in an asp.net website, be sure the linqsupportclasses and ormsupportclasses dlls in the ASP.NET site's bin folder are up to date. It's likely the site runs with outdated dll which cause the bug to occur in the website but not in unittests.

Unfortunately, this is not the case. As part of the deployment process, also the LLBLGen assemblies are copied to the web's bin folder. I checked this, and it makes use of the same assemblies. However, what is wrong in your test is that you compare the same columns which each other (o.CustomerId == o.CustomerId), I am comparing a property of a CountBlockEntity instance (which already fetched data). So your test should be something like:


OrderEntity entity = new OrderEntity(1);
var q = (from o in metaData.Order
    where o.CustomerId==o.CustomerId
    && (o.EmployeeId==entity.EmployeeId || !o.EmployeeId.HasValue)
    select o.OrderId).Count();

Assuming that Order.EmployeeId is nullable. Note also that I am using SelfService and not Adapter.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 30-Sep-2009 12:08:40   

oh! flushed what a blunder. I'll fix the test right away and check again. The exception occurs inside generic code which is used by both selfservicing and adapter, it doesn't matter which one you use. EmployeeId is nullable indeed

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 30-Sep-2009 12:20:04   

I now also get expected results, I already found the number 818 too high.


[Test]
public void WhereClauseWithTwoNullableParametersInsideCount()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var order = metaData.Order.FirstOrDefault(o => o.OrderId == 10254);
        Assert.IsNotNull(order);

        var q = (from o in metaData.Order
                 where o.CustomerId == order.CustomerId
                && (o.EmployeeId == order.EmployeeId || !o.EmployeeId.HasValue)
                select o.OrderId).Count();

        Assert.AreEqual(2, q);
    }
}

Works properly:


Generated Sql query: 
    Query: SELECT TOP 1 COUNT(*) AS [LPAV_] FROM (SELECT [LPLA_1].[OrderID] AS [OrderId] FROM [Northwind].[dbo].[Orders] [LPLA_1]  WHERE ( ( ( ( [LPLA_1].[CustomerID] = @CustomerId1) AND ( ( [LPLA_1].[EmployeeID] = @EmployeeId2) OR NOT ( [LPLA_1].[EmployeeID] IS NOT NULL)))))) [LPA_L1]
    Parameter: @CustomerId1 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "CHOPS".
    Parameter: @EmployeeId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3.

Selfservicing variant:


[Test]
public void WhereClauseWithTwoNullableParametersInsideCount()
{
    LinqMetaData metaData = new LinqMetaData();
    var order = metaData.Order.FirstOrDefault(o => o.OrderId == 10254);
    Assert.IsNotNull(order);

    var q = (from o in metaData.Order
             where o.CustomerId == order.CustomerId
            && (o.EmployeeId == order.EmployeeId || !o.EmployeeId.HasValue)
             select o.OrderId).Count();

    Assert.AreEqual(2, q);
}

query:


Generated Sql query: 
    Query: SELECT TOP 1 COUNT(*) AS [LPAV_] FROM (SELECT [LPLA_1].[OrderID] AS [OrderId] FROM [Northwind].[dbo].[Orders] [LPLA_1]  WHERE ( ( ( ( [LPLA_1].[CustomerID] = @CustomerId1) AND ( ( [LPLA_1].[EmployeeID] = @EmployeeId2) OR NOT ( [LPLA_1].[EmployeeID] IS NOT NULL)))))) [LPA_L1]
    Parameter: @CustomerId1 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "CHOPS".
    Parameter: @EmployeeId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 3.

You're running september 10th build, we haven't had a fix in linq after that date.

What I'd like you to do is to verify if your unittest (which succeeds as well as you described) is really the same as your asp.net code. I.o.w.: please give me a failing unittest so I can investigate from there, as I can't reproduce it with the query you described (I double checked that I have the proper query)

I also changed the CustomerId (which is a string type) field compare to another nullable value type or to EmployeeId again (all nullable) but they all succeeded.

Frans Bouma | Lead developer LLBLGen Pro
PatrickD
User
Posts: 65
Joined: 05-Sep-2006
# Posted on: 30-Sep-2009 14:18:52   

flushed It seemed that the web site on my local machine contained the new LLBLGen assemblies, but the assemblies on the test host (which are deployed from another machine) were from February 6th. As I copied the software from the test host to my local machine (to be able to debug) and then overwrote the assemblies with Debug assemblies (including LLBLGen assemblies) I never noticed this.

It all works now.

Mea Culpa...

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 30-Sep-2009 15:00:21   

great that it works without having to fix anything wink

Frans Bouma | Lead developer LLBLGen Pro
PatrickD
User
Posts: 65
Joined: 05-Sep-2006
# Posted on: 30-Sep-2009 15:02:10   

Otis wrote:

great that it works without having to fix anything wink

zucht

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 01-Oct-2009 10:04:54   

PatrickD wrote:

Otis wrote:

great that it works without having to fix anything wink

zucht

huh? Ik bedoelde het als: Ben blij dat het werkt zonder dat ik iets hoef te fixen. Geen idee waarom dat verkeerd opgevat kan worden? Ik bedoelde het iig totaal niet als iets negatiefs naar jou/jullie toe.

Frans Bouma | Lead developer LLBLGen Pro
PatrickD
User
Posts: 65
Joined: 05-Sep-2006
# Posted on: 01-Oct-2009 13:13:53   

Otis wrote:

PatrickD wrote:

Otis wrote:

great that it works without having to fix anything wink

zucht

huh? Ik bedoelde het als: Ben blij dat het werkt zonder dat ik iets hoef te fixen. Geen idee waarom dat verkeerd opgevat kan worden? Ik bedoelde het iig totaal niet als iets negatiefs naar jou/jullie toe.

Hoi Frans,

Ik denk dat je mijn reactie verkeerd hebt opgevat. Ik bedoelde hiermee dat ik eerst uitdrukkelijk zeg dat de versie van de assemblies klopte en dat dat dan uiteindelijk toch niet zo blijkt te zijn. Het was dus sarcastisch richting mijzelf bedoeld. Sorry als dat verkeerd overkwam,

Patrick

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 01-Oct-2009 13:25:45   

ooooooooooh! simple_smile Dan had ik het idd verkeerd begrepen simple_smile .

Frans Bouma | Lead developer LLBLGen Pro