CODEWITHSUNDEEP
c#asp.netlinq
dotnetnoob
dotnetnoob

Reputation: 11360

Is it possible to combine these 2 linq queries?

I'm using entity framework to return my data.

I have 2 almost identical methods/queries. The only difference is that one has an additional Where statement.

The first query gets the average value of accepted transactions, and the second query gets the average of all transactions.

Here are the 2 methods:

    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();
        }
    }

Rather than have 2 distinct queries I'd like to use 1 that returns BuyerId, TreeId, TierId, EpaValue, EplValue. Is this possible and if so, how?

Upvotes: 1

Views: 164

Answers (4)

Jeremy Smith
Jeremy Smith

Reputation: 1469

Why not refactor the Where clause and pass it in as a variable?

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();
    }
}

Upvotes: 3

cadrell0
cadrell0

Reputation: 17327

While most of the other answers are showing you how to use the same code to toggle the query between EPL and EPA, I believe what you are asking is how to get both values in a single query.

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();
    }
}

Upvotes: 3

MarcinJuraszek
MarcinJuraszek

Reputation: 125660

You can create helper method with a predicate as parameter:

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();
    }
}

And then use it within each of the original methods:

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

static IEnumerable<BuyerEarning> GetBuyerEPL()
{
    return GetXXXX();
}

Btw., why do you use ToList() before final projection? It makes the Average calculation being performed by application (using LINQ to Objects), which is much less efficient then when done by SQL Server.

Upvotes: 3

p.s.w.g
p.s.w.g

Reputation: 149058

Seems like you want this:

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();
    }
}

Upvotes: 2

Related Questions