Click here to Skip to main content
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 450
1 OriginalGriff 403
2 CPallini 189
3 Sergey Alexandrovich Kryukov 189
4 CHill60 180
0 OriginalGriff 6,092
1 Sergey Alexandrovich Kryukov 4,958
2 Maciej Los 3,269
3 Peter Leow 3,129
4 DamithSL 2,490


Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 26 Nov 2012
Copyright © CodeProject, 1999-2014
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