The latest update to the MSCRM SDK included a new LINQ provider. In this blog post we are going to compare and contrast querying for data by owner ID using the old and new SDK techniques. If you are new to the LINQ provider then you should read my quick start blog post here first.
For this example let’s assume I want to retrieve accounts where the owner ID is the current user. The following is straight from the CRM SDK and uses the lower level API calls. Keep reading past it and I will use the new LINQ provider to accomplish the same thing plus a little bit.
Guid principalId = user.UserId;
// Create a column set holding the names of the columns to be retrieved.
ColumnSet colsPrincipal = new ColumnSet();
// Set the properties of the column set.
colsPrincipal.Attributes = new string[] { "name", "accountid" };
// Create a ConditionExpression.
ConditionExpression conditionPrincipal = new ConditionExpression();
// Set the ConditionExpressions properties so that the condition is true when the
// ownerid of the account equals the principalId.
conditionPrincipal.AttributeName = "ownerid";
conditionPrincipal.Operator = ConditionOperator.Equal;
conditionPrincipal.Values = new object[1];
conditionPrincipal.Values[0] = principalId;
// Create the FilterExpression.
FilterExpression filterPrincipal = new FilterExpression();
// Set the properties of the FilterExpression.
filterPrincipal.FilterOperator = LogicalOperator.And;
filterPrincipal.Conditions = new ConditionExpression[] { conditionPrincipal };
// Create the QueryExpression.
QueryExpression queryPrincipal = new QueryExpression();
// Set the properties of the QueryExpression.
queryPrincipal.EntityName = EntityName.account.ToString();
queryPrincipal.ColumnSet = colsPrincipal;
queryPrincipal.Criteria = filterPrincipal;
// Create the request object.
RetrieveMultipleRequest retrievePrincipal = new RetrieveMultipleRequest();
// Set the properties of the request object.
retrievePrincipal.Query = queryPrincipal;
Now that we have looked at how we would have done this in the past, let’s look at what is possible using the new LINQ provider. First, you should have already read my other blog post where I talked about how to call Who Am I to get the current user ID here. For this example we startup assuming that has already been accomplished.
To make this a little more interesting, in addition to just retrieving the accounts for the user I also want to retrieve the user’s name and only the first 10 accounts.
The first thing we are going to do is build the LINQ expression that will provide the criteria for our query. The following queries for accounts that have an owner ID matching. It also shapes the results into an anonymous type that only has the properties ID and Name. Pause for a second and compare the following 7 lines, which could really be expressed as 1 line to the code we used above without the LINQ provider.
var principalQuery = from acct in ctx.accounts
where acct.ownerid.Value == currentUserID
select new
{
ID = acct.accountid,
Name = acct.name
};
Remember, the above just defines the LINQ expression it doesn’t cause execution of the query against the CRM server that will happen later when we start to enumerate the results. Next, let’s go get the user information so we can grab the full name of the user.
var userInfo =
ctx.systemusers.Where(u => u.systemuserid == currentUserID).FirstOrDefault();
| If we retrieved a user the output the user name. |
| if (userInfo != null) Console.WriteLine("First 10 Accounts for user " + userInfo.fullname); Next, we are going to enumerate the accounts and print the account name. Notice that we added a Take(10) onto the LINQ query we built earlier. This will cause only the first 10 records to be used. foreach (var acct in principalQuery.Take(10)) Console.WriteLine(" Account Name :" + acct.Name); Hopefully it’s clear from the example the new CRM LINQ provider that’s part of the updated SDK makes it much easier to build queries against the CRM data. |