LINQ compiled query select sum multiple columns

Question

I have a table:

ForObjectTypeID (short, PK)
ForObjectID (int, PK)
UserID (int, PK)
Upvote (bool)
ShadowBannedVote (bool)

Given an ObjectTypeID and ObjectID, I wish to return a Tuple<int, int, int> where the respective values are:

Total Votes: Total number of records where ShadowBannedVote == false
Total Upvotes: Total number of records where Upvote == true && ShadowBannedVote == false
Total Shadow Banned Votes: Total number of records where ShadowBannedVote == true

It needs to be a single compiled query, not broken into multiple queries. This is as far as I've gotten, I just can't work out how to perform the sums and counts in the return value.

public static readonly Func<DBContext, ObjectType, int, Tuple<int, int, int>> GetTotalVotes = CompiledQuery.Compile(
    (DBContext db, ObjectType forObjectType, int forObjectID) =>
    db.UserVotes.Where(c => c.ForObjectTypeID == (short)forObjectType && c.ForObjectID == forObjectID)
    .Select(c=> new {c.Upvote, c.ShadowBannedVote}).Select(c=> new Tuple<int, int, in>(0, 0, 0)));

score 0 · Answer 1 · answered Aug 22 '17 at 14:32

Will be interested to see if this is possible, but one solution would be:

public static readonly Func<DBContext, ObjectType, int, IEnumerable<Tuple<bool, bool>>> GetTotalVotes = CompiledQuery.Compile(
    (DBContext db, ObjectType forObjectType, int forObjectID) =>
    db.UserVotes.Where(c => c.ForObjectTypeID == (short)forObjectType && c.ForObjectID == forObjectID)
    .Select(c=> new Tuple<bool, bool>(c.Upvote, c.ShadowBannedVote)));

And then simply work out the numbers in the application logic.

score 0 · Answer 2 · answered Aug 23 '17 at 08:36

You could try grouping by a constant, summing and taking the result, ie something like

  public static readonly Func<DBContext, ObjectType, int, Tuple<int, int, int>> GetTotalVotes = CompiledQuery.Compile(
        (DBContext db, ObjectType forObjectType, int forObjectID) 
   => 
   db.UserVotes
    .Where(c => c.ForObjectTypeID == (short)forObjectType 
             && c.ForObjectID == forObjectID)
    .Select(c => new { c.Upvote, c.ShadowBannedVote })
    .GroupBy(c => 1)
    .Select(c => new Tuple<int, int, int>(
        c.Count(r => !r.ShadowBannedVote), 
        c.Count(r => r.Upvote && !r.ShadowBannedVote), 
        c.Count(r => r.ShadowBannedVote)
    )).Single());

LINQ compiled query select sum multiple columns

2 Answers2