FluentDynamics.QueryBuilder 1.1.2

FluentDynamics QueryBuilder

FluentDynamics QueryBuilder is a fluent, chainable API for building and executing Dynamics 365/Dataverse queries. It simplifies the process of creating complex QueryExpressions with a more intuitive and readable syntax.

Features

• 🔄 Fluent API - Chainable, intuitive query building
• 🔍 Type-safe - Strong typing for Dynamics 365 operations
• 🚀 Async Support - Full support for async/await patterns
• 📊 LINQ-like Operations - Familiar extension methods for query results
• 📑 Pagination - Built-in support for handling paged results
• 🔗 Complex & Advanced Joins - Rich set of join helpers (Inner, LeftOuter, Natural, CrossApply, In, Exists, Any/NotAny, All/NotAll)
• 🧩 Extensible - Clean architecture for extending functionality
• 🛠 FetchXML Conversion - Convert queries to FetchXML easily
• 🧮 Distinct, NoLock, QueryHint, ForceSeek - Advanced query options
• ⚡ FilterBuilder Extensions - Syntactic sugar methods for common conditions (Equal, In, Like, LastXDays, IsNull, etc.)
• 🔍 Query Debugging - Human-readable query inspection with DebugView
• 🔄 Optimized Cloning - Efficient query cloning operations for different use cases

Installation

Install via NuGet Package Manager:

Install-Package FluentDynamics.QueryBuilder

Or via .NET CLI:

dotnet add package FluentDynamics.QueryBuilder

Basic Usage

using FluentDynamics.QueryBuilder;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;

// Create a simple query
var query = Query.For("account")
    .Select("name", "accountnumber", "telephone1")
    .Where(f => f
        .Condition("statecode", ConditionOperator.Equal, 0)
    )
    .OrderBy("name");

// Execute the query
EntityCollection results = query.RetrieveMultiple(organizationService);

// Use extension methods on results
var accounts = results.ToList();

Advanced Examples

Complex Filtering (Nested AND/OR)

var query = Query.For("contact")
    .Select("firstname", "lastname", "emailaddress1")
    .Where(f => f
        .Condition("statecode", ConditionOperator.Equal, 0)
        .And(fa => fa
            .Condition("createdon", ConditionOperator.LastXDays, 30)
            .Condition("emailaddress1", ConditionOperator.NotNull)
        )
        .Or(fo => fo
            .Condition("parentcustomerid", ConditionOperator.Equal, accountId)
            .Condition("address1_city", ConditionOperator.Equal, "Seattle")
        )
    )
    .OrderBy("lastname")
    .OrderBy("firstname");

Complex Filtering Using FilterBuilder Extensions

var query = Query.For("contact")
    .Select("firstname", "lastname", "emailaddress1")
    .Where(f => f
        .Equal("statecode", 0)
        .And(fa => fa
            .LastXDays("createdon", 30)
            .IsNotNull("emailaddress1")
        )
        .Or(fo => fo
            .Equal("parentcustomerid", accountId)
            .Equal("address1_city", "Seattle")
        )
    )
    .OrderBy("lastname")
    .OrderBy("firstname");

Joining Entities (Link Entities)

var query = Query.For("opportunity")
    .Select("name", "estimatedvalue", "closeprobability")
    .Where(f => f
        .Condition("statecode", ConditionOperator.Equal, 0)
    )
    .Link("account", "customerid", "accountid", JoinOperator.Inner, link => {
        link.Select("name", "accountnumber")
            .As("account")
            .Where(f => f
                .Condition("statecode", ConditionOperator.Equal, 0)
            );
    })
    .Link("contact", "customerid", "contactid", JoinOperator.LeftOuter, link => {
        link.Select("fullname", "emailaddress1")
            .As("contact");
    });

Advanced Join Extensions

The library exposes helper extension methods (in QueryBuilderExtensions) for additional Dataverse JoinOperator values. These allow more expressive and often more performant queries in certain scenarios.

Note: Some of these operators can significantly change the result semantics and/or execution plan. Always validate with real data and inspect the generated FetchXML (via ToFetchExpression) or use DebugView() for clarity.

When to Use Which Advanced Join

• Use CrossApplyJoin to fetch a single "primary" child row (e.g., latest activity) without bringing an entire child set.
• Use ExistsJoin / AnyJoin when you want to check for the presence of related rows without needing their data.
• Use NotAnyJoin to enforce absence of related data (e.g. accounts without open opportunities).
• Use InJoin when a sub-select style inclusion might be faster than a traditional join (test vs inner join).
• Use AllJoin when you need parent rows where all related rows fail a condition expressed in the filters (Dataverse implements this as “all related but none meet filter X”).

Code Examples

1. Any vs NotAny (Presence / Absence)

// Accounts that HAVE at least one active contact
var accountsWithActiveContact = Query.For("account")
    .AnyJoin("contact", "accountid", "parentcustomerid", link => link
        .Where(f => f.Equal("statecode", 0))
    );

// Accounts that have NO active contact
var accountsWithoutActiveContact = Query.For("account")
    .NotAnyJoin("contact", "accountid", "parentcustomerid", link => link
        .Where(f => f.Equal("statecode", 0))
    );

2. Exists vs In

// Accounts that have at least one open opportunity (using EXISTS)
var accountsWithOpportunitiesExists = Query.For("account")
    .ExistsJoin("opportunity", "accountid", "parentaccountid", link => link
        .Where(f => f.Equal("statecode", 0))
    );

// Accounts using IN-based restriction (platform may choose different plan)
var accountsWithOpportunitiesIn = Query.For("account")
    .InJoin("opportunity", "accountid", "parentaccountid", link => link
        .Where(f => f.Equal("statecode", 0))
    );

3. CrossApply (First Matching Child)

// Get account with only ONE (first) recent task (improves performance vs full child set)
var accountsWithOneTask = Query.For("account")
    .CrossApplyJoin("task", "accountid", "regardingobjectid", link => link
        .Select("subject", "scheduledend")
        .OrderByDesc("scheduledend")  // Ensure desired ordering for "first"
        .Top(1) // (Optional) If you later add Top to the link (manual pattern)
    );

Dataverse’s cross-apply logic tries to retrieve just the first qualifying child row; ensure your sorting reflects the intended “first”.

4. All / NotAll Semantics

// Accounts where related opportunities exist, but NONE of those are still open
var accountsWhereAllOpportunitiesClosed = Query.For("account")
    .AllJoin("opportunity", "accountid", "parentaccountid", link => link
        .Where(f => f.Equal("statecode", 0)) // Filter defines "open" – ALL join returns parents where no linked rows satisfy this
    );

// NotAll is effectively Any (platform quirk)
var accountsWithAnyOpenOpportunity = Query.For("account")
    .NotAllJoin("opportunity", "accountid", "parentaccountid", link => link
        .Where(f => f.Equal("statecode", 0))
    );

5. Combining Multiple Advanced Joins

var complex = Query.For("account")
    .Select("name")
    .AnyJoin("contact", "accountid", "parentcustomerid", c => c
        .Where(f => f.Equal("statecode", 0))
        .Select("fullname")
    )
    .NotAnyJoin("opportunity", "accountid", "parentaccountid", o => o
        .Where(f => f.Equal("statecode", 0)) // Has no active opportunities
    )
    .CrossApplyJoin("task", "accountid", "regardingobjectid", t => t
        .Select("subject")
        .OrderByDesc("createdon")
    );

Performance Tips

Use DebugView() during development:

var debug = complex.DebugView();
Console.WriteLine(debug);

Pagination & Async

// Get a specific page
var page2 = query.RetrieveMultiple(service, pageNumber: 2, pageSize: 50);

// Retrieve all pages automatically
var allResults = query.RetrieveMultipleAllPages(service);

// Using async version
var results = await query.RetrieveMultipleAsync(service);

// Async with pagination
var pageResults = await query.RetrieveMultipleAsync(service, pageNumber: 2, pageSize: 50);

// Async all pages
var allAsyncResults = await query.RetrieveMultipleAllPagesAsync(service);

FetchXML Conversion

// Convert QueryExpression to FetchXML
var fetchXml = query.ToFetchExpression(service);

Working with Results

// Convert to list
var entities = results.ToList();

// Filter results
var filteredEntities = results.Where(e => e.Contains("emailaddress1"));

// Project to new form
var names = results.Select(e => e.GetAttributeValue<string>("name"));

// Get first matching entity
var matchingContact = results.FirstOrDefault(e => 
    e.GetAttributeValue<string>("emailaddress1")?.Contains("example.com") == true);

// Safe attribute access
string name = entity.TryGet<string>("name", "Default Name");

API Reference

Query

Entry point for building queries:

• Query.For(entityName) - Creates a new query for the specified entity

QueryExpressionBuilder

Methods for configuring the main query:

• Select(params string[] attributes) - Specifies columns to include
• SelectAll() - Includes all columns
• Where(Action<FilterBuilder> filterConfig) - Adds a filter group using fluent configuration
• OrderBy(attribute) - Adds ascending a sort order
• OrderByDesc(attribute) - Adds descending a sort order
• Link(toEntity, fromAttribute, toAttribute, joinType, Action<LinkEntityBuilder> linkBuilder) - Adds a join
• Top(count) - Limits the number of records
• Distinct() - Returns only distinct records
• NoLock() - Uses NOLOCK hint
• QueryHint(hint) - Adds a query hint
• ForceSeek(indexName) - Forces using a specific index

Execution Methods

• RetrieveMultiple(service)
• RetrieveMultiple(service, pageNumber, pageSize)
• RetrieveMultipleAllPages(service)
• Exists(service)
• RetrieveMultipleAsync(service, CancellationToken cancellationToken = default)
• RetrieveMultipleAsync(service, pageNumber, pageSize, CancellationToken cancellationToken = default)
• RetrieveMultipleAllPagesAsync(service, CancellationToken cancellationToken = default)
• ExistsAsync(service, CancellationToken cancellationToken = default)
• ToQueryExpression()
• ToFetchExpression(service)

FilterBuilder

Builds complex filter logic:

• Condition(attribute, operator, value)
• And(Action<FilterBuilder> nested)
• Or(Action<FilterBuilder> nested)
• ToExpression()

FilterBuilder Extensions (Syntactic Sugar)

Convenience methods mapping to common ConditionOperator values. They improve readability and reduce verbosity.

Example:

var q = Query.For("contact")
    .Select("firstname", "lastname", "emailaddress1", "createdon")
    .Where(f => f
        .Equal("statecode", 0)
        .IsNotNull("emailaddress1")
        .LastXDays("createdon", 30)
        .Or(o => o
            .Like("emailaddress1", "%@example.com")
            .In("address1_city", "Seattle", "London", "Berlin")
        )
    );

LinkEntityBuilder

Configures join/link entities:

• Select(params string[] attributes)
• SelectAll()
• As(alias)
• OrderBy(attribute)
• OrderByDesc(attribute)
• Where(Action<FilterBuilder> filterConfig)
• Link(toEntity, fromAttribute, toAttribute, joinType, Action<LinkEntityBuilder> linkBuilder)

Advanced Join Helpers (QueryBuilderExtensions)

• InnerJoin(...)
• LeftOuterJoin(...)
• NaturalJoin(...)
• CrossApplyJoin(...)
• InJoin(...)
• ExistsJoin(...)
• AnyJoin(...)
• NotAnyJoin(...)
• AllJoin(...)
• NotAllJoin(...)

Extension Methods (LINQ-like)

• ToList() / ToArray()
• FirstOrDefault(predicate)
• SingleOrDefault(predicate)
• Where(predicate)
• Select(selector)
• TryGet<T>(attributeName, defaultValue)
• DeepClone()
• ShallowClone()
• CloneForPagination()
• DebugView()

Module Coverage

Overall Coverage

Test Summary

• Total Tests: 180
• Failed: 0
• Succeeded: 180
• Skipped: 0
• Duration: 2.5s

License

This project is licensed under the MIT License - see the LICENSE file for details.

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.