PowerPortalsPro.Dataverse.Linq 1.0.8

PowerPortalsPro.Dataverse.Linq

A strongly-typed LINQ query provider for Microsoft Dataverse (Dynamics 365 / Power Platform) that translates standard LINQ expressions into FetchXml and executes them against the Dataverse API. Write queries using familiar C# syntax instead of hand-crafting FetchXml strings.

Installation

dotnet add package PowerPortalsPro.Dataverse.Linq

Supports .NET 8, .NET 10+ (with full async support) and .NET Framework 4.6.2+ (sync only).

Quick Start

using PowerPortalsPro.Dataverse.Linq;

var accounts = await service.Queryable<Account>()
    .Where(a => a.Name.StartsWith("Contoso"))
    .OrderBy(a => a.Name)
    .ToListAsync();

Entry Points

The Queryable() extension method creates a LINQ queryable from your service connection. On .NET 10+ it extends IOrganizationServiceAsync; on .NET Framework 4.6.2+ it extends IOrganizationService.

// Typed query — entity type must have [EntityLogicalName] attribute
var accounts = await service.Queryable<Account>().ToListAsync();

// Typed query with explicit column set
var accounts = await service.Queryable<Account>("name", "revenue").ToListAsync();

// Unbound query — no proxy class required
var results = await service.Queryable("account", "name", "revenue").ToListAsync();

Filtering

Standard C# comparison operators, null checks, and string methods translate directly to FetchXml conditions. All supported condition operators are listed below.

Comparison and Equality

Translates to FetchXml operators eq, ne, lt, le, gt, ge, null, not-null.

.Where(a => a.Name == "Contoso")
.Where(a => a.Revenue > 1000000 && a.Revenue <= 5000000)
.Where(a => a.Description != null)

String Methods

Contains, StartsWith, and EndsWith translate to like / not-like with appropriate wildcard patterns. string.IsNullOrEmpty() translates to a null OR eq "" filter.

.Where(a => a.Name.Contains("Corp"))
.Where(a => a.Name.StartsWith("A"))
.Where(a => a.Name.EndsWith("Inc"))
.Where(a => !string.IsNullOrEmpty(a.Email))

String Length

string.Length comparisons translate to like / not-like patterns using underscore wildcards.

.Where(a => a.Name.Length == 10)
.Where(a => a.Name.Length > 5)

In / Not In

Contains() on a collection translates to in / not-in.

var names = new[] { "Contoso", "Fabrikam", "Northwind" };
.Where(a => names.Contains(a.Name))
.Where(a => !names.Contains(a.Name))

// Also works with Guid collections
var ids = new[] { id1, id2, id3 };
.Where(a => ids.Contains(a.AccountId))

Column-to-Column Comparison

Compare two columns in the same row using the FetchXml valueof attribute. Also works across joined entities.

.Where(c => c.FirstName == c.LastName)
.Where(o => o.ActualRevenue > o.EstimatedRevenue)

Negation

Prefix any boolean filter with ! to negate it:

.Where(a => !a.Name.Contains("Test"))
.Where(a => !a.CreatedOn.LastXDays(30))

Subquery Filtering (Any, All, Exists)

These operators filter rows based on the existence or properties of related records, translating to FetchXml link-entity elements with special link-type values. Each predicate must include a join condition relating the inner entity to the outer entity.

Any — at least one match

Any() translates to link-type="any" (nested inside a <filter>). Returns parent rows where at least one related child matches.

// Contacts that are the primary contact of an account named "Contoso"
await service.Queryable<Contact>()
    .Where(c => service.Queryable<Account>().Any(
        a => a.PrimaryContactId.Id == c.ContactId && a.Name == "Contoso"))
    .ToListAsync();

// Negate with ! → link-type="not any" (no matching children)
await service.Queryable<Contact>()
    .Where(c => !service.Queryable<Account>().Any(
        a => a.PrimaryContactId.Id == c.ContactId))
    .ToListAsync();

All — every child matches

All() translates to link-type="all". Returns parent rows where every related child satisfies the condition. Parents with no children are excluded (no vacuous truth).

The LINQ predicate is automatically negated in the generated FetchXml (including DeMorgan's law for nested &&/|| conditions), because FetchXml link-type="all" uses inverted filter semantics — the filter describes what would cause a child to fail.

// Contacts where ALL linked accounts have a non-null rating
await service.Queryable<Contact>()
    .Where(c => service.Queryable<Account>().All(
        a => a.PrimaryContactId.Id == c.ContactId
             && a.AccountRating != null))
    .ToListAsync();

// Negate with ! → link-type="not all" (at least one child fails)
await service.Queryable<Contact>()
    .Where(c => !service.Queryable<Account>().All(
        a => a.PrimaryContactId.Id == c.ContactId
             && a.Name == "Contoso"))
    .ToListAsync();

// Complex predicate with OR — DeMorgan applied automatically
// All(Name == "Contoso" || Rating != null) generates:
//   link-type="all" with filter: Name != "Contoso" AND Rating == null
await service.Queryable<Contact>()
    .Where(c => service.Queryable<Account>().All(
        a => a.PrimaryContactId.Id == c.ContactId
             && (a.Name == "Contoso" || a.AccountRating != null)))
    .ToListAsync();

All() and !All() are complementary within the set of parents that have at least one related child.

Exists and In — semi-join operators

Exists() and In() translate to link-type="exists" and link-type="in" respectively, placed as direct children of the <entity> element (not inside a <filter>). Both are semi-joins — they filter parent rows based on the existence of matching child rows without returning columns from the related entity.

Exists() and In() are semantically equivalent but may have different performance characteristics depending on the Dataverse query optimizer. Exists uses a correlated subquery while In uses an IN subquery. Try both if you encounter performance issues with large datasets.

// Accounts that have at least one active contact (EXISTS subquery)
await service.Queryable<Account>()
    .Where(a => service.Queryable<Contact>().Exists(
        c => c.ParentCustomerId.Id == a.AccountId && c.StateCode == 0))
    .ToListAsync();

// Same query using IN subquery
await service.Queryable<Account>()
    .Where(a => service.Queryable<Contact>().In(
        c => c.ParentCustomerId.Id == a.AccountId && c.StateCode == 0))
    .ToListAsync();

// Negate with ! — Dataverse doesn't support "not exists" or "not in" as
// link-types, so negation automatically falls back to link-type="not any"
await service.Queryable<Account>()
    .Where(a => !service.Queryable<Contact>().Exists(
        c => c.ParentCustomerId.Id == a.AccountId))
    .ToListAsync();

// Join-only (no filter) — check for any related record
await service.Queryable<Account>()
    .Where(a => service.Queryable<Contact>().Exists(
        c => c.ParentCustomerId.Id == a.AccountId))
    .ToListAsync();

Summary

DateTime Operators

Extension methods in PowerPortalsPro.Dataverse.Linq map to all FetchXml datetime condition operators:

using PowerPortalsPro.Dataverse.Linq;

Parameterless operators:

Parameterized operators:

All DateTime methods have both DateTime and DateTime? overloads.

User and Business Unit Operators

Extension methods for user/business unit condition operators:

using PowerPortalsPro.Dataverse.Linq;

.Where(a => a.OwnerId.Id.EqualUserId())         // Current user's records
.Where(a => a.OwningBusinessUnit.Id.EqualBusinessId())  // Current business unit

Hierarchy Operators

Extension methods for hierarchical condition operators:

using PowerPortalsPro.Dataverse.Linq;

.Where(a => a.AccountId.Under(parentId))
.Where(a => a.AccountId.AboveOrEqual(childId))

Multi-Select Option Sets

Extension methods for choice column operators (contain-values / not-contain-values):

using PowerPortalsPro.Dataverse.Linq;

// ContainsValues — contain-values operator
.Where(c => c.PreferredMethods.ContainsValues(Method.Email, Method.Phone))

// Negated — not-contain-values operator
.Where(c => !c.PreferredMethods.ContainsValues(Method.Email))

// Single-item Contains — contain-values for one value
.Where(c => c.PreferredMethods.Contains(Method.Email))

// Equals — eq/ne for single value, in/not-in for multiple
.Where(c => c.PreferredMethods.Equals(Method.Email))
.Where(c => c.PreferredMethods.Equals(new[] { Method.Email, Method.Phone }))

Projections

Select specific columns using FetchXml attribute elements. Only the selected columns are retrieved from Dataverse.

// Anonymous type projection
var results = await service.Queryable<Account>()
    .Select(a => new { a.Name, a.Revenue, a.PrimaryContact })
    .ToListAsync();

// Project into entity type
var results = await service.Queryable<Account>()
    .Select(a => new Account { AccountId = a.AccountId, Name = a.Name })
    .ToListAsync();

// Ternary / null-coalesce expressions are supported in projections
.Select(a => new { a.AccountId, IsPreferred = a.IsPreferred ?? false })

Joins

Inner joins and left joins using standard LINQ join syntax translate to FetchXml link-entity elements.

// Inner join
var results = await (from a in service.Queryable<Account>()
                     join c in service.Queryable<Contact>()
                         on a.AccountId equals c.ParentCustomerId.Id
                     where a.Name != null
                     orderby c.LastName
                     select new { AccountName = a.Name, ContactName = c.FullName })
                    .ToListAsync();

// Left join (DefaultIfEmpty)
var results = await (from a in service.Queryable<Account>()
                     join c in service.Queryable<Contact>()
                         on a.AccountId equals c.ParentCustomerId.Id into contacts
                     from c in contacts.DefaultIfEmpty()
                     select new { a.Name })
                    .ToListAsync();

// Three-way join
var results = await (from a in service.Queryable<Account>()
                     join c in service.Queryable<Contact>()
                         on a.PrimaryContactId.Id equals c.ContactId
                     join pa in service.Queryable<Account>()
                         on c.ParentCustomerId.Id equals pa.AccountId
                     select new { a.Name, ContactName = c.FullName, ParentName = pa.Name })
                    .ToListAsync();

// Join with explicit column sets
var results = await (from a in service.Queryable<Account>("name", "createdon")
                     join c in service.Queryable<Contact>("firstname", "lastname")
                         on a.PrimaryContactId.Id equals c.ContactId
                     select new { a.Name, c.FirstName, c.LastName })
                    .ToListAsync();

// Sub-query: join on pre-filtered queryables
var activeAccounts = service.Queryable<Account>().Where(a => a.StateCode == 0);
var results = await (from a in activeAccounts
                     join c in service.Queryable<Contact>()
                         on a.PrimaryContactId.Id equals c.ContactId
                     select new { a.Name, c.FullName })
                    .ToListAsync();

// First-row join — returns only the first matching child per parent
// Translates to link-type="matchfirstrowusingcrossapply"
var results = await (from c in service.Queryable<Contact>()
                     join t in service.Queryable<Task>().WithFirstRow()
                         on c.ContactId equals t.RegardingObjectId.Id
                     select new { c.FullName, t.Subject })
                    .ToListAsync();

Ordering

Translates to FetchXml order elements.

.OrderBy(a => a.Name)
.OrderByDescending(a => a.Revenue)

// Multiple sort criteria (including across joined entities)
from a in service.Queryable<Account>()
join c in service.Queryable<Contact>() on a.PrimaryContactId.Id equals c.ContactId
orderby c.LastName, a.Name
select new { a.Name, c.LastName }

Paging

Control FetchXml paging with WithPageSize, WithPage, and Take.

// Page size and page number
var page2 = await service.Queryable<Account>()
    .OrderBy(a => a.Name)
    .WithPageSize(50)
    .WithPage(2)
    .ToListAsync();

// Top N records (FetchXml top attribute)
var top10 = await service.Queryable<Account>()
    .OrderByDescending(a => a.Revenue)
    .Take(10)
    .ToListAsync();

ForEachPage / ForEachPageAsync

When calling ToList() or ToListAsync(), all pages are fetched automatically and combined into a single list. This is simple but loads the entire result set into memory at once, which may not be desirable for large data sets.

ForEachPage and ForEachPageAsync give you control over how each page is processed as it arrives from Dataverse. This is useful when you need to:

• Process records in batches without loading everything into memory
• Stream results to an external system as they arrive
• Apply back-pressure or throttling between pages
• Track progress across large data sets

Use WithPageSize() to control how many records are returned per page. Without it, Dataverse uses its default page size (up to 5,000 records), meaning all results may come back in a single page.

// Process 100 records at a time
await service.Queryable<Account>()
    .Where(a => a.StateCode == 0)
    .WithPageSize(100)
    .ForEachPageAsync(async page =>
    {
        Console.WriteLine($"Processing batch of {page.Count} accounts...");
        foreach (var account in page)
            await ProcessAccountAsync(account);
    });

ForEachPageAsync accepts a Func<List<T>, Task> callback and supports a CancellationToken. A synchronous ForEachPage overload accepting Action<List<T>> is also available. Both methods work with Where, Select, OrderBy, and other query operators — the filtering and projection are applied server-side in the FetchXml, so each page contains only the matching, projected results.

Distinct

Translates to the FetchXml distinct attribute:

var uniqueNames = await service.Queryable<Account>()
    .Select(a => a.Name)
    .Distinct()
    .ToListAsync();

Aggregations

Standard LINQ aggregate methods translate to FetchXml aggregate queries.

var count = await service.Queryable<Account>().CountAsync();
var count = await service.Queryable<Account>().CountAsync(a => a.Revenue > 0);
var max   = await service.Queryable<Account>().MaxAsync(a => a.Revenue);
var sum   = await service.Queryable<Account>().Select(a => a.NumberOfEmployees).SumAsync();
var avg   = await service.Queryable<Account>().Select(a => a.PercentComplete).AverageAsync();

// CountColumnAsync — counts non-null values only
var nonNull = await service.Queryable<Account>()
    .Select(a => a.NumberOfEmployees)
    .CountColumnAsync();

// CountChildren — row aggregate for hierarchical entities
var results = await service.Queryable<Account>()
    .Select(a => new { a.Name, Children = a.CountChildren() })
    .ToListAsync();

GroupBy

GroupBy translates to FetchXml grouping with aggregation.

// Simple group with aggregates
var results = await (from a in service.Queryable<Account>()
                     group a by a.IndustryCode into g
                     select new
                     {
                         Industry = g.Key,
                         Count = g.Count(),
                         TotalRevenue = g.Sum(x => x.Revenue),
                         AverageRevenue = g.Average(x => x.Revenue),
                         MaxEmployees = g.Max(x => x.NumberOfEmployees),
                         MinEmployees = g.Min(x => x.NumberOfEmployees),
                         DescriptionCount = g.CountColumn(x => x.Description),
                     }).ToListAsync();

// Group by constant — aggregate without grouping
var totals = await (from a in service.Queryable<Account>()
                    group a by 1 into g
                    select new
                    {
                        Count = g.Count(),
                        Total = g.Sum(x => x.Revenue),
                    }).FirstAsync();

// Join + GroupBy — aggregate on linked entity
var results = await (from c in service.Queryable<Contact>()
                     join o in service.Queryable<Opportunity>()
                         on c.ContactId equals o.ParentContactId.Id
                     group o by c.ContactId into g
                     select new
                     {
                         ContactId = g.Key,
                         Count = g.Count(),
                         TotalRevenue = g.Sum(x => x.ActualRevenue),
                     }).ToListAsync();

Date Grouping

Group by date parts using the dategrouping FetchXml attribute:

using PowerPortalsPro.Dataverse.Linq;

var byYear = await (from o in service.Queryable<Opportunity>()
                    group o by o.ActualCloseDate.Value.Year into g
                    orderby g.Key
                    select new { Year = g.Key, Count = g.Count() })
                   .ToListAsync();

var byQuarter = await (from o in service.Queryable<Opportunity>()
                       group o by o.ActualCloseDate.Value.Quarter() into g
                       select new { Quarter = g.Key, Count = g.Count() })
                      .ToListAsync();

var byFiscalYear = await (from o in service.Queryable<Opportunity>()
                          group o by o.ActualCloseDate.Value.FiscalYear() into g
                          select new { FiscalYear = g.Key, Count = g.Count() })
                         .ToListAsync();

OptionSet Grouping

Group by OptionSetValue.Value for choice columns:

var results = await (from o in service.Queryable<Opportunity>()
                     group o by o.StatusReason.Value into g
                     select new { Status = g.Key, Count = g.Count() })
                    .ToListAsync();

Unbound Queries

Query without a typed proxy class using GetAttributeValue:

var results = await service.Queryable("account", "name", "revenue")
    .Where(e => !string.IsNullOrEmpty(e.GetAttributeValue<string>("name")))
    .Select(e => new
    {
        Name = e.GetAttributeValue<string>("name"),
        Revenue = e.GetAttributeValue<Money>("revenue")
    })
    .ToListAsync();

Query Options

Configure FetchXml attributes on the query:

await service.Queryable<Account>()
    .WithPageSize(100)
    .WithLateMaterialize()
    .WithQueryHints(SqlQueryHint.ForceOrder, SqlQueryHint.DisableRowGoal)
    .ToListAsync();

Available SqlQueryHint values: ForceOrder, DisableRowGoal, EnableOptimizerHotfixes, LoopJoin, MergeJoin, HashJoin, NoPerformanceSpool, EnableHistAmendmentForAscKeys.

ReturnRecordCount / ReturnRecordCountAsync

Adds returntotalrecordcount="true" to the FetchXml query and invokes a callback with the total record count after the first page of results is retrieved. This is useful when you need to know the total number of matching rows without fetching them all — for example, to display a total count alongside a paged result set.

The callback receives a RecordCountArguments record containing TotalRecordCount (the total number of matching rows) and TotalRecordCountLimitExceeded (whether the count exceeds the 50,000 row limit).

Both methods are chainable and can be combined with any other query operators.

// Sync callback
RecordCountArguments? countArgs = null;
var results = service.Queryable<Account>()
    .Where(a => a.StateCode == 0)
    .ReturnRecordCount(args => countArgs = args)
    .ToList();

Console.WriteLine($"Showing {results.Count} of {countArgs!.TotalRecordCount} total records");

// Async callback (.NET 8, .NET 10+)
RecordCountArguments? countArgs = null;
var results = await service.Queryable<Account>()
    .Where(a => a.StateCode == 0)
    .ReturnRecordCountAsync(async args =>
    {
        countArgs = args;
        await Task.CompletedTask;
    })
    .ToListAsync();

Inspect Generated FetchXml

Use ToFetchXml() to see the generated FetchXml without executing the query:

var fetchXml = service.Queryable<Account>()
    .Where(a => a.Name != null)
    .OrderBy(a => a.Name)
    .Select(a => new { a.Name, a.Revenue })
    .ToFetchXml();

Console.WriteLine(fetchXml);

Async Operations (.NET 8, .NET 10+)

All query execution methods have async counterparts. These are only available when targeting .NET 10+.

SumAsync and AverageAsync have overloads for int, int?, decimal, and decimal?. FirstAsync, FirstOrDefaultAsync, SingleAsync, SingleOrDefaultAsync, and CountAsync have overloads accepting a predicate.

var accounts = await service.Queryable<Account>().ToListAsync();
var first = await service.Queryable<Account>().FirstAsync(a => a.Name == "Contoso");
var count = await service.Queryable<Account>().CountAsync();
var sum = await service.Queryable<Account>().Select(a => a.Revenue).SumAsync();

See the ForEachPage / ForEachPageAsync section for async paged processing examples.

Requirements

FetchXml Documentation

For more information about FetchXml, see the official Microsoft documentation:

• Query data using FetchXml - Overview
• FetchXml reference - Element reference
• Select columns
• Join tables
• Order rows
• Filter rows
• Condition operators - Full operator reference
• Page results
• Aggregate data
• Count rows
• Optimize performance
• Query hierarchical data

License

This project is licensed under the GNU Lesser General Public License v3.0 (LGPL-3.0). You are free to use this library in proprietary and open-source applications. If you modify the library itself, you must make those modifications available under the same license. See the LICENSE file for details.