postgresql EF核心Where子句中具有多个参数和int返回类型的表达式

huus2vyu  于 2023-02-04  发布在  PostgreSQL
关注(0)|答案(1)|浏览(122)

我正在使用.NET Core 3.1、Microsoft.EntityFrameworkCore 3.1.9Npgsql 4.1.9。我希望重用CompareBuilds函数来比较Where(...)语句中的2个Build对象。我希望将该函数转换为SQL。
这是我目前拥有的功能:

public List<Ticket> GetTicketsForBuild(Build build)
{
    Expression<Func<Build, Build, int>> CompareBuilds = (first, second) =>
        // complex logic, simplified for the sake of example
        first.Version == second.Version ? 0 : (first.Version < second.Version ? -1 : 1);
    
    return _myDbContext.Ticket
        .Where(x => CompareBuilds(x.BuildIntroducedIn, build) <= 0)
        .Where(x => x.IsPublic ?
            CompareBuilds(x.BuildResolvedIn, build) < 0 :
            CompareBuilds(x.BuildResolvedIn, build) > 0)
        .ToList();
}

但是,编译器在第一个Where子句中给出了以下错误:

CS0149: Method name expected

如何重用同一个函数来比较Where子句中的两个Build对象?此外,该函数应该返回一个int,就像代码片段中演示的那样。

jgovgodb

jgovgodb1#

首先激活LINQKit,只需要配置DbContextOptions即可:

builder
    .UseSqlServer(connectionString) // or any other provider
    .WithExpressionExpanding();     // enabling LINQKit extension

然后,您可以重复使用以下表达式:

public List<Ticket> GetTicketsForBuild(Build build)
{
    Expression<Func<Build, Build, int>> CompareBuilds = (first, second) =>
        // complex logic, simplified for the sake of example
        first.Version == second.Version ? 0 : (first.Version < second.Version ? -1 : 1);
    
    return _myDbContext.Ticket
        .Where(x => CompareBuilds.Invoke(x.BuildIntroducedIn, build) <= 0)
        .Where(x => x.IsPublic ?
            CompareBuilds.Invoke(x.BuildResolvedIn, build) < 0 :
            CompareBuilds.Invoke(x.BuildResolvedIn, build) > 0)
        .ToList();
}

但通常这样的函数在其他查询中很方便,我们可以用更通用的方式来做:

public static class QueryHelper
{
    [Expandable(nameof(CompareBuildsImpl))]
    public static int CompareBuilds(Build first, Build second)
    {
        throw new InvalidOperationexception();
    }

    private static Expression<Func<Build, Build, int>> CompareBuildsimpl()
    {
        return (first, second) =>
            // complex logic, simplified for the sake of example
            first.Version == second.Version ? 0 : (first.Version < second.Version ? -1 : 1);
    }
}

并在查询中重用:

public List<Ticket> GetTicketsForBuild(Build build)
{    
    return _myDbContext.Ticket
        .Where(x => QueryHelper.CompareBuilds(x.BuildIntroducedIn, build) <= 0)
        .Where(x => x.IsPublic ?
            QueryHelper.CompareBuilds(x.BuildResolvedIn, build) < 0 :
            QueryHelper.CompareBuilds(x.BuildResolvedIn, build) > 0)
        .ToList();
}

还有其他的库做同样的事情,我已经收集了其中的一些here

相关问题