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

Query: Recognize additional join patterns #3263

Closed
maumar opened this issue Sep 29, 2015 · 3 comments
Closed

Query: Recognize additional join patterns #3263

maumar opened this issue Sep 29, 2015 · 3 comments
Assignees
Labels
closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. type-enhancement
Milestone

Comments

@maumar
Copy link
Contributor

maumar commented Sep 29, 2015

In EF6 we support easy patterns to create join queries:

var query = from sna in ctx.Sna
            from foo in ctx.Foo.Where(foo => foo.Id == sna.Id)
            select new { sna, foo };

translates to:

SELECT
    [Extent1].[Id] AS [Id],
    [Extent2].[Id] AS [Id1]
    FROM  [dbo].[Sna] AS [Extent1]
    INNER JOIN [dbo].[Foo] AS [Extent2] ON [Extent2].[Id] = [Extent1].[Id]
var query = from sna in ctx.Sna
            from foo in ctx.Foo.Where(foo => foo.Id == sna.Id).DefaultIfEmpty()
            select new { sna, foo };

translates to:

SELECT
    [Extent1].[Id] AS [Id],
    [Extent2].[Id] AS [Id1]
    FROM  [dbo].[Sna] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Foo] AS [Extent2] ON [Extent2].[Id] = [Extent1].[Id]

This also allows making joins using different operators than equal:

var query = from sna in ctx.Sna
            from foo in ctx.Foo.Where(foo => foo.Id != sna.Id).DefaultIfEmpty()
            select new { sna, foo };

translates to:

SELECT
    [Extent1].[Id] AS [Id],
    [Extent2].[Id] AS [Id1]
    FROM  [dbo].[Sna] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Foo] AS [Extent2] ON [Extent2].[Id] <> [Extent1].[Id]

We should consider those for EF Core as well.

@MovGP0
Copy link

MovGP0 commented Sep 10, 2018

There is an additional pattern. Consider:

from foo in Context.Foos
join bar in Context.Bars on foo.BarId equals bar.BarId into fooBars
from fooBar in fooBars.DefaultIfEmpty()
select new { fooBar.FooId, fooBar.BarId }
SELECT [t2].[value] AS [BarId], [t2].[value2] AS [FooId]
FROM (
    SELECT [t1].[BarId] AS [value], [t1].[FooId] AS [value2]
    FROM [Foo] AS [t0]
    LEFT OUTER JOIN [Bar] AS [t1] ON ([t0].[FooId]) = [t1].[FooId]
    ) AS [t2]

vs.

from foo in Context.Foos
from bar in Context.Bars.DefaultIfEmpty()
select new { foo.FooId, bar.BarId }
SELECT [t2].[value] AS [FooId], [t2].[FooId]
FROM (
    SELECT [t1].[FooId] AS [value], [t0].[FooId]
    FROM [Foo] AS [t0]
    LEFT OUTER JOIN [Bar] AS [t1] ON 1=1 
    ) AS [t2]

@smitpatel
Copy link
Contributor

We already translate patterns to generate join in 3.0. We don't process non-equal conditions yet. Filed #18871 to track it. Closing this as fixed in 3.0

@smitpatel smitpatel modified the milestones: Backlog, 3.0.0 Nov 12, 2019
@smitpatel smitpatel self-assigned this Nov 12, 2019
@smitpatel smitpatel added the closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. label Nov 12, 2019
@smitpatel
Copy link
Contributor

Fixed in #17077

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-enhancement
Projects
None yet
Development

No branches or pull requests

4 participants