Query :: invalid sql generated for query with joins where key selector is a null comparison

[maumar]

query:

from instance in ctx.ExamInstances
join answered in ctx.ExamInstanceQuestions
on true equals answered.AnswerId != null

generated query is:

SELECT [answered].[ExamInstanceQuestionId], [answered].[AnswerId], [answered].[ExamInstanceId], [answered].[QuestionId]
FROM [ExamInstances] AS [instance]
INNER JOIN [ExamInstanceQuestions] AS [answered] ON 1 = [answered].[AnswerId] IS NOT NULL

we should be converting the null check into CASE statement, like we do for projections

[smitpatel]

Ah complex join conditions.

[divega]

we should be converting the null check into CASE statement, like we do for projections

Hopefully not in this particular case 😄 The argument for ON is also a search condition, like WHERE, so we shouldn't need to convert from search condition to a Boolean value.

I think this would be a nice translation:

SELECT [answered].[ExamInstanceQuestionId], [answered].[AnswerId], [answered].[ExamInstanceId], [answered].[QuestionId]
FROM [ExamInstances] AS [instance]
INNER JOIN [ExamInstanceQuestions] AS [answered] ON [answered].[AnswerId] IS NOT NULL

And maybe we can arrive to it by first eliminating the redundant true equals?

[smitpatel]

We already have optimizer to remove redundant true equals => PredicateReductionExpressionOptimizer
I guess its just matter of running it on join conditions.

[smitpatel]

[Fact]
public virtual void Complex_join()
{
    using (var db = CreateContext())
    {
        var query = (from customer in db.Customers
                        join order in db.Orders
                            on true equals order.CustomerID != null
                        select customer
        )
            .ToList();

        Assert.Equal(
            @"SELECT [customer].[CustomerID], [customer].[Address], [customer].[City], [customer].[CompanyName], [customer].[ContactName], [customer].[ContactTitle], [customer].[Country], [customer].[Fax], [customer].[Phone], [customer].[PostalCode], [customer].[Region]
FROM [Customers] AS [customer]
INNER JOIN [Orders] AS [order] ON [order].[CustomerID] IS NOT NULL",
            Sql);
    }
}

Above test passes if ApplyOptimizations function in DefaultQuerySqlGenerator which solved #5652 is run on join condition.