Querying Documents with LINQ

Polecat provides a custom LINQ provider that translates .NET LINQ queries into SQL Server queries against the JSON document data.

Basic Queries

// Simple filter
var smiths = await session.Query<User>()
    .Where(x => x.LastName == "Smith")
    .ToListAsync();

// With ordering
var sorted = await session.Query<User>()
    .OrderBy(x => x.LastName)
    .ThenBy(x => x.FirstName)
    .ToListAsync();

// First or default
var first = await session.Query<User>()
    .FirstOrDefaultAsync(x => x.Email == "alice@example.com");

Aggregates

var count = await session.Query<User>().CountAsync();
var any = await session.Query<User>().AnyAsync(x => x.Internal);

Paging

var page = await session.Query<User>()
    .OrderBy(x => x.LastName)
    .Skip(20)
    .Take(10)
    .ToListAsync();

Or use the built-in paging support:

var pagedList = await session.Query<User>()
    .OrderBy(x => x.LastName)
    .ToPagedListAsync(pageNumber: 2, pageSize: 10);

See Paging for more details.

How It Works

LINQ queries are translated to SQL using JSON_VALUE() to extract properties from the JSON document:

SELECT data FROM pc_doc_user
WHERE JSON_VALUE(data, '$.lastName') = @p0
ORDER BY JSON_VALUE(data, '$.firstName')

The LINQ provider supports:

• Equality and comparison operators
• String operations (Contains, StartsWith, EndsWith)
• Boolean logic (And, Or, Not)
• Null checks
• Collection operations (Any, All, Contains)
• Arithmetic operations
• Nested property access

See Supported LINQ Operators for a complete list.