Hi everyone,

I need to return the sum for a number of columns in a single table (ie. sum particular properties in a single entity).

I am using the SelfServicing model.

My entity is named ReferralBonus.

It has the following fields:

AffiliateId : Guid EligibleDate : DateTime OrderAmount : decimal BonusAmount : decimal BonusPoolAmount : decimal

I need to return the totals for OrderAmount, BonusAmount and BonusPoolAmount where the EligibleDate has a specified YEAR and MONTH.

I'd like to do this in two ways:

1. Return totals for a single AffiliateId in the specified month/year, and
2. Return totals grouped by AffiliateId for the specified month/year.

As SQL queries I would do this as follows:

Case #1

Declare
@AffiliateId uniqueidentifier,
@Year int,
@Month int

set @AffiliateId = 'D38FE3D1-679F-48BB-A276-1C6F148A799A',
    @Year = 2013,
    @Month = 6

select SUM(OrderValue) as OrderTotal,
SUM(BonusPoolAmount) as PoolTotal,
SUM(BonusAmount) as BonusTotal
from ReferralBonus rb
where (AffiliateId = @AffiliateId)
AND DATEPART(MONTH, EligibleDate) = @Month
AND DATEPART(YEAR, EligibleDate) = @Year

... and for case #2

Declare
@AffiliateId uniqueidentifier,
@Year int,
@Month int

set @AffiliateId = 'D38FE3D1-679F-48BB-A276-1C6F148A799A',
    @Year = 2013,
    @Month = 6

select AffiliateId, SUM(OrderValue) as OrderTotal,
SUM(BonusPoolAmount) as PoolTotal,
SUM(BonusAmount) as BonusTotal
from ReferralBonus rb
where (AffiliateId = 'D38FE3D1-679F-48BB-A276-1C6F148A799A')
AND DATEPART(MONTH, EligibleDate) = 6
AND DATEPART(YEAR, EligibleDate) = 2013
group by AffiliateId

I'm not sure whether the best solution for this is to use QueryFactory or LinqMetaData. If possible, I'd love to see how this solution would be implemented using BOTH techniques.

For my first case (single affiliate), I think I can query the Sum() for each field using a query like this:

            var qf = new QueryFactory();
            var q =
                qf.ReferralBonus
                  .Where(ReferralBonusFields.AffiliateId == affiliate.AffiliateId)
                  .Select(() => new
                                {
                                    OrderTotal = ReferralBonusFields.OrderValue.Sum().ToValue<decimal>(),
                                    BonusTotal = ReferralBonusFields.BonusAmount.Sum().ToValue<decimal>(),
                                    PoolTotal = ReferralBonusFields.BonusPoolAmount.Sum().ToValue<decimal>()
                                });

... but I don't know how to add the DATEPART criteria to the Where() clause.

For the second case (all affiliates), I presume I need to add a group by clause like .GroupBy(ReferralBonusFields.AffiliateId) and remove the AffiliateId field from the Where() clause.

I'd really appreciate any help that anyone can give me to work out how to structure this.

Thanks in advance.

Your query is correct. To do the Year/Month part, there are predefined QuerySpec datetime function mappings.

For Linq2LLBL is very similar. See the aggregate examples in the documentation.