LINQ to SQL join generates SQL which joins on IS NULL

Question

I am not good at Linq expression, today I am running into one weird issue as the below of inner join statement,

var orders = (from q in dao.CurrentDBContext.New_OrderForm 
              join d in dao.CurrentDBContext.New_OrderGoodsDetail on q.billNum equals d.billNum
              select new
             {    
                q.billNum,
                q.orderSource,
                q.sourceOddNum    
                d.PPT    
             }

While I traced the linq statement, I am confused of that Entity Framework will convert the linq statement to the below sql statment

SELECT 
[Extent1].[billNum] AS [billNum], 
[Extent1].[orderSource] AS [orderSource], 
[Extent1].[sourceOddNum] AS [sourceOddNum], 
[Extent2].[PPT] AS [PPT]
FROM [dbo].[New_OrderForm] AS [Extent1]
INNER JOIN [dbo].[New_OrderGoodsDetail] AS [Extent2] 
           ON ([Extent1].[billNum] = [Extent2].[billNum]) OR 
              (([Extent1].[billNum] IS NULL) AND ([Extent2].[billNum] IS NULL))

Do you know why the below SQL segment did automatically append?

OR (([Extent1].[billNum] IS NULL) AND ([Extent2].[billNum] IS NULL)"

I don't expect that the above automatically append, since it did slow down SQL performance. Any suggestions?

Answer 1

Here is what you can do in case you cannot change the billNum columns to be non nullable.

First, set the option mentioned by @Giorgi

class CurrentDBContext
{
    public CurrentDBContext()
    {
        Configuration.UseDatabaseNullSemantics = true;
        // ...
    }
}

Then change the LINQ query to not use join, but simple where like this

var orders = (from q in dao.CurrentDBContext.New_OrderForm 
              from d in dao.CurrentDBContext.New_OrderGoodsDetail
              where q.billNum == d.billNum
              select ...

The result will be the exact SQL query as the one you've shown (with JOIN!) without the OR part.

Answer 2

It seems that Linq translates q.billNum equals d.billNum is such a way that it also includes a valid match in case both q.billNum and d.billNum are NULL (in SQL NULL is never equal to NULL, hence the OR in your query).

Making both fields non-nullable would be the best solution, provided both fields can never be NULL.

If this is not the case, you could also try to add a where clause in your Linq statement to specifiy that both q.billNum and d.billNum cannot be NULL. With any luck, Linq will recognize that nullable values are not possible.

Note: If you are working with Oracle you should check for empty strings as well as NULL (empty string is equivalent to NULL). Empty strings should be fine as a valid value in SQL Server.

As the above did not help, you could try to write the query yourself. If I'm not mistaking it would be something along the following lines (assuming var is an List<Order> in your example code - the results of your query should match the class you are using):

StringBuilder query = new StringBuilder();
query.AppendLine("SELECT [Extent1].[billNum] AS [billNum],");
query.AppendLine("       [Extent1].[orderSource] AS [orderSource],");
query.AppendLine("       [Extent1].[sourceOddNum] AS [sourceOddNum],");
query.AppendLine("       [Extent2].[PPT] AS [PPT]");
query.AppendLine("FROM [dbo].[New_OrderForm] AS [Extent1]");
query.AppendLine("INNER JOIN [dbo].[New_OrderGoodsDetail] AS [Extent2] ON [Extent1].[billNum] = [Extent2].[billNum]");

List<Order> orders = DbContext.Database.SqlQuery<Order>(query.ToString()).ToList();

I have used similar workarounds to get around performance issues in the past.

Answer 3

If you are using EF6 try setting

context.Configuration.UseDatabaseNullSemantics = true;

and it will not generate NULL checks for those columns.

According to documentation

For example (operand1 == operand2) will be translated as: (operand1 = operand2) if UseDatabaseNullSemantics is true, respectively (((operand1 = operand2) AND (NOT (operand1 IS NULL OR operand2 IS NULL))) OR ((operand1 IS NULL) AND (operand2 IS NULL))) if UseDatabaseNullSemantics is false.

Answer 4

Following on from @Giorgi's answer, the UseDatabaseNullSemantics flag will not work with the equals keyword - only the == operand. Thus in order to get round this and ensure the join on billNum is not part of the OR clause this approach should work (in conjunction with the UseDatabaseNullSemantics flag):

var orders = (from q in dao.CurrentDBContext.New_OrderForm 
          from d in dao.CurrentDBContext.New_OrderGoodsDetail 
          where q.billNum == d.billNum
          select new
         {    
            q.billNum,
            q.orderSource,
            q.sourceOddNum    
            d.PPT    
         }

This will generate the JOIN without the OR.