LINQ编译查询处理可空int异常：Null匹配失效原因及修复方案

Let's break down why this is happening and how to fix it without messing with your database schema.

The Core Problem #

In SQL, NULL represents an unknown value—so comparing anything to NULL using the = operator will never return true. Your compiled LINQ query is generating [t0].[ForObjectID] = @p2 even when @p2 is NULL, which doesn't match rows where ForObjectID is actually NULL. That's why swapping it to IS NULL manually works—it's the correct syntax for checking null values in SQL.

Compiled queries are tricky here because they pre-generate their SQL structure at compile time. Unlike regular LINQ queries, they can't dynamically switch between = and IS NULL based on whether the parameter is null when the query runs.

Quick, Clean Fixes #

Here are a couple of straightforward solutions:

1. Explicitly Handle Null in the LINQ Predicate

Update your query to check if both the parameter and column are null, or if they match when non-null. This tells LINQ to generate the right SQL for both cases:

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 
            && (forObjectID == null ? c.ForObjectID == null : c.ForObjectID == forObjectID) 
            && c.TypeID == (byte)notificationType 
            && c.Date > forUser.NotificationsLastRead.Date));

When forObjectID is null, this will generate [t0].[ForObjectID] IS NULL in the SQL; when it has a value, it'll use = @p2 as expected.

2. Use Object.Equals for Null-Safe Comparison

Many versions of LINQ to SQL recognize Object.Equals as a way to handle nulls correctly. Replace your c.ForObjectID == forObjectID line with:

&& Object.Equals(c.ForObjectID, forObjectID)

This should automatically generate SQL that checks for equality and null matches, no conditional needed.

3. Check Your Library Version

If you're using an older version of LINQ to SQL (or Entity Framework), upgrading might resolve this automatically—newer versions have better null handling in compiled queries. But the above fixes work reliably across most versions.

Why Your Instinct to Avoid Default 0 Is Correct #

You're right to skip setting a default 0 for ForObjectID—that's a hack that can cause problems down the line. NULL properly represents "no associated object", while 0 could end up being a valid object ID later, leading to confusing bugs. Fixing the query is the maintainable, correct approach.

内容的提问来源于stack exchange，提问作者Tom Gullen