Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

RC2. Bad SQL strategy when LINQ lambda where clause contains references hierarchy #5457

Closed
AR1ES opened this issue May 21, 2016 · 1 comment
Assignees
Labels
closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. type-bug
Milestone

Comments

@AR1ES
Copy link

AR1ES commented May 21, 2016

I have a three models with one-to-one relationship. I need to use these relations in where clause. But instead of a single query EF executes multiple SQL statements.
Models (only relations):

    public class PlayerModel {
        public virtual PlayerOnlineModel PlayerOnline { get; set; }
    }

    public class PlayerOnlineModel {
        public virtual PlayerModel Player { get; set; }
    }

    public class AdminModel {
        public virtual PlayerModel Player { get; set; }
    }

My LINQ expression:

    var players = await _db.Admins
        .Where(p => p.Player != null && p.Player.PlayerOnline != null)
        .ToListAsync();

I expect SQL like this:

SELECT XXX
FROM [Admins] AS [p]
LEFT JOIN [Players] AS [p.Player] ON [p].[PlayerId] = [p.Player].[Id]
LEFT JOIN [PlayersOnline] AS [p.Player.PlayerOnline] ON [p.Player.PlayerOnline].[Id] = [p.Player].[Id]
WHERE [p.Player].[Id] IS NOT NULL AND [p.Player.PlayerOnline].[Id] IS NOT NULL

Or like this:

SELECT XXX
FROM [Admins] AS [p]
JOIN [Players] AS [p.Player] ON [p].[PlayerId] = [p.Player].[Id]
JOIN [PlayersOnline] AS [p.Player.PlayerOnline] ON [p.Player.PlayerOnline].[Id] = [p.Player].[Id]

But real result contains two SQL statements:

SELECT XXX
FROM [PlayersOnline] AS [p.Player.PlayerOnline]


SELECT XXX
FROM [Admins] AS [p]
LEFT JOIN [Players] AS [p.Player] ON [p].[PlayerId] = [p.Player].[Id]
WHERE [p].[PlayerId] IS NOT NULL
@maumar
Copy link
Contributor

maumar commented May 23, 2016

duplicate of #4588

@maumar maumar closed this as completed May 23, 2016
@ajcvickers ajcvickers added the closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. label Oct 15, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. type-bug
Projects
None yet
Development

No branches or pull requests

4 participants