Dynamic queries
Creating a XrmDataContext
We need to create a XrmDataContext instance before we start quering.
Normal
CrmService sdk = Connection.Create("http://host.com:port/mscrmservices/2007/crmservice.asmx",
"OrgName", "Domain", "UserName", "Password");
XrmDataContext context = new XrmDataContext(sdk);
Inside a Plugin
public class SamplePlugin : IPlugin
{
public void Execute(IPluginExecutionContext executionContext)
{
ICrmService isdk = executionContext.CreateCrmService(true); // use current user
XrmDataContext context = new XrmDataContext(isdk);
}
}
Inside a Workflow
public class SampleWorkFlow : SequenceActivity
{
protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
{
IContextService contextService = (IContextService)executionContext.GetService(typeof(IContextService));
IWorkflowContext context = contextService.Context;
ICrmService isdk = context.CreateCrmService(false); // not as administrator
XrmDataContext context = new XrmDataContext(isdk);
return ActivityExecutionStatus.Closed;
}
}
IMPORTANT: By default the provider will use QueryExpressions, QueryExpressions have limitations with some queries, specially joins and selecting multiple attributes from linked entities, therefore we recommend you using FetchXml.
CrmDataContext context = new CrmDataContext(sdk);
context.QueryThrough = XrmLinq.QueryThroughType.FetchXml;
Available data types
These are the data types you can use with dynamic queries, table below shows which .net data type maps to which crm data type.
Uniqueidentifier, Lookup, Owner, Customer, Key
String, Memo, State
CrmDateTime
CrmNumber, Picklist, Status
CrmMoney, CrmDecimal
CrmFloat
CrmBoolean
Simple select
var accounts = (from a in context["account"] orderby a.Attribute<string>("name") select a).ToList();
Anonymous selectors
Select specific columns from an entity. Please note that you are not able to retrieve columns from 1:N or N:1 links.
var accounts = (from a in context["account"]
where a.Attribute<string>("name").StartsWith("A")
select new
{
AccountName = a.Attribute<string>("name"),
CreatedOn = a.Attribute<DateTime>("createdon")
}).ToList();
Project to existing class
Select specific columns from an entity and then project it onto an existing class; you can project to your own class or project it onto a class that has been generated by the Entity Mapper.
var accounts = (from a in context["account"]
where a.Attribute<string>("name").StartsWith("A")
select new Account
{
Name = a.Attribute<string>("name"),
CreatedOn = a.Attribute<DateTime>("createdon")
}).ToList();
Simple where condition
var accounts = (from a in context["account"]
// .Contains, .EndsWith, .StartsWith & .Equals are supported
where a.Attribute<string>("name").StartsWith("My")
select a).ToList();
Skip & Take
var accounts = (from a in context["account"]
orderby a.Attribute<string>("name")
select a).Skip(10).Take(10).ToList(); // skip 10 records and take the next 10
Complex where conditions
You can use a combination of AND OR expressions, we automatically work out the QueryExpression.
var results = (from c in context["contact"]
where (c.Attribute<string>("fullname").Contains("a") ||
c.Attribute<string>("emailaddress1").EndsWith("microsoft.com")) &&
c.Attribute<string>("website").Contains("microsoft.com")
orderby c.Attribute<string>("fullname")
select c).ToList();
Where condition on a N:1 linked entity
Be careful when you use where conditions on joined entities, CRM SDK is not as flexible as SQL, therefore some queries will not give you the expected result.
var results = (from c in context["contact"]
join a in context["account"] on c.Attribute<Guid>("parentcustomerid") equals a.Attribute<Guid>("accountid")
where a.Attribute<string>("name") == "My Account"
select c).ToList();
Where condition using an enumeration
var results = (from c in context["contact"]
join a in context["account"] on c.Attribute<Guid>("parentcustomerid") equals a.Attribute<Guid>("accountid")
where a.Attribute<int>("industrycode") == 1
select c).ToList();
Where condition using datetime
When working with DateTime types we only support >= and <=. CRM does not have operators for > and <.
var results = (from c in context["contact"]
where c.Attribute<DateTime>("createdon") >= DateTime.Now.AddDays(-7) &&
c.Attribute<DateTime>("createdon") <= DateTime.Now
orderby c.Attribute<DateTime>("createdon") descending
select c).ToList();