in ASP.NET MVC 2.0 I am doing something like below (in particular, 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 enable SQL Logging) 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]
Actually it will not return anything, since comparison = NULL will not return matches. I thought this would create IS NULL (which works fine). Like this...
...
([t0].[project_id] IS NULL) AND ([t0].[expiration_date] IS NULL)
What am I missing with this? I believe that I can enable ANSI_NULLS in SQL Server, but this is deprecated and should be an easier way.
I know, I know ... I should not define uniqueness with NULL values, but is there any way around this?