Strongly typed queries
NOTE: You can dynamically query crm once you have created a CrmDataContext by using the CrmDataContext indexer.
Creating a CrmDataContext
We need to create a CrmDataContext instance before we start quering.
Normal
CrmService sdk = Connection.Create("http://host.com:port/mscrmservices/2007/crmservice.asmx",
"OrgName", "Domain", "UserName", "Password");
CrmDataContext context = new CrmDataContext(sdk);
Inside a Plugin
public class SamplePlugin : IPlugin
{
public void Execute(IPluginExecutionContext executionContext)
{
ICrmService isdk = executionContext.CreateCrmService(true); // use current user
CrmDataContext context = new CrmDataContext(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
CrmDataContext context = new CrmDataContext(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;
IMPORTANT: Create, Update & Delete are now available from CrmDataContext (context variable above), please use it instead of CrmDataAccess.
Simple select
var accounts = (from a in context.Accounts orderby a.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.Accounts where a.Name.StartsWith("A")
select new
{
AccountName = a.Name,
CreatedOn = a.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.Accounts where a.Name.StartsWith("A")
select new Account
{
Name = a.Name,
CreatedOn = a.CreatedOn
}).ToList();
Create, Update & Delete
Account mine = new Account { AccountId = Guid.NewGuid(), Name = "My Account" };
CrmDataAccess.Create<Account>(sdk, mine);
// update
mine.WebSiteURL = "http://www.xrmlinq.com";
CrmDataAccess.Update<Account>(sdk, mine);
// delete
CrmDataAccess.Delete(sdk, mine.AccountId, Account.Fields.SchemaName);
Simple where condition
accounts = (from a in context.Accounts
// .Contains, .EndsWith, .StartsWith & .Equals are supported
where a.Name.StartsWith("My")
select a).ToList();
Skip & Take
accounts = (from a in context.Accounts
orderby a.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 complexContacts = (from c in context.Contacts
where (c.FullName.Contains("a") || c.EMailAddress1.EndsWith("microsoft.com")) &&
c.WebSiteUrl.Contains("microsoft.com")
orderby c.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.
complexContacts = (from c in context.Contacts
where c.Parentcustomer.Name == "My Account"
select c).ToList();
Where condition on multiple N:1 linked entities
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 emails = (from e in context.Emails
where e.AccountRegardingObject.PrimaryContact.CreatedBy.BusinessUnit.Name == "XrmLinq"
orderby e.CreatedOn descending
select e).ToList();
Where condition on multiple N:N linked entities
Querying native N:N is a bit difficult due to the hidden entity. Example below shows how you can retrieve members from a marketing list.
var members = (from a in context.Accounts join member in context["listmember"] on a.AccountId equals member.Attribute<Guid>("listmemberid") join list in context.Lists on member.Attribute<Guid>("listid") equals list.ListId where list.ListName == "Test" select a).ToList();
1. Since we’re wanting to get all the account members in a marketing list we start with the account
2. Then we join onto the hidden entity, which is “listmember” you can find the name of this hidden entity by going into the customizations area, open the N:N relationship, take a look at the relationship entity name at the bottom
3. Then we join onto the other side of the query, eg: Account <> (N:N) <> Marketing List
4. This is optional but I put a where clause so that we only get members from the Marketing List called “test”
5. Select the columns you need
Where condition using an enumeration
complexContacts = (from c in context.Contacts
where c.Parentcustomer.IndustryCode == Account.Enums.IndustryCode.Accounting
select c).ToList();
Where condition using datetime
When working with DateTime types we only support >= and <=. CRM does not have operators for > and <.
complexContacts = (from c in context.Contacts
where c.CreatedOn >= DateTime.Now.AddDays(-7) && c.CreatedOn <= DateTime.Now
orderby c.CreatedOn descending
select c).ToList();
Explicit Joins
var results = (from i in context.Invoices
join a in context.Accounts on i.CustomerId.Value equals a.AccountId
where a.Name.StartsWith("A")
select i.TotalAmount).ToList();
Left Joins
Left joins are useful when you want to retrieve the customer for opportunities, orders, quotes, invoices where the customer can be an account or a contact.
var orders = (
from order in context.SalesOrders
join a in context.Accounts on order.CustomerId.Value equals a.AccountId into ja
// DefaultIfEmpty makes it a left join
from account in ja.DefaultIfEmpty()
join c in context.Contacts on order.CustomerId.Value equals c.ContactId into jc
from contact in jc.DefaultIfEmpty()
select new
{
OrderId = order.SalesOrderId,
OrderNumber = order.OrderNumber,
TotalAmount = order.TotalAmount,
AccountName = account.Name,
ContactName = contact.FullName
}).ToList();
FetchXml
<fetch mapping="logical">
<entity name="salesorder">
<attribute name="salesorderid" />
<attribute name="ordernumber" />
<attribute name="totalamount" />
<link-entity name="account" from="accountid" to="customerid" link-type="outer" alias="a">
<attribute name="name" />
</link-entity>
<link-entity name="contact" from="contactid" to="customerid" link-type="outer" alias="c">
<attribute name="fullname" />
</link-entity>
</entity>
</fetch>
Annotation
Annotation note = new Annotation
{
AnnotationId = Guid.NewGuid(),
Subject = "subject",
NoteText = "note body",
IsDocument = false,
ObjectIdType = "contact" // schema name of the entity you are pointing to
ObjectTypeCode = "contact" // same as above
ObjectId = Guid.Empty, // point this at any entity that support notes
};
CrmDataAccess.Create<Annotation>(sdk, note);
Lookup
If the type is a lookup we create another property with the same name but suffix it with Type, you must specify the entity schema name for this property.
Email email = new Email
{
ActivityId = Guid.NewGuid(),
Subject = "subject",
RegardingObjectIdType = "contact" // schema name of the entity you are pointing to
RegardingObjectId = Guid.Empty, // point this at any entity that is supported
};
Create / Update Email Fields (To, Ccc, Bcc)
Email e = new Email
{
ActivityId = Guid.NewGuid(),
Subject = "Subject line",
MimeType = "text/html",
DirectionCode = true,
RegardingObjectIdType = Account.Fields.SchemaName, // set regarding if needed
RegardingObjectId = Guid.Empty,
TrackingToken = "",
Description = "Description"
};
// IMPORTANT! must create a variable to hold the email address list
var to = new List<XrmLinq.Core.ActivityParty>();
to.Add(
new XrmLinq.Core.ActivityParty
{
PartyIdType = Contact.Fields.SchemaName, // must set the type first
PartyId = Guid.Empty
});
var from = new List<XrmLinq.Core.ActivityParty>();
from.Add(
new XrmLinq.Core.ActivityParty
{
PartyIdType = SystemUser.Fields.SchemaName, // must set the type first
PartyId = _context.WhoAmI().UserId
});
e.To = to; // set the property values
e.From = from;
_context.Create<Email>(e); // create the email in crm
SendEmailRequest request = new SendEmailRequest
{
EmailId = e.ActivityId,
IssueSend = true,
TrackingToken = ""
};
_context.Sdk.Execute(request); // send the email
Complex FetchXml
var results = (from invoice in _context.Invoices
// assume the invoice has been created on an account
join customerid in _context.Accounts on invoice.CustomerId.Value equals customerid.AccountId
// find the sales order of this invoice
join salesorderid in _context.SalesOrders on invoice.SalesOrderId equals salesorderid.SalesOrderId
// join the order onto a custom entity
join xrm_jobid in _context.Jobs on salesorderid.JobId equals xrm_ordersid.JobId
// join the custom entity above (Job) to a custom attribute called 'invoiceto'
join xrm_invoicetoid in _context.Contacts on xrm_ordersid.InvoicetoId equals xrm_invoicetoid.ContactId
// get all fulfilled orders
where salesorderid.StatusCode == 100001
orderby customerid.Name
select new
{
InvoiceId = invoice.InvoiceId,
InvoiceNumber = invoice.InvoiceNumber,
Amount = invoice.TotalAmount.HasValue ? invoice.TotalAmount.Value : 0,
OrderId = salesorderid.SalesOrderId,
ClientId = customerid.AccountId,
Client = customerid.Name,
InvoiceToId = xrm_invoicetoid.ContactId,
EmailTo = xrm_invoicetoid.EMailAddress1,
EmailToFirstName = xrm_invoicetoid.FirstName,
}).ToList();
}