Bitwise Aggregates (PostgreSQL)

Bitwise Aggregates (PostgreSQL)

PostgreSQL provides the aggregate functions bit_or, bit_and and bit_xor that combine all values of a group with a bitwise OR / AND / XOR. This library exposes them through EF.Functions.BitOr, EF.Functions.BitAnd and EF.Functions.BitXor.

Requires using Thinktecture;. These are always available on the PostgreSQL provider — there is no option to enable. bit_xor requires PostgreSQL 14 or later.

Usage

Call the function inside a GroupBy(...).Select(...), passing the group's values via g.Select(e => e.Column):

var flagsPerOwner = await ctx.BusinessTransactionObjects
   .GroupBy(o => o.FinanceObjectId)
   .Select(g => new
   {
      FinanceObjectId = g.Key,
      PhaseMembership = EF.Functions.BitOr(g.Select(e => e.PhaseMembership))
   })
   .ToListAsync();

Generated SQL:

SELECT o.finance_object_id, bit_or(o.phase_membership)
FROM execution_engine.business_transaction_object AS o
GROUP BY o.finance_object_id

Supported types

BitOr<T> / BitAnd<T> / BitXor<T> are generic and return the same type they aggregate:

• short / int / long (PostgreSQL smallint / integer / bigint)
• their nullable forms (short?, int?, long?)
• [Flags] enums backed by an integer type — the enum round-trips, e.g.:

[Flags]
public enum PhaseMembership : short { None = 0, Draft = 1, Active = 2, Closed = 4 }

var combined = await ctx.Items
   .GroupBy(i => i.GroupId)
   .Select(g => EF.Functions.BitOr(g.Select(i => i.PhaseMembership)))
   .ToListAsync();

Null handling

bit_or/bit_and/bit_xor ignore NULL values and return NULL only when every value in the group is NULL.

Notes

• GROUP BY aggregate form only — there is no window (OVER (...)) form for these functions.
• No cast is emitted: PostgreSQL's bit_* returns the input type, and the result is mapped back to your CLR type automatically.