MySql, Entity Framework, Inner Joins with null checks generated

Question

Hi,

I have had a play with the Entity Framework and the MySQL driver. Excuse me if this is a silly question. Table adverts has a FK to the PK of vacancies. Both fields are marked as NOT NULL. I am performing a simple join:

 var qry = (from vacancy in context.vacancies
                       join advert in context.adverts on vacancy.id equals advert.vacancies.id
                       where advert.status == 1
                       select
                         new
                             {
                                 Id = advert.id
                             })
                            ;

This generates the following SQL:

SELECT 1 AS C1, Extent2.id FROM vacancies AS Extent1 
    INNER JOIN adverts AS Extent2 ON (Extent1.id = Extent2.vacancy_id) OR ((Extent1.id IS  NULL) AND (Extent2.vacancy_id IS  NULL))
    WHERE 1 = Extent2.status

Why is the EF generating "IS NULL" conditions for the join, when the columns are not marked as nullable?

Thanks,

Bob

Answer 1

1. It's the provider, not the EF, which generates SQL.
2. The query is wrong anyway. It's generally wrong to use join, and all this join does is restrict the results to adverts with a vacancy, which you say is non-nullable anyway.