Two useful LINQ Queries

In order to run these queries you’ll have to install LINQPad and LINQPad Driver for CRM.

Query 1 – Who created the entities?

It is not possible to look at an entity and find out who created it. You can however use the createdby on the SavedQuery (System View) to find out this information, as the System View is created the same time as the entity is created and also contains the createdby user information.

from q in
(from s in SavedQuerySet.AsEnumerable()
 where s.QueryType == 0
 group s by s.ReturnedTypeCode
into g
 select new
 {
	 g.Key,
	 Views = (from s in SavedQuerySet
			  where s.ReturnedTypeCode == g.Key && s.QueryType == 0
			  orderby s.CreatedOn
			  select new { s.CreatedOn, s.CreatedBy }).First()
 })
orderby q.Views.CreatedOn descending
select new { q.Key, CreatedOn = q.Views.CreatedOn.Value.ToLocalTime(), q.Views.CreatedBy.Name }

Entity creation query.png

Query 2 – All Plugins with message, stage, filtering attributes and entity

You can use this query to get a quick snapshot of all the plugins in the system.

from m in SdkMessageProcessingStepSet
join f in SdkMessageFilterSet on m.SdkMessageFilterId.Id equals f.SdkMessageFilterId
join s in SdkMessageSet on f.SdkMessageId.Id equals s.SdkMessageId
join p in PluginTypeSet on m.PluginTypeId.Id equals p.PluginTypeId
where f.IsCustomProcessingStepAllowed.Value
&& !m.IsHidden.Value
&& m.CustomizationLevel.Value == 1
select new { Message = s.Name, Rank = m.Rank.Value, Stage = m.Stage, StageName = m.FormattedValues["stage"], m.FilteringAttributes, p.AssemblyName, PluginName= p.Name, StepName = m.Name, StepDescription = m.Description, Status = m.StatusCode, StatusName = m.FormattedValues["statuscode"]}

plugins

Quicktip: Linq query with Optionset condition

LinqPad is a great tool to quickly query CRM and export the records you want, for further processing. LinqPad Driver for Dynamics CRM is what this post is about – specifically on how to use “FormattedValues” in your queries. There are formatted values for Money, Optionset and DateTime. I will demonstrate on how to use Optionset text values in where condition.

For example this is how you can query all the open opportunities.

from q in OpportunitySet.AsEnumerable()
where q.FormattedValues["statecode"].ToLower() == "open"
select new { q.CreatedOn, q.Id, StateCodeName = q.FormattedValues["statecode"], q.StateCode }

The critical bit here is the “AsEnumerable”. If you don’t convert your set to an Enumerable, you would get this error.

image

There is a another LinqPad Driver for CRM (https://github.com/kenakamu/CRMLinqPadDriverWebAPI), but you wont be able to run similar query as WebAPI doesn’t seem to be exposing the “FormattedValues” property for each record.