Hi!

I'm trying to convert some legacy database queries to use LLBLGen generated code instead, but I got stumped at this one query. Is it possible to convert this query into C# code?

SELECT AVG(numberOfBidders * 1.0) 
FROM (SELECT COUNT(DISTINCT BidderId) numberOfBidders 
    FROM Bid B (NOLOCK) 
    WHERE CreatedDate BETWEEN @startdate AND @enddate 
    GROUP BY AuctionId) 
AS RESULT

where @startdate and @enddate are DateTime variables.

I've tried a few ways. The closest I got was this

public static decimal GetAverageNumberOfBiddersPerAuction(DateTime startDate, DateTime endDate)
{

    var bids = new BidCollection();

    var innerPredicate = new PredicateExpression();
    innerPredicate.Add(BidFields.CreatedDate >= startDate);
    innerPredicate.Add(BidFields.CreatedDate <= endDate);

    var groupBy = new GroupByCollection();
    groupBy.Add(BidFields.AuctionId);

    var bidderCount = new EntityField2("BidderCount", 
        new  ScalarQueryExpression(BidFields.BidderId.SetAggregateFunction(AggregateFunction.CountDistinct), 
        innerPredicate, null, null, groupBy));

    var returnValue = bids.GetScalar(BidFieldIndex.Id, bidderCount * 1.0m, AggregateFunction.Avg);

    if (Equals(returnValue, DBNull.Value))
    {
        return 0;
    }

    return (decimal)returnValue;
}

but got the exception

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException : An exception was caught during the execution of a retrieval query: Cannot perform an aggregate function on an expression containing an aggregate or a subquery. 

which I suppose should tell me that it isn't possible to do just that, but I figured someone here might think of a way to rewrite the query or code. I tried to retrieve a BidCollection and loop through all entities and calculate the average that way, but since the table contains over 250.000 entries it is inefficient to say the least.

The Bid table has the following fields: [Id], [AuctionId], [BidderId], [Bid], [CreatedDate]

Here's an excerpt from the table:

Id             AuctionId    BidderId                                                                     Bid    CreatedDate
1184156 89134   9649D13E-F2F2-42AC-B3AF-5AF7F01F8AB7      1   2008-06-25 07:03:38.937
1184157 89134   2B6D1F17-FD18-4758-BD60-81D65E9F36AF     11 2008-06-25 07:05:05.907
1184158 89134   6C37547C-39A7-4B58-9718-8E1A7AC33B5B    21  2008-06-25 07:22:11.187
1184159 89134   9649D13E-F2F2-42AC-B3AF-5AF7F01F8AB7      31    2008-06-25 07:23:59.250
1184160 89175   0A270B82-92FE-4B58-9296-35C56FE7BB46      1  2008-06-25 07:33:08.750
1184161 89212   0A270B82-92FE-4B58-9296-35C56FE7BB46      1  2008-06-25 07:33:47.250

which should give the average bidder count (3 + 1 + 1) / 3 = 1.66666666...

Our setup: LLBLGen Pro v2.6 Final .Net v3.5 SQL Server 2005

To select from a Temp table you should try using DerivedTableDefinition.

An easier solution is to only execute the inner select and calculate the Average at client side.

SELECT COUNT(DISTINCT BidderId) numberOfBidders 
    FROM Bid B (NOLOCK) 
    WHERE CreatedDate BETWEEN @startdate AND @enddate 
    GROUP BY AuctionId

And for this you hsould use a DynamicList.