CODEWITHSUNDEEP
entity-frameworkentity-framework-core
user1077685
user1077685

Reputation:

EF 7 - Navigation properties - incorrect SQL?

Does EF7 fully support navigation properties and custom projection? Or maybe I'm misunderstanding how to construct this query. The Study entity has a nullable ProjectId and corresponding virtual Project navigation property. The Project entity has a non-nullable CategoryId and Category navigation property. The entities were reverse scaffolded using the ef command.

If I run the following query:

return _context.Study
            .Include(s => s.Project)
            .ThenInclude(p => p.Category)
            .Select(s => new Models.StudySearchResult
            {
                StudyId = s.StudyId,
                MasterStudyId = s.MasterStudyId,
                ShortTitle = s.ShortTitle,
                Category = s.Project == null ? string.Empty : s.Project.Category.CategoryDesc,
                SubmitterId = s.SubmitterId
            }).ToList();

EF7 incorrectly generates the following SQL, which uses INNER JOIN instead of LEFT JOIN:

SELECT [s].[StudyId]
    ,[s].[MasterStudyId]
    ,[s].[ShortTitle]
    ,CASE WHEN [s].[ProjectId] IS NULL THEN @__Empty_0 ELSE [s.Project.Category].[CategoryDesc] END
    ,[s].[SubmitterId]
FROM [Study] AS [s]
INNER JOIN [Project] AS [s.Project]
    ON [s].[ProjectId] = [s.Project].[ProjectId]
INNER JOIN [Category] AS [s.Project.Category]
    ON [s.Project].[CategoryId] = [s.Project.Category].[CategoryId]

Upvotes: 2

Views: 536

Answers (1)

Igor
Igor

Reputation: 381

I have the same problem. And I found out that there is currently open issue in EF7 for generating SQL for optional navigations. It will be fixed in RC2.

https://github.com/aspnet/EntityFramework/issues/4205 https://github.com/aspnet/EntityFramework/issues/3186

Upvotes: 2

Related Questions