1

I have the following compiled Linq query:

public static readonly Func<DBContext, Models.User, Type, ObjectType, int?, UserNotification> GetUnreadNotificationID =
    CompiledQuery.Compile((DBContext db, Models.User forUser, Type notificationType, ObjectType forObjectType, int? forObjectID) =>
        db.UserNotifications.FirstOrDefault(c =>
            c.ForUserID == forUser.ID
            && c.ForObjectTypeID == (short)forObjectType
            && c.ForObjectID == forObjectID
            && c.TypeID == (byte)notificationType
            && c.Date > forUser.NotificationsLastRead.Date));

Note the parameter int? forObjectID.

In query profiler, an example executed SQL statement would be:

exec sp_executesql N'SELECT TOP (1) 
[t0].[ID], [t0].[TypeID], [t0].[ForUserID], [t0].[Date], [t0].[ForObjectTypeID], [t0].[ForObjectID], [t0].[Count]
FROM 
[dbo].[UserNotifications] AS [t0]
WHERE
([t0].[ForUserID] = @p0) 
AND ([t0].[ForObjectTypeID] = @p1) 
AND ([t0].[ForObjectID] = @p2) 
AND ([t0].[TypeID] = @p3) 
AND ([t0].[Date] > @p4)',

N'@p0 int,@p1 int,@p2 int,@p3 int,@p4 datetime',@p0=77812,@p1=5,@p2=NULL,@p3=4,@p4='2018-01-24 13:18:44.107'

When forObjectID is null, the query does not return the expected records. If I change:

AND ([t0].[ForObjectID] = @p2)

To:

AND ([t0].[ForObjectID] IS NULL)

It does return the correct results.

  • Why is null not handled in the way I would expect it to?
  • Is there an easy fix? (I can convert the table to not accept nulls for that field and default to 0 but feels icky)
Tom Gullen
  • 61,249
  • 84
  • 283
  • 456

2 Answers2

1

If this is Linq2Sql

change
c.ForObjectID == forObjectID to Object.Equals(c.ForObjectID, forObjectID)

for it to be able to translate to is null when forObjectID is null.

Magnus
  • 45,362
  • 8
  • 80
  • 118
0

The easy fix would be to write a SQL stored procedure which does what you want - I doubt that anyone will fix LinkToSql.

Or try this instead:

((c.ForObjectID == forObjectID) || 
 (c.ForObjectId == null && forObjectId == null))