Linq is a simple way to query Microsoft Dynamics CRM. However, the Microsoft's Linq provider has many limitations and bugs. This render it unusable for the queries which go further than simple requests.
That's where the Akzin CRM Linq provider comes in handy. The Akzin Linq Provider is a drop-in replacement for querying CRM entities, and supports both early-bound and late-bound classes. It supports the same functionality as provided by the SDK Linq provider, solves the bugs and provides capabilities beyond.
Quick Start
The Akzin Linq Provider is available via nuget. It can be installed using the following Package Manager command:
PM> Install-Package Akzin.Crm.Linq
After you've installed the package, you simply import the namespace using Akzin.Crm.Linq
.
You are then ready to start using the Query
extension method on your IOrganizationService
instance.
The extension method has overloads for both early-bound and late-bound entities.
IOrganizationService crm = CreateCrmContext();
//using Akzin.Crm.Linq;
var query = from c in crm.Query<Contact>()
join acc in crm.Query<Account>()
on c.ParentCustomerId.Id equals acc.AccountId
where acc.Name == "akzin.com"
select new { a = acc, c };
var listOfAccountsAndContacts = query.ToList();
Supported features
Akzin CRM Linq includes support for the same queries as the SDK Linq provider, and also provides many more features. The feature-set is covered by more than hundred unit tests. Some of the unit tests' code is show-cased below to demonstrate some of the capabilities.
Complex left outer join
While the SDK Linq provider officially does not support left outer joins,
a single left outer join is supported.
However, when you want to go further and join some more tables,
you'll soon see a NotSupportedException
thrown at your screen.
var q = from c in crm.Query<Contact>()
join acc in crm.Query<Account>() on
c.ParentCustomerId.Id equals acc.AccountId
join su in crm.Query<SystemUser>()
on acc.PreferredSystemUserId.Id equals su.Id
select su;
Count
You can fire a query and only request the count to be returned. The SDK Linq provider does not have this capability, and will force you to download all data to the client, even if you're only interested in the statistics.
var q = from a in crm.Query<Account>()
where a.Name == "akzin.com"
select a;
var totalRecords = q.Count();
List contains
The list contains capability is a powerful tool that allows you to write efficient queries with the least server round-trips. Without this capability, your query would suffer from the so-called N+1 problem.
var l = new List{"John", "Pete"};
var q = from c in crm.Query<Contact>()
where l.Contains(c.Firstname)
select c;
q.ToList();
Use early-bound enums in filter and map functions
Akzin CRM Linq allows filtering on enum values without having to cast them to integers.
var q = from a in crm.Query<Account>()
where a.StatusCodeEnum == AccountStatuscode.Active
select a;
Distinct
You could use the Distinct()
to only return unique entities. The uniqueness filtering gets
hinted in the query to CRM, so that only a constrained number of bytes get send back from CRM over the wire.
var q = from c in crm.Query<Contact>()
join acc in crm.Query<Account>()
on c.ParentCustomerId.Id equals acc.AccountId
orderby c.Firstname, acc.Name descending
select c;
var uniques = q.Distinct().ToList();