空合并在LINQ查询中不起作用 [英] Null coalesce not working in LINQ query

问题描述

为此:

int? item1 = null;
int? item2 = null;

someObjectList.Where(x => x.SomeItem1 == (item1 ?? x.SomeItem1)
                       && x.SomeItem2 == (item2 ?? x.SomeItem2) 
                    );

列表中所有对象中 someObjectList 不为空并且 SomeItem1 和 SomeItem2 为 null 的地方.

Where someObjectList is not empty and SomeItem1 and SomeItem2 is null in all the objects in the list.

为什么什么都不返回?

我的代码:

public void GetPlacementsByMaterial(long clientMaterialID)
{
    ClientMaterial clientMaterial = ((ApplicationEntityModel)NavigationItem.ObjectContext).ClientMaterial.FirstOrDefault(x => x.ClientMaterialID == clientMaterialID);

    var list = GetPlacementList(supplier, mediaSpace);

    PlacementsList = list.Where(x => x.MediaCategoryFormatID == (clientMaterial.MediaCategoryFormatID ?? x.MediaCategoryFormatID)
                                                && x.MediaCategorySizeID == (clientMaterial.MediaCategorySizeID ?? x.MediaCategorySizeID) 
                             );
}

所有ID均为 Nullable< long> .

SQL事件探查器:

SELECT *
  FROM [dbo].[CampaignSchedulePlacements] AS [Extent5]
WHERE ([Extent5].[MediaCategoryFormatID] = [Extent5].[MediaCategoryFormatID]) AND ([Extent5].[MediaCategorySizeID] = [Extent5].[MediaCategorySizeID])

注意:清理了SQL.

推荐答案

在SQL中，NULL不等于NULL.

In SQL, NULL is not equal to NULL.

您可以将NULL解释为含义:有价值，但我不知道它是什么".因此，如果您要比较两个NULL值，您实际上是在问第一个未知值等于第二个未知值吗?"当然，没有理由假设它们是，所以SQL会说"no".

You can interpret NULL as meaning: "there is value, but I don't know what it is". So if you're comparing two NULL values, you're really asking "is the first unknown value equal to the second unknown value?" Of course, there is no reason to assume they are, so SQL will say "no".

我认为这是引起您问题的原因.您可以通过查看生成的实际SQL来验证.如果使用SQL =运算符，则确实是问题所在.您可以通过在数据库工具(例如SQL Management Studio)中运行SQL来进行验证，以防您使用的是SQL Server.

I am assuming that this is causing your problem. You can verify that by looking at the actual SQL produced. If it's using the SQL = operator, this is indeed the problem. You can verify that by running the SQL in a database tool, such as SQL Management Studio in case you're using SQL Server.

更新:

条件

([Extent5].[MediaCategoryFormatID] = [Extent5].[MediaCategoryFormatID]) 

当[Extent5]确实会返回false.[MediaCategoryFormatID]为NULL.

will indeed return false when [Extent5].[MediaCategoryFormatID] is NULL.

这回答了问题为什么它什么也没返回?"

That answers the question "Why is it returning nothing?"

但是，另一个问题浮现在脑海:为什么实体框架会从此linq查询生成SQL?

However, another question come to mind: why would the entity framework generate that SQL from this linq query?

恐怕由于SQL生成的质量，对linq to实体的了解并不确切，这种情况似乎可以证实这一点.您可能会考虑使用Linq to SQL.即使从长远来看这似乎是一条死路，但当前的实现要比linq实体好得多.

I'm afraid that linq to entities is not exactly known for the quality of its SQL generation, and this case seems to confirm that. You might consider Linq to SQL. Even if that seems to be a dead-end track in the long run, the current implementation if a lot better than linq to entities.

无论哪种情况，您都尝试过类似的方法

In either case, have you tried something like

someObjectList.Where(x => 
    !item1.hasValue || 
    x.SomeItem1.HasValue && x.SomeItem1.Value == item1.Value)

请确保也要在事件探查器下进行验证，对实体的linq也可能使它混乱.

Make sure to verify that under the profiler as well though, linq to entities might mess it up too.

这篇关于空合并在LINQ查询中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！