in ASP.NET MVC 2.0 I'm doing something like the below (specifically, matching a record based on some values as well as some NULL values).
var result = dataContext.Inventory
.SingleOrDefault(x => (x.part_id == model.Part.id &&
x.location_id == transaction.to_location_id &&
x.bin_id == transaction.to_bin_id &&
x.project_id == transaction.project_id &&
x.expiration_date == inventoryToTransfer.expiration_date));
...which returns null. I know the record exists, but the generated SQL (if I turn SQL Logging on) is
SELECT [t0].[id], [t0].[part_id], etc...
FROM [dbo].[Inventory] AS [t0]
INNER JOIN [dbo].[Parts] AS [t1] ON [t1].[id] = [t0].[part_id]
WHERE ([t0].[part_id] = @p0) AND (([t0].[location_id]) = @p1) AND ([t0].[bin_id] = @p2) AND
([t0].[project_id] = @p3) AND ([t0].[expiration_date] = @p4)
-- @p0: Input BigInt (Size = 0; Prec = 0; Scale = 0) [5197]
-- @p1: Input BigInt (Size = 0; Prec = 0; Scale = 0) [57]
-- @p2: Input BigInt (Size = 0; Prec = 0; Scale = 0) [71]
-- @p3: Input BigInt (Size = 0; Prec = 0; Scale = 0) [Null]
-- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [Null]
Which in fact will return nothing as a comparison of = NULL will return no matches. What I was thinking is that this would generate IS NULL (which works great). Like this...
...
([t0].[project_id] IS NULL) AND ([t0].[expiration_date] IS NULL)
What am I missing with this? I believe I could turn on ANSI_NULLS in SQL Server but this is deprecated and there must be an easier way.
I know, I know... I shouldn't be defining uniqueness with NULL values, but is there a way around this?