是否可以组合这两个linq查询

本文关键字:两个 linq 查询 组合 是否 | 更新日期: 2023-09-27 18:27:31

我正在使用实体框架来返回我的数据。

我有两个几乎相同的方法/查询。唯一的区别是有一个额外的Where语句。

第一个查询获取已接受事务的平均值,第二个查询获取所有事务的平均数。

以下是两种方法:

    static IEnumerable<BuyerEarning> GetBuyerEPA()
    {
        var collectTo = DateTime.Now.AddDays(-1).DayEnd();
        var collectFrom = collectTo.AddDays(-29).DayStart();
        using (var uow = new UnitOfWork(ConnectionString.PaydayLenders))
        {
            var r = new Repository<MatchHistory>(uow.Context);
            return r.Find()
                .Where(x =>
                    x.ResultTypeId == (int)MatchResultType.Accepted &&
                    x.CreatedOn <= collectTo &&
                    x.CreatedOn >= collectFrom)
                .GroupBy(x => new
                {
                    x.BuyerId,
                    x.TreeId,
                    x.TierId
                })
                .ToList()
                .Select(x => new BuyerEarning(
                    x.Key.BuyerId,
                    x.Key.TreeId,
                    x.Key.TierId,
                    x.Average(y => y.Commission)))
                .ToList();
        }
    }
    static IEnumerable<BuyerEarning> GetBuyerEPL()
    {
        var collectTo = DateTime.Now.AddDays(-1).DayEnd();
        var collectFrom = collectTo.AddDays(-29).DayStart();
        using (var uow = new UnitOfWork(ConnectionString.PaydayLenders))
        {
            var r = new Repository<MatchHistory>(uow.Context);
            return r.Find()
                .Where(x =>
                    x.CreatedOn <= collectTo &&
                    x.CreatedOn >= collectFrom)
                .GroupBy(x => new
                {
                    x.BuyerId,
                    x.TreeId,
                    x.TierId
                })
                .ToList()
                .Select(x => new BuyerEarning(
                    x.Key.BuyerId,
                    x.Key.TreeId,
                    x.Key.TierId,
                    x.Average(y => y.Commission)))
                .ToList();
        }
    }

我不想使用2个不同的查询,而是使用1个返回BuyerId、TreeId、TierId、EpaValue、EplValue。这可能吗?如果可能,怎么做?

是否可以组合这两个linq查询

虽然大多数其他答案都在向您展示如何使用相同的代码在EPL和EPA之间切换查询,但我相信您要问的是如何在单个查询中获得这两个值。

static IEnumerable<BuyerEarning> GetBuyerEPA()
{
    var collectTo = DateTime.Now.AddDays(-1).DayEnd();
    var collectFrom = collectTo.AddDays(-29).DayStart();
    using (var uow = new UnitOfWork(ConnectionString.PaydayLenders))
    {
        var r = new Repository<MatchHistory>(uow.Context);
        return r.Find()
            .Where(x =>
                x.CreatedOn <= collectTo &&
                x.CreatedOn >= collectFrom)
            .GroupBy(x => new
            {
                x.BuyerId,
                x.TreeId,
                x.TierId
            })
            .Select(x => new BuyerEarning(
                x.Key.BuyerId,
                x.Key.TreeId,
                x.Key.TierId,
                x.Average(y => y.Commission), //EPL
                x.Where(y => y.ResultTypeId == (int)MatchResultType.Accepted)
                    .Average(y => y.Commission)) //EPA
            .ToList();
    }
}

您可以创建以谓词为参数的辅助方法:

static IEnumerable<BuyerEarning> GetXXXX(Func<MatchHistory, bool> predicate = null)
{
    var collectTo = DateTime.Now.AddDays(-1).DayEnd();
    var collectFrom = collectTo.AddDays(-29).DayStart();
    using (var uow = new UnitOfWork(ConnectionString.PaydayLenders))
    {
        var r = new Repository<MatchHistory>(uow.Context);
        var filtered = r.Find()
                        .Where(x.CreatedOn <= collectTo && x.CreatedOn >= collectFrom);
        if(predicate != null)
            filtered = filtered.Where(predicate);
        return filtered 
            .GroupBy(x => new
            {
                x.BuyerId,
                x.TreeId,
                x.TierId
            })
            .ToList()
            .Select(x => new BuyerEarning(
                x.Key.BuyerId,
                x.Key.TreeId,
                x.Key.TierId,
                x.Average(y => y.Commission)))
            .ToList();
    }
}

然后在每个原始方法中使用它:

static IEnumerable<BuyerEarning> GetBuyerEPA()
{
    return GetXXXX(x => x.ResultTypeId == (int)MatchResultType.Accepted);
}
static IEnumerable<BuyerEarning> GetBuyerEPL()
{
    return GetXXXX();
}

Btw,为什么在最终投影之前使用ToList()?它使得Average计算由应用程序(使用LINQ to Objects)执行,这比由SQL Server执行的效率低得多。

为什么不重构Where子句并将其作为变量传入?

var epaFilter = new Func<MatchHistory, bool>(x => x.ResultTypeId == (int)MatchResultType.Accepted &&      x.CreatedOn <= collectTo &&                    x.CreatedOn >= collectFrom);
var eplFilter = new Func<MatchHistory, bool>(x => x.CreatedOn <= collectTo && x.CreatedOn >= collectFrom);        
private static IEnumerable<MatchHistory> GetBuyerByFilter(Func<MatchHistory,Boolean> filter)
{
    var collectTo = DateTime.Now.AddDays(-1).DayEnd();
    var collectFrom = collectTo.AddDays(-29).DayStart();
    using (var uow = new UnitOfWork(ConnectionString.PaydayLenders))
    {
        var r = new Repository<MatchHistory>(uow.Context);
        return r.Find()
            .Where(filter)
            .GroupBy(x => new
            {
                x.BuyerId,
                x.TreeId,
                x.TierId
            })
            .ToList()
            .Select(x => new BuyerEarning(
                x.Key.BuyerId,
                x.Key.TreeId,
                x.Key.TierId,
                x.Average(y => y.Commission)))
            .ToList();
    }
}

看起来你想要这个:

static IEnumerable<BuyerEarning> GetBuyer(bool acceptedOnly)
{
    var collectTo = DateTime.Now.AddDays(-1).DayEnd();
    var collectFrom = collectTo.AddDays(-29).DayStart();
    using (var uow = new UnitOfWork(ConnectionString.PaydayLenders))
    {
        var r = new Repository<MatchHistory>(uow.Context);
        IQueryable<MatchHistory> results = r.Find()
            .Where(x =>
                x.CreatedOn <= collectTo &&
                x.CreatedOn >= collectFrom);
        if (acceptedOnly)
        {
            results = results
                .Where(x => x.ResultTypeId == (int)MatchResultType.Accepted);
        }
        return results
            .GroupBy(x => new
            {
                x.BuyerId,
                x.TreeId,
                x.TierId
            })
            .ToList()
            .Select(x => new BuyerEarning(
                x.Key.BuyerId,
                x.Key.TreeId,
                x.Key.TierId,
                x.Average(y => y.Commission)))
            .ToList();
    }
}