Client with Linq support for Microsoft Dynamics CRM (alternative to client from sdk)
When developing one of the projects, I needed integration with MS CRM ... Looking at the standard query mechanisms in msdn, I realized that it was a bit inconvenient, and since the project promises to be long and even endless (internal automation), I decided that using QueryExpression in a pure form will lead to a serious increase in labor costs and will become a breeding ground for careless mistakes (as the developers of the project will often change - whoever has the time will do it).
So, it was decided to write a wrapper over QueryExpression and add the ability to build fluent queries like in EF. Immediately I would like to say that when writing this wrapper (somewhere in the middle) I found a library from sdk which provides such an opportunity - sdk crm client, but after looking at it more closely I realized that there is no documentation (!!!) and several useful features, for example : use in in where, adding conditions to join and a few more smaller ones. I will give a comparative table later.
Since the project promises to be long, I still decided to finish my implementation ...
')
The project is an assembly, with just one additional dependency - microsoft.xrm.sdk.dll, which is easy to connect to the project.
The assembly provides an abstract base class for creating a client - CrmClientBase. This class has one abstract field that needs to be redefined:
IWcfCrmClient is an interface for interacting with a client reference added to a WCF project (Service Reference).
How to create a client class is better to show with an example (In most cases, it is enough just to copy it into a project, correct using and everything should work):
OrganizationServiceClient is a client from the Service Reference
To work with CRM entities, you need to apply them to classes (define a data contract). There are 2 attributes for this (standard attributes from the microsoft.xrm.sdk.dll assembly)
If attributes are not specified, then the class name / property name is used as the name of the entity / field in CRM.
Each data contract must be inherited from the base class CrmDataContractBase. This is an abstract class with one abstract property.
which you need to override and also mark the attribute AttributeLogicalName.
Example data contract:
IMPORTANT!
To replace CRM enums, you need to define a class, inherit it from CrmOptionsSetBase and mark it with the EntityLogicalName attribute, in which you specify the name of the enumeration in CRM:
CrmOptionsSetBase implements an IEnumerable interface of type CrmOption, i.e. they can immediately be used as a data source for controls.
The CrmOption class contains 2 properties:
The label contains the display name of the element, and Value is the same OptionSetValue used in the data contract CRM entities.
These are simple operations, everything should be clear from the example:
IMPORTANT! All changes are applied to CRM right away. No transaction (I haven't figured it out yet)
The client has a special method for receiving transfers.
Where T is the data contract listing. An example (data contarct is described above):
The namespace CrmClient.Linq defines the following extension methods for generating fluent requests to CRM:
The methods from this namespace begin with Crm, so that you can immediately see where a request to CRM is being formed and where work is being done with objects that have already been uploaded.
Starting method of forming a query to CRM - Query:
after it other methods of query formation can be used.
The request itself to the CRM is executed. when calling the GetEnumerator () method, that is, when trying to enumerate data (as in EF).
Anonymous type
Class (as in EF, a class must have a constructor without parameters)
IMPORTANT! For the 'in' construction, only an array needs to be passed. I will remove this restriction later.
There is also support for composite conditions (as in my extension for EF The “WHERE” condition for composite keys in the Entity Framework ):
The result will be sorted by Name asc, then by Id desc
With Join, things are a bit more complicated ... For example, you need to make users join their leaders:
The request will execute correctly. But if you take a closer look, the Id property of s.Chief is not buried anywhere, since this is the property of the EntityReference class ... But the s.Chief property itself is mapped onto the CRM 'parent systemuserid' we need ... The client resolves this situation by substituting the CRM request into the parent systemuserid 'from s.Chief, and the entry s => s.Chief.Id, d => d.Id is needed for type compatibility.
Another problem is specifying the conditions for a join request. In a CRM request, this condition is specified in the Link class itself, so in order to specify this condition, it must be specified in the join request itself:
_client.Query (). CrmWhere (u => u.Id == _directorUserId) - this is the condition for the join. Those. if this is an inner join, then only users whose director is with id _directorUserId will return. Here you can specify other conditions, for example Order, but this will have no effect, only the Where condition is taken into account.
This method is needed so that requests on the CRM side are executed with the with option (nolock) useful for obtaining data for reporting over the past period. Example:
CRM query supports paginated output of results. There is a method for paginated query
It returns immediately to the List, which means that its call immediately executes a request to CRM.
This method returns the total number of records, and a sign that there is still data to get on the CRM side. Example:
(All examples can be found in the test project)
The client uses reflection only to initially receive mapping data, and this data is cached in memory. Because of this, the first request will be slow ...
The code for instantiating classes is compiled dynamically, so that the time to get the result depends only on the execution time of the request on the CRM side and network delays. Type creation code is compiled into memory - one assembly per type. But there is one caveat: the creation of anonymous types occurs through the constructor through reflection. This is due to the fact that in different assemblies anonymous types have a different internal type and coercion is impossible . If anyone knows how to overcome this limitation, please write, I will correct it.
In terms of speed, this client and the client from the SDK are almost the same (the difference is within the network delays when performing the test). Due to the small amount of data on the test CRM, I did not find out whether the client from the SDK does everything on the CRM side or something already on the application side (for example, sorting ...), this is not understood from the test code, as it uses standard IQueryable and standard Linq extension methods.
Sam comparative test is in the source. The results of its implementation are as follows:
You can download the build and see the source codeplex - mscrmclient . Solution consists of 3 projects:
On the codeplex I wrote the documentation in English, the Russian version of the documentation - this article.
All the errors that I find in the process of using this client, I will immediately correct and upload the update to the codeplex. If you decide to use this client in your projects, and find an error, create an issue on the project page. I subscribed to receive notifications. I will try to correct all errors as soon as possible (it is in my interest, if only because I also use this client in my projects).
So, it was decided to write a wrapper over QueryExpression and add the ability to build fluent queries like in EF. Immediately I would like to say that when writing this wrapper (somewhere in the middle) I found a library from sdk which provides such an opportunity - sdk crm client, but after looking at it more closely I realized that there is no documentation (!!!) and several useful features, for example : use in in where, adding conditions to join and a few more smaller ones. I will give a comparative table later.
Since the project promises to be long, I still decided to finish my implementation ...
')
Tasks:
- Make it possible to define DataContract for the necessary CRM entities, so that when changing / deleting / adding fields you need to take this into account only in one place.
- Provide the ability to write fluent requests to CRM to support strong typing, and to identify the maximum number of problems from deleting or changing the field type at the compilation stage
- Provide the ability to customize the result set received from CRM so that it is convenient to minimize traffic (Select, Join, Where, etc.)
- Provide security and role-based access to data in CRM (ASP.NET Impersonation in my case)
What eventually happened:
The project is an assembly, with just one additional dependency - microsoft.xrm.sdk.dll, which is easy to connect to the project.
Customer
The assembly provides an abstract base class for creating a client - CrmClientBase. This class has one abstract field that needs to be redefined:
protected abstract IWcfCrmClient WcfClient { get; } IWcfCrmClient is an interface for interacting with a client reference added to a WCF project (Service Reference).
How to create a client class is better to show with an example (In most cases, it is enough just to copy it into a project, correct using and everything should work):
using System; using CrmClient; using Microsoft.Xrm.Sdk; using Microsoft.Xrm.Sdk.Query; using MsCrmClientTest.MSCRM; public class OrgCrmClient : CrmClientBase { private class WcfCrmClient : IWcfCrmClient { private OrganizationServiceClient _client; public Guid Create(Entity entity) { return _client.Create(entity); } public void Update(Entity entity) { _client.Update(entity); } public void Delete(string entityName, Guid id) { _client.Delete(entityName, id); } public EntityCollection RetrieveMultiple(QueryBase query) { return _client.RetrieveMultiple(query); } public OrganizationResponse Execute(OrganizationRequest request) { return _client.Execute(request); } public void Close() { _client.Close(); } public WcfCrmClient() { _client = new OrganizationServiceClient(); } } private IWcfCrmClient _wcfClient; protected override IWcfCrmClient WcfClient { get { if (_wcfClient == null) _wcfClient = new WcfCrmClient(); return _wcfClient; } } } OrganizationServiceClient is a client from the Service Reference
Mapping
To work with CRM entities, you need to apply them to classes (define a data contract). There are 2 attributes for this (standard attributes from the microsoft.xrm.sdk.dll assembly)
- EntityLogicalNameAttribute (string name) - defines the name of the entity in CRM
- AttributeLogicalNameAttribute (string name) - defines the name of the entity field in CRM
If attributes are not specified, then the class name / property name is used as the name of the entity / field in CRM.
Each data contract must be inherited from the base class CrmDataContractBase. This is an abstract class with one abstract property.
public abstract Guid Id { get; set; } which you need to override and also mark the attribute AttributeLogicalName.
Example data contract:
[EntityLogicalName("systemuser")] public class User : CrmDataContractBase { [AttributeLogicalName("systemuserid")] public override Guid Id { get; set; } [AttributeLogicalName("fullname")] public string Name { get; set; } [AttributeLogicalName("parentsystemuserid")] public EntityReference hief { get; set; } [AttributeLogicalName("caltype")] public OptionSetValue CALType } IMPORTANT!
- If the property is a link to another CRM entity (like hief in the example), it should be of type EntityReference
- If the property is a value from the CRM enumeration (as CALType in the example), it should be of type OptionSetValue
CRM transfers mapping
To replace CRM enums, you need to define a class, inherit it from CrmOptionsSetBase and mark it with the EntityLogicalName attribute, in which you specify the name of the enumeration in CRM:
[EntityLogicalName("connectionrole_category")] public class ConnectionRoleCategoryEnum : CrmOptionsSetBase { } CrmOptionsSetBase implements an IEnumerable interface of type CrmOption, i.e. they can immediately be used as a data source for controls.
The CrmOption class contains 2 properties:
public string Label { get; private set; } public OptionSetValue Value { get; private set; } The label contains the display name of the element, and Value is the same OptionSetValue used in the data contract CRM entities.
Customer use
Add, edit, delete CRM entities
These are simple operations, everything should be clear from the example:
[EntityLogicalName("new_nsi")] public class NSI : ICrmDataContract { [AttributeLogicalName("new_nsiid")] public override Guid Id { get; set; } [AttributeLogicalName("new_name")] public string Name { get; set; } } // var newnsi = new NSI { Name = "Test NSI" }; _client.Add(newnsi); // 'Add' 'Id' , EF // newnsi.Name = "Test NSI 2"; _client.Update(newnsi); // _client.Delete(newnsi); IMPORTANT! All changes are applied to CRM right away. No transaction (I haven't figured it out yet)
Getting CRM transfers
The client has a special method for receiving transfers.
public T OptionsSet<T>() Where T is the data contract listing. An example (data contarct is described above):
var optionSet = _client.OptionsSet<ConnectionRoleCategoryEnum>(); CRM queries using Linq
The namespace CrmClient.Linq defines the following extension methods for generating fluent requests to CRM:
- Crmselect
- Crmwehere
- Crmjoin
- Crmorder
- Crmpaging
- Crmdistinct
The methods from this namespace begin with Crm, so that you can immediately see where a request to CRM is being formed and where work is being done with objects that have already been uploaded.
Starting method of forming a query to CRM - Query:
public ICrmQueryable<T> Query<T>() after it other methods of query formation can be used.
The request itself to the CRM is executed. when calling the GetEnumerator () method, that is, when trying to enumerate data (as in EF).
Select
Anonymous type
var users = _client.Query<CrmUser>() .CrmSelect(u => new { u.Id, u.Name, Test = 1 }) .ToList(); Class (as in EF, a class must have a constructor without parameters)
var users2 = _client.Query<CrmUser>() .CrmSelect(u => new TestUser() { Id = u.Id, FullName = u.Name, Test = 1 }) .ToList(); Where
// var user = _client.Query<CrmUser>() .CrmWhere(i => i.Id == _directorUserId) .Single(); var list = new[] { _directorUserId }; // in var filteredUsers = _client.Query<CrmUser>() .CrmWhere(i => list.Contains(i.Id)) .ToList(); // not in filteredUsers = _client.Query<CrmUser>() .CrmWhere(i => !list.Contains(i.Id)) .ToList(); //like var users = _client.Query<CrmUser>().ToList(); var firstUser = users.First(i => i.Name.Contains(" ")).Name.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries); // like text% var user = _client.Query<CrmUser>() .CrmWhere(i => i.Name.StartsWith(firstUser[0])) .ToList(); // like %text user = _client.Query<CrmUser>() .CrmWhere(i => i.Name.EndsWith(firstUser[0])) .ToList(); // like %text% user = _client.Query<CrmUser>() .CrmWhere(i => i.Name.Contains(firstUser[0])) .ToList(); // not like text% user = _client.Query<CrmUser>() .CrmWhere(i => !i.Name.StartsWith(firstUser[0])) .ToList(); // not like %text user = _client.Query<CrmUser>() .CrmWhere(i => !i.Name.EndsWith(firstUser[0])) .ToList(); // not like %text% user = _client.Query<CrmUser>() .CrmWhere(i => !i.Name.Contains(firstUser[0])) .ToList(); IMPORTANT! For the 'in' construction, only an array needs to be passed. I will remove this restriction later.
There is also support for composite conditions (as in my extension for EF The “WHERE” condition for composite keys in the Entity Framework ):
var users = _client.Query<CrmUser>().ToList(); var directors = users.Where(u => u.Director != null).Select(u => new { u.Director.Id, u.Director.Name }).Take(2); var users2 = _client.Query<CrmUser>() .CrmWhere(ExpressionType.Or, directors, (u, d) => u.Id == d.Id && u.Name == d.Name, (pn, o) => { switch (pn) { case "Id": return o.Id; case "Name": return o.Name; default: return null; } }) .ToList(); Order
var users = _client.Query<CrmUser>() .CrmOrderBy(i => i.Name) .CrmOrderByDescending(i => i.Id) .ToList(); The result will be sorted by Name asc, then by Id desc
Distinct
var users = _client.Query<CrmUser>() .CrmDistinct() .ToList(); Join
With Join, things are a bit more complicated ... For example, you need to make users join their leaders:
var users = _client.Query<CrmUser>() .CrmJoin(_client.Query<CrmUser>(), s => s.Chief.Id, d => d.Id, (s, d) => new { s.Id, s.Name, ChiefId = d.Id, ChiefFullName = d.Name }) .ToList(); or var users = _client.Query<CrmUser>() .CrmLeftJoin(_client.Query<CrmUser>(), s => s.Chief.Id, d => d.Id, (s, d) => new { s.Id, s.Name, ChiefId = d.Id, ChiefFullName = d.Name }) .ToList(); The request will execute correctly. But if you take a closer look, the Id property of s.Chief is not buried anywhere, since this is the property of the EntityReference class ... But the s.Chief property itself is mapped onto the CRM 'parent systemuserid' we need ... The client resolves this situation by substituting the CRM request into the parent systemuserid 'from s.Chief, and the entry s => s.Chief.Id, d => d.Id is needed for type compatibility.
Another problem is specifying the conditions for a join request. In a CRM request, this condition is specified in the Link class itself, so in order to specify this condition, it must be specified in the join request itself:
var users = _client.Query<CrmUser>() .CrmJoin(_client.Query<CrmUser>().CrmWhere(u => u.Id == _directorUserId), s => s.Chief.Id, d => d.Id, (s, d) => new { s.Id, s.Name, ChiefId = d.Id, ChiefFullName = d.Name }) .ToList(); or var users = _client.Query<CrmUser>() .CrmLeftJoin(_client.Query<CrmUser>().CrmWhere(u => u.Id == _directorUserId), s => s.Chief.Id, d => d.Id, (s, d) => new { s.Id, s.Name, ChiefId = d.Id, ChiefFullName = d.Name }) .ToList(); _client.Query (). CrmWhere (u => u.Id == _directorUserId) - this is the condition for the join. Those. if this is an inner join, then only users whose director is with id _directorUserId will return. Here you can specify other conditions, for example Order, but this will have no effect, only the Where condition is taken into account.
Nolock
This method is needed so that requests on the CRM side are executed with the with option (nolock) useful for obtaining data for reporting over the past period. Example:
var users = _client.Query<CrmUser>() .CrmNoLock() .ToList(); Paging
CRM query supports paginated output of results. There is a method for paginated query
List<T> CrmGetPage(int pageNumber, int pageSize, out int totalCount, out bool moreRecordsExists) It returns immediately to the List, which means that its call immediately executes a request to CRM.
This method returns the total number of records, and a sign that there is still data to get on the CRM side. Example:
int total; bool moreExists; var users = _client.Query<CrmUser>().CrmGetPage(1, 10, out total, out moreExists); (All examples can be found in the test project)
Customer features
The client uses reflection only to initially receive mapping data, and this data is cached in memory. Because of this, the first request will be slow ...
The code for instantiating classes is compiled dynamically, so that the time to get the result depends only on the execution time of the request on the CRM side and network delays. Type creation code is compiled into memory - one assembly per type. But there is one caveat: the creation of anonymous types occurs through the constructor through reflection. This is due to the fact that in different assemblies anonymous types have a different internal type and coercion is impossible . If anyone knows how to overcome this limitation, please write, I will correct it.
Comparison with client from SDK
In terms of speed, this client and the client from the SDK are almost the same (the difference is within the network delays when performing the test). Due to the small amount of data on the test CRM, I did not find out whether the client from the SDK does everything on the CRM side or something already on the application side (for example, sorting ...), this is not understood from the test code, as it uses standard IQueryable and standard Linq extension methods.
Sam comparative test is in the source. The results of its implementation are as follows:
Operation ThisCrmClient SdkCrmClient Query 00:00:25.6005598 00:01:01.1291123 Select 00:00:03.5173517 00:00:03.6273627 Order 00:00:08.2558255 00:00:08.2338233 Where 00:00:04.1074107 00:00:03.9203920 WhereIn 00:00:05.3745374 not supported %Like% 00:00:03.3983398 00:00:03.4093409 %Like 00:00:03.4403440 00:00:03.4163416 Like% 00:00:03.3093309 00:00:03.3033303 Join 00:00:03.4313431 00:00:03.4143414 JoinFilter 00:00:03.3833383 not supported NoLock 00:00:09.6899689 not supported Distinct 00:00:07.9847984 00:00:08.0328032 Build and source
You can download the build and see the source codeplex - mscrmclient . Solution consists of 3 projects:
- MsCrmClient - the client itself
- MsCrmClientTest - tests for the client
- PerfomanceTest is a console application for comparing performance with client from SDK
Instead of conclusion
On the codeplex I wrote the documentation in English, the Russian version of the documentation - this article.
All the errors that I find in the process of using this client, I will immediately correct and upload the update to the codeplex. If you decide to use this client in your projects, and find an error, create an issue on the project page. I subscribed to receive notifications. I will try to correct all errors as soon as possible (it is in my interest, if only because I also use this client in my projects).
Source: https://habr.com/ru/post/177273/
All Articles