Is it possible to express this SQL command as a QueryExpression (requires entity attribute comparison)?

(0) Share

Report

Posted on by Community Member

Hi,

I need to create a BulkDeleteRequest and the QueryExpression (i.e. the BulkDeleteRequest's QuerySet) required looks like this when expressed in SQL:

SELECT *
FROM	[dbo].[vdi_contactrecord] as contactrecord 
INNER JOIN [dbo].[vdi_contactdataset] as dataset
ON dataset.vdi_contactdatasetId = contactrecord.vdi_contactdatasetnameguid
WHERE
	contactrecord.CreatedOn < DATEADD(day,-7,GETDATE())
	AND
	( dataset.vdi_date <> contactrecord.vdi_versiondate
	OR dataset.vdi_version <> contactrecord.vdi_version)

(There will also need to be an additional clause to check the BusinessUnit.)

Is it possible to express the above as a QueryExpression? I can already do the join, the BU check and, I think, the date comparison. The problem I keep running into are those 2 last clauses: I can find no way to compare an attribute in the first entity with an attribute in the second (linked) entity. It seems like this basic SQL functionality is entirely missing from QueryExpression - or did I miss something?

Are there alternative approaches that I should consider?

*This post is locked for comments

All responses (14)

Answers (2)

Sort by

crm development 870 on at

Like (0)

Report
Copy link

Link copied!

You can use LINQ to achieve this.

docs.microsoft.com/.../sample-retrieve-multiple-with-condition-operators-using-linq

Thanks,

Anand
Community Member on at

Like (0)

Report
Copy link

Link copied!

Thanks for the response Anand.

To implement the SELECT? But how can you pass a LINQ query to BulkDeleteRequest, which is used to perform clean-up daily?
Community Member on at

Like (0)

Report
Copy link

Link copied!

Another option that occurred to me was to try to write this in FetchXML and then convert that to a QueryExpression, perhaps using Microsoft's FetchXmlToQueryExpressionRequest Class [ ref. https://docs.microsoft.com/en-us/previous-versions/dynamicscrm-2016/developers-guide/gg307989(v%3Dcrm.8) ]. However, FetchXML has its own limitations (although less than the popular SQL-to-FetchXML web tool suggests) and I have the impression that QueryExpression simply cannot compare attributes from different entities.
Verified answer

Guido Preite 54,086 Moderator on at

Like (0)

Report
Copy link

Link copied!

as far as I know you can't create that kind of query (put field values in the right side of the conditions)
crm development 870 on at

Like (1)

Report
Copy link

Link copied!

Hi Zeek,

For better performance, I would suggest you to use ExecuteMultipleRequest instead of BulkDeleteRequest.

In below code, you need to pass EntityReference collection retrieved using LINQ query. Below sample code is present in SDK.

/// <summary>

/// Call this method for bulk delete

/// </summary>

/// <param name="service">Org Service</param>

/// <param name="entityReferences">Collection of EntityReferences to Delete</param>

public static void BulkDelete(IOrganizationService service, DataCollection<EntityReference> entityReferences)

{

// Create an ExecuteMultipleRequest object.

var multipleRequest = new ExecuteMultipleRequest()

{

// Assign settings that define execution behavior: continue on error, return responses.

Settings = new ExecuteMultipleSettings()

{

ContinueOnError = false,

ReturnResponses = true

},

// Create an empty organization request collection.

Requests = new OrganizationRequestCollection()

};

// Add a DeleteRequest for each entity to the request collection.

foreach (var entityRef in entityReferences)

{

DeleteRequest deleteRequest = new DeleteRequest { Target = entityRef };

multipleRequest.Requests.Add(deleteRequest);

}

// Execute all the requests in the request collection using a single web method call.

ExecuteMultipleResponse multipleResponse = (ExecuteMultipleResponse)service.Execute(multipleRequest);

}
RaviKashyap 55,410 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi Zeek,

based on this sample, you can specify criteria for the linked entities as well. Try this:

docs.microsoft.com/.../gg327709(v%3Dcrm.8)

Hope this helps.
Community Member on at

Like (0)

Report
Copy link

Link copied!

Thanks Guido. Yes, that is my understanding too, after checking the class methods and Googling for information. This might become the "Suggested Answer" :(
Community Member on at

Like (0)

Report
Copy link

Link copied!

Thanks Ravi. I am already using Criteria - and Filters and Conditions - but frustratingly none of these supports comparison of 2 attributes from different entities, as far as I can see.
Community Member on at

Like (0)

Report
Copy link

Link copied!

Thanks Anand. I googled ExecuteMultipleRequest and I think there could be some problems in using it instead of BulkDeleteRequest:

a. Does not appear to produce a regular, periodic clean-up System Job (as BulkDeleteRequest does).

b. Although some references (e.g. [View:https://docs.microsoft.com/en-us/previous-versions/dynamicscrm-2016/developers-guide/jj863631(v=crm.8):750:50] recommend ExecuteMultipleRequest for improved performance, others recommend against using it from a

plugin (ref. [View:https://docs.microsoft.com/en-us/dynamics365/customer-engagement/guidance/server/avoid-batch-requests-plugin:750:50] ), which is our preferred implementation location currently, as it can lead to SQL time-outs. However, our approach is currently to create BulkDelete System Jobs ("cron jobs") with BulkDeleteRequest, so the actual deletion is not attempted in the plugin, just creation of the "cron job" that will later perform the deletion.

It may yet be necessary for us to take the approach you suggest (or similar) but, while it appears to solve the problem, it introduces a new problem: how then to call this LINQ-based bulk delete daily?
Community Member on at

Like (0)

Report
Copy link

Link copied!

My own thought to use FetchXML was thwarted, at least when using the XrmToolBox's FetchXML plugin, which also did not support comparing 2 attributes from different entities :( (It did however provide the means to quickly display the QueryExpression equivalent of any FetchXML you are able to produce in the UI, presumably using Microsoft's FetchXmlToQueryExpressionRequest Class. ) Also, not all of the current plugin's functionality is supported by CRM2011, which is apparent whenever you try to run unsupported functionality against you CRM org.