Writing fluent OrganizationService queries

Scope

Programming in the Dynamics environment is often very data driven by its nature.

Because of this, most applications and plugins we write need to retrieve data from CRM, for processing it.

Usually we use QueryExpressions or FetchXml for doing so. When programming early bound, the OrganizationServiceContext becomes an option as well.

What I want to introduce to you, is FluentQuery. It’s an internal DSL (domain specific language), that I created for writing more fluent QueryExpressions. It works for programming late bound as well as early bound.

In this post we’re going to see some examples for using it, define where it can help us and how to get it.

 

Getting it

Since I wanted to be able to use it everywhere, in plugins as well as in external applications, I published it as Nuget Source package.

That means that the source code (one file) is added directly to your project, so that it is completely native to your application.

You can install it from NuGet.

 

Using it

FluentQuery is an extension method to the IOrganizationService.

You just need to make sure to add usings for `Xrm.Oss.FluentQuery` and the Query extension methods show up.

You can call it with generic parameters for receiving early bound records, or use entity or the non generic query overload for retrieving plain entity records.

A simple query using FluentQuery could look as follows:

var accounts = service.Query("account")
                .IncludeColumns("name")
                .Where(e => e
                    .Attribute(a => a
                        .Named("name")
                        .Is(ConditionOperator.Equal)
                        .To("Adventure Works")
                    )
                )
                .RetrieveAll();

This retrieves all accounts that have a name equal to “Adventure Works” including the name and address1_line1 columns and returns them as early bound Account objects.

It retrieves all records that are found, you don’t have to take care of paging.

As you can see, the syntax is designed to be as fluent as possible, so that queries sound nearly like sentences.

FluentQuery offers the whole feature set of QueryExpressions, you can also add LinkEntities and complex filters.

But there’s even more: FluentQuery does not only automatically take care of paging for you, it also can handle caching.

Just create a MemoryCache in your application and pass it as UseCache option and FluentQuery automatically retrieves and stores data automatically from and to it.

Sample:

var isoCodeDe = service.Query("oss_country")
                .IncludeColumns("name")
                .Where(e => e
                    .Attribute(a => a
                        .Named("oss_isocode")
                        .Is(ConditionOperator.Equal)
                        .To("de-de")
                    )
                )
                .UseCache(memoryCache, new DateTimeOffset(DateTime.UtcNow.AddHours(1)))
                .RetrieveAll();

This retrieves the country record(s) with iso code “de-de” and caches the result for an hour, so that the next query does not need to call to CRM for retrieving the information.

There are some considerations however:

  • Use for mostly static data only, otherwise you might work on old data if it changes often and your cache life time is somewhat high.
  • Do use this only for non security relevant data. When the result is stored in the cache, the next query execution takes the result and proceeds, no matter if the user of the current user context had enough security permissions for retrieving the data or not.

Check out the GitHub page for the ReadMe and the test cases for seeing more examples.

Summary

I hope I was able to show you, how readable queries can get by using FluentQuery.

In addition to that, you can also benefit from automatic caching and automatic paging if you want to.

I’d be happy to see this in use, if you’re having any issues, please report them on the issues GitHub page.

Leave a Reply

Your email address will not be published.

RELATED POST

Entity updates using change tracker

Scope When working with entity records in plugins and tools, we almost always need to update the records. For updating…