Click here to Skip to main content
11,704,107 members (48,712 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# Framework entity
Hi all,
I have a strange problem dealing with Entity Framework.
This problem never ever occured to me before, so I am kind of surprised.

It's all about the AgencyId, which is in fact null.
I verify this where I set agencyEqualsNull in the first line (it is true).


// verify that AgencyId indeed is null
bool agencyEqualsNull = AgencyId == null;
 
// here I do not get a result -; test is null
tblTest test = context.tblTest.FirstOrDefault(w => 
w.couponId == Id
&&
w.couponCode == Code
&&
w.customerId == CustomerId
&&
w.agencyId == AgencyId
);
 
if (test != null)
{
    // we never get here
}
 
// here I do get a result - test is not null
tblTest test2 = context.tblTest.FirstOrDefault(w => 
w.couponId == Id
&&
w.couponCode == Code
&&
w.customerId == CustomerId
&&
w.agencyId == null
);
 
if (test2 != null)
{
    //we get here
}

Now for the first try I receive null for test, for the second try I recieve a value for test2 (not null).
The only difference is that I do not pass the variable (which is in fact null) but null directly...

Do I overlook something? I ssumed the queries are both equal and should return a value...

// UPDATE
I fired up the SQL Server Profiler in order to enlight the situation
here is the query:
exec sp_executesql N'SELECT TOP (1) 
[Extent1].[id] AS [id], 
[Extent1].[code] AS [code], 
[Extent1].[customerId] AS [customerId], 
[Extent1].[employeeId] AS [employeeId],
FROM [dbo].[tblTest] AS [Extent1]
WHERE 
([Extent1].[Id] = @p__linq__0) AND 
([Extent1].[Code] = @p__linq__1) AND 
([Extent1].[customerId] = @p__linq__2) AND 
([Extent1].[employeeId] = @p__linq__3)',
N'@p__linq__0 bigint,
@p__linq__1 nvarchar(4000),
@p__linq__2 bigint,
@p__linq__3 bigint',
@p__linq__0=82,
@p__linq__1=N'ADVENT2012-CU88888',
@p__linq__2=88888,
@p__linq__3=NULL

In the last line you can see @p__linq__3=NULL
If I pass null directly it resolves to
([Extent1].[employeeId] IS NULL)
instead of
([Extent1].[employeeId] = @p__linq__3)
where
@p__linq__3=NULL

Now I am even more puzzled because I really assumed the generated query would be the same if I pass a variable containing null or null directly...

// UPDATE
I figured out that this will do the trick:
change
w.agencyId == AgencyId
to
(AgencyId == null) ? w.agencyId == null : w.agencyId == AgencyId


I somehow assumed that this would be done automatically...
This is actually not my code and now I am kind of afraid that I have to check everything that might be related to this problem in the complete codebase...

Does anybody has a suggestion regarding this one?


Any help is kindly appreciated,
best regards
Andy
Posted 26-Nov-12 4:32am
Edited 26-Nov-12 5:28am
v8

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I assume behavior is by design, for all null values, they need to be compared separately. or you have another option is set ansi null off on sql server which will allow you to compare Null values like AgencyID = null.

SET ANSI_NULLS OFF
GO
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Maciej Los 530
1 OriginalGriff 510
2 Sergey Alexandrovich Kryukov 421
3 Andy Lanng 294
4 F-ES Sitecore 220
0 OriginalGriff 8,744
1 Sergey Alexandrovich Kryukov 7,747
2 CPallini 5,107
3 Maciej Los 4,716
4 Mika Wendelius 3,606


Advertise | Privacy | Mobile
Web01 | 2.8.150819.1 | Last Updated 26 Nov 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100