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

Advertisements

3 thoughts on “Two useful LINQ Queries

  1. I was able to rewrite the first two queries for WebAPI but was unable to rewrite the third one. This is because “plugintypeid” of “sdkmessageprocessingsteps” always seems to return null, even though it is not. You can use

    from m in sdkmessageprocessingsteps.AsEnumerable()
    select new { m.plugintypeid, m.rank, m.stage, m.filteringattributes, m.ishidden, m._sdkmessagefilterid_value, m.customizationlevel, m.name, m.description, m.statuscode } into m2
    where m2.customizationlevel.Value == 1
    join f in sdkmessagefilters.AsEnumerable() on m2._sdkmessagefilterid_value equals f.sdkmessagefilterid.Value
    join s in sdkmessages.AsEnumerable() on f._sdkmessageid_value equals s.sdkmessageid.Value
    select new { message = s.name, rank = m2.rank.Value, stage = m2.stage, m2.filteringattributes,
    stepname = m2.name, stepdescription = m2.description, status = m2.statuscode }

    but it doesn’t include plugintype.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s