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

Allow expression row value comparisons via extension methods #2349

Closed
roji opened this issue May 4, 2022 · 2 comments · Fixed by #2350
Closed

Allow expression row value comparisons via extension methods #2349

roji opened this issue May 4, 2022 · 2 comments · Fixed by #2350
Assignees
Labels
enhancement New feature or request
Milestone

Comments

@roji
Copy link
Member

roji commented May 4, 2022

See dotnet/efcore#26822 - this implements that but for PG only.

Most databases allow comparing row value expressions:

WHERE (a, b) > (3, 4)
-- Equivalent to:
WHERE a > 3 OR (a = 3 AND b > 4)

This is is notably useful for keyset pagination with multiple keys (see this detailed presentation).

I prevently implemented #2111 and reverted a bad approach, which pattern-matched the expanded form above in the tree, and replaced it with the row value expression. This was both brittle (some tree shapes failed to be matched), and also results in complicated LINQ queries.

Ideally, users would be able to express row value comparisons in LINQ queries by using value tuples (see dotnet/efcore#26822):

ctx.Blogs.Where(b => EF.Functions.GreaterThan((b.X, b.Y), (3, 4)))

However, since value tuple syntax isn't yet supported in C# expression trees, we can allow object arrays instead:

ctx.Blogs.Where(b => EF.Functions.GreaterThan(new object[] { b.X, b.Y }, new object[] { 3, 4 }))

/cc @mrahhal

@roji roji added the enhancement New feature or request label May 4, 2022
@roji roji added this to the 7.0.0 milestone May 4, 2022
@roji roji self-assigned this May 4, 2022
roji added a commit to roji/efcore.pg that referenced this issue May 4, 2022
roji added a commit to roji/efcore.pg that referenced this issue May 4, 2022
roji added a commit to roji/efcore.pg that referenced this issue May 4, 2022
roji added a commit to roji/efcore.pg that referenced this issue May 4, 2022
roji added a commit to roji/efcore.pg that referenced this issue May 6, 2022
@roji
Copy link
Member Author

roji commented May 6, 2022

OK, I ended up going in a slightly different direction, and using ValueType<T1, T2...>(x, y...) instead of arrays to express row values in LINQ:

_ = await ctx.Customers
    .Where(c => EF.Functions.GreaterThan(
        new ValueTuple<string, string>(c.City, c.CustomerID),
        new ValueTuple<string, string>("Buenos Aires", "OCEAN")))
    .CountAsync();

This is a bit uglier/more verbose, but:

  • Tuples really are the appropriate .NET construct for mapping a row value.
  • When some day ValueTuple syntax becomes supported in LINQ expression trees ((x, y)), this should transition seamlessly.
  • Doesn't rely on provider array support which is PG-specific, so should be easily portable to the general relational layer if we want.
  • Avoids any confusion with arrays, which are also supported in PG. For example, arrays can also be compared in PG (very different thing from row value comparisons).
  • We can selectively make NewExpression for ValueTuple non-evaluatable, rather than NewArrayExpression, which is used in other contexts where it can be evaluatable (see Support for row value comparisons #2350 (comment)).

/cc @smitpatel

@roji roji changed the title Allow expression row value comparisons via extension method with object array parameters Allow expression row value comparisons via extension methods May 6, 2022
roji added a commit to roji/efcore.pg that referenced this issue May 6, 2022
roji added a commit to roji/efcore.pg that referenced this issue May 7, 2022
@roji roji closed this as completed in #2350 May 7, 2022
roji added a commit that referenced this issue May 7, 2022
roji added a commit to npgsql/doc that referenced this issue May 8, 2022
@roji
Copy link
Member Author

roji commented May 8, 2022

Docs: npgsql/doc@511a6af in the 7.0 branch.

roji added a commit to npgsql/doc that referenced this issue May 17, 2022
roji added a commit to npgsql/doc that referenced this issue Jun 5, 2022
roji added a commit to roji/Npgsql.Doc that referenced this issue Jul 10, 2022
roji added a commit to roji/Npgsql.Doc that referenced this issue Aug 24, 2022
roji added a commit to roji/Npgsql.Doc that referenced this issue Aug 24, 2022
roji added a commit to npgsql/doc that referenced this issue Aug 24, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant