Supported LINQ Operators

Polecat's LINQ provider supports the following operators and methods.

Comparison Operators

.Where(x => x.Age == 25)
.Where(x => x.Age != 25)
.Where(x => x.Age > 18)
.Where(x => x.Age >= 18)
.Where(x => x.Age < 65)
.Where(x => x.Age <= 65)

Boolean Logic

.Where(x => x.Active && x.Age > 18)
.Where(x => x.Active || x.Admin)
.Where(x => !x.Deleted)

String Operations

.Where(x => x.Name.Contains("john"))
.Where(x => x.Name.StartsWith("J"))
.Where(x => x.Name.EndsWith("son"))
.Where(x => x.Name == "John")
.Where(x => string.IsNullOrEmpty(x.Name))

See Searching on String Fields for more details.

Null Checks

.Where(x => x.Email != null)
.Where(x => x.Email == null)

Arithmetic

.Where(x => x.Price * x.Quantity > 100)
.Where(x => x.Total - x.Discount < 50)

Collection Operations

// Check if a value is in a list
var ids = new[] { id1, id2, id3 };
.Where(x => ids.Contains(x.Id))

// Check child collections
.Where(x => x.Tags.Any())
.Where(x => x.Tags.Contains("priority"))

Ordering

.OrderBy(x => x.LastName)
.OrderByDescending(x => x.CreatedDate)
.ThenBy(x => x.FirstName)
.ThenByDescending(x => x.Age)

Projection

.Select(x => new { x.FirstName, x.LastName })
.Select(x => x.Email)

Aggregation

.CountAsync()
.LongCountAsync()
.AnyAsync()
.AnyAsync(x => x.Active)
.FirstAsync()
.FirstOrDefaultAsync()
.SingleAsync()
.SingleOrDefaultAsync()
.MinAsync(x => x.Age)
.MaxAsync(x => x.Age)
.SumAsync(x => x.Amount)
.AverageAsync(x => x.Score)

GroupBy

Polecat supports the GroupBy() LINQ operator for grouping documents by one or more keys and computing aggregate values. GroupBy translates to SQL GROUP BY with aggregate functions like COUNT, SUM, MIN, MAX, and AVG.

Simple Key with Aggregates

[Fact]
public async Task group_by_simple_key_with_count()
{
    await StoreSeedDataAsync();

    await using var query = theStore.QuerySession();
    var results = await query.Query<LinqTarget>()
        .GroupBy(x => x.Color)
        .Select(g => new { Color = g.Key, Count = g.Count() })
        .ToListAsync();

    results.Count.ShouldBe(3);
    results.Single(x => x.Color == TargetColor.Blue).Count.ShouldBe(2);
    results.Single(x => x.Color == TargetColor.Green).Count.ShouldBe(3);
    results.Single(x => x.Color == TargetColor.Red).Count.ShouldBe(1);
}

^{snippet source | anchor}

Composite Key

You can group by multiple properties using an anonymous type:

var results = await session.Query<LinqTarget>()
    .GroupBy(x => new { x.Color, x.Name })
    .Select(g => new { Color = g.Key.Color, Text = g.Key.Name, Count = g.Count() })
    .ToListAsync();

Where Before GroupBy

Filter documents before grouping with a standard Where() clause:

var results = await session.Query<LinqTarget>()
    .Where(x => x.Age > 20)
    .GroupBy(x => x.Color)
    .Select(g => new { Color = g.Key, Count = g.Count() })
    .ToListAsync();

HAVING (Where After GroupBy)

Filter groups with a Where() clause after GroupBy() -- this translates to SQL HAVING:

var results = await session.Query<LinqTarget>()
    .GroupBy(x => x.Color)
    .Where(g => g.Count() > 1)
    .Select(g => new { Color = g.Key, Count = g.Count() })
    .ToListAsync();

Supported Aggregates

• g.Count() / g.LongCount() -- COUNT(*)
• g.Sum(x => x.Property) -- SUM(property)
• g.Min(x => x.Property) -- MIN(property)
• g.Max(x => x.Property) -- MAX(property)
• g.Average(x => x.Property) -- AVG(property)

CountBy(), AggregateBy(), and Index() (.NET 9)

The CountBy, AggregateBy, and Index operators added in .NET 9 are not translated to SQL by Polecat, because .NET only added them to Enumerable (there are no IQueryable overloads, so a query provider never sees them). Calling them on a Polecat query therefore runs client-side: the full result set is pulled into memory first and the operator is applied there with LINQ to Objects -- the same behavior you get with EF Core.

For large tables, express a CountBy as GroupBy(...).Select(...), which Polecat does translate to a SQL GROUP BY:

// Translated to SQL: select count(*), color ... group by color
var counts = await session.Query<Target>()
    .GroupBy(x => x.Color)
    .Select(g => new { Color = g.Key, Count = g.Count() })
    .ToListAsync();

There is no efficient SQL equivalent for AggregateBy's arbitrary accumulator function; Index corresponds to a window ROW_NUMBER() but is likewise only an in-memory operator today. If/when .NET ships Queryable overloads for these, Polecat can revisit native translation.

Paging

.Skip(20)
.Take(10)
.ToPagedListAsync(pageNumber, pageSize)

Result Materialization

.ToListAsync()
.ToArrayAsync()
.ToJsonArrayAsync()