Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Strange Query Issue From Plugin

Jump to Best Answer
  • 1.  Strange Query Issue From Plugin

    TOP CONTRIBUTOR
    Posted Apr 07, 2020 02:00 PM
    I have run across a very strange issue (at least to me) in one of my plugins.  Any help or insight would be GREATLY appreciated.

    System Info
    • V9.0
    • Op-Prem

    Plug-In Info
    • Entity: Business (account entity renamed)
    • Other Involved Entity: Client  (child(ren) of Business entity)
    • Trigger - Pre-Operation:  Delete (of Business record)
    • Desired action:  When a user attempts to delete a Business record, I want to check to see if the Business has any Client records underneath it.  If it does, then I throw an exception to inform the user and abort the deletion.
    Problem
    The problem is that when this plugin runs Pre-Operation an a Business that has at least one client record, the code below returns 0 records.  If I run the plugin (Pre-Validation), the code returns the correct number of client records.  A vendor of ours has been able to reproduce the same results and we can't figure out why this is happening. The plugin runs under the system admin creds as in most of our plugins.  Another function in the plugin does the same query on related contacts with the same issue.

    Is there something about the new version query that I'm missing (coming from 8.2)?  I'm not a query expression expert by any means, so am I doing something wrong there?

    Code
    Private Function ContainsClients() As Boolean
    ContainsClients = False

    Dim query = New QueryExpression("vs360_accountdata")
    query.ColumnSet = New ColumnSet({"vs360_accountdataid"})
    query.NoLock = True
    query.Criteria = New FilterExpression(LogicalOperator.And)
    With query.Criteria
    .AddCondition("vs360_accountid", ConditionOperator.Equal, triggerInfo.PreImage.Id)
    End With
    Dim currClients = util.crmService.RetrieveMultiple(query).Entities.Count
    If currClients > 0 Then
    ContainsClients = True
    End If
    End Function


    ------------------------------
    Glen Wolinsky
    Lead Applications Developer
    Miller & Martin PLLC
    Chickamauga GA
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Strange Query Issue From Plugin

    TOP CONTRIBUTOR
    Posted Apr 08, 2020 12:11 PM
    Edited by Ryan Perry Apr 08, 2020 12:19 PM
    Hi Glen,

    ... Are you using a custom account entity? If you are trying to retrieve child accounts, I'd think you'd be creating new QueryExpression("account").

    Have a look at these two forum's if you haven't run across them:
    https://community.dynamics.com/crm/f/microsoft-dynamics-crm-forum/315118/pre-image-for-delete-message?pifragment-97030=1
    https://nishantrana.me/2018/11/05/plugin-on-pre-validation-stage-in-dynamics-365-ce/

    Per these posts, the DELETE message pre image appears to store only the entity reference, not the full entity.  My guess is that, once you've done pre-validation, you only need the entityreference to do the delete, so MSFT opted to not pass the full entity to the next stage. If this is the case, and you are attempting to get the vs360_accountid from the entity itself, it may be null, interfering with your query results.   If you get it from the entityreference instead, that may fix it.  Check to see if you are getting a value from the PreImage.Id.  Just a guess, but hope it helps.




    ------------------------------
    Ryan Perry
    Business Systems Analyst
    (Currently Looking)

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Strange Query Issue From Plugin

    TOP CONTRIBUTOR
    Posted Apr 08, 2020 02:28 PM
    Ryan,

    Thanks for your reply.  Let me respond in the order you did.

    First, the Business (account) entity is MSCRM OOB.  The Client entity I mentioned is a custom entity as a child of Business. A Business can have multiple Client numbers/records (we are a law firm).  So my goal here is to prevent the user from deleting the Business record IF one or more Client records exist for the Business.  That is why I am querying vs36_accountdata (Client) in my code.

    Second, the second of the two links you provided is very similar to what I was able to make work in my coding.

    Third, you are absolutely correct that in the Delete message, Target is not available, only TargetRef and any PreImage (assuming you wired that up).  That is what I did when using the ID from the Business PreImage in my code line:
    .AddCondition("vs360_accountid", ConditionOperator.Equal, triggerInfo.PreImage.Id)

    During my debug, I verified that all the data I am using in my query exists and is correct (triggerInfo.PreImage.Id).  The triggerInfo object is one I create from PluginContext information formatted and organized in ways to make it easier for me to consume all through my plugin soution.  So, the debug reveals that the ID I am using is not null and is the correct GUID for the Business that triggered the message.

    So, you have perfect grasp of what I'm doing and the fail points that would be obvious.  The problem, none of the fail points failed! That is what has me SO puzzled.  The query runs AND has all the correct data.  For some reason, the query does not see the relationships in during the PreOp phase but does during the PreValidate phase.

    I'm either missing some technical issue/"feature" from MS, or maybe I've stumbled onto a bug.  Although, it seems I would not have been the first to hit this if a bug.  Anyway, thanks and to everyone:  any info is greatly appreciated.

    Sincerely,
    Glen


    ------------------------------
    Glen Wolinsky
    Lead Applications Developer
    Miller & Martin PLLC
    Chickamauga GA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Strange Query Issue From Plugin

    TOP CONTRIBUTOR
    Posted Apr 08, 2020 02:31 PM
    Correction: I just finished reading to the very end of the second link Ryan was kind enough to provide and the poster distinctly said:

    "In case of pre-operation the child records were not available."

    I'm not sure if the poster stated that as an intended design by MS or just stating the fact I've stumbled upon.  Any enlightenment would be appreciated.

    Sincerely,
    Glen Wolinsky

    ------------------------------
    Glen Wolinsky
    Lead Applications Developer
    Miller & Martin PLLC
    Chickamauga GA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Strange Query Issue From Plugin

    TOP CONTRIBUTOR
    Posted Apr 09, 2020 06:15 AM
    I'm not sure why this would be, and it is very intriguing, but a couple of other alternatives:

    1. Isn't the PreValidate step a better place to put validation logic? From memory the user gets a nicer presentation of the exception error message from plugins registered in this step
    2. Rather than using a plugin at all for this, could you not just change the relationship behavior to Delete: Restrict to get the system to prevent deleting the account when it has related records?


    ------------------------------
    Mark Carrington
    Chief Technologist
    Data8
    Chester
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: Strange Query Issue From Plugin

    MICROSOFT MVP
    Posted Apr 09, 2020 09:03 AM
    I second Marks 2nd point.  Why create a plugin when this is supported OOB?

    I'm also curious, since this is not the behavior I would expect, have you tried changing  "query.NoLock = true" to false to see if it changes the outcome?

    ------------------------------
    Daryl LaBar
    President, MVP
    Gap Integrity
    Fishers IN
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: Strange Query Issue From Plugin

    TOP CONTRIBUTOR
    Posted Apr 09, 2020 09:45 AM
    Daryl,

    Thanks for your response.  In regards to the OOB idea, please see my (lengthy response to Mark).  Your idea is an interesting and I will do some experimentation and let you know what happens.

    Thanks,
    Glen

    ------------------------------
    Glen Wolinsky
    Lead Applications Developer
    Miller & Martin PLLC
    Chickamauga GA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: Strange Query Issue From Plugin

    TOP CONTRIBUTOR
    Posted Apr 09, 2020 09:42 AM
    Mark,

    Thanks for taking the time to respond.  My experience with the error messages in Pre-Op vs. Pre-Validate is that the presentation is identical.  I am on-prem instead of online, so I don't know if that might be the difference or if I'm missing something (entirely possible).

    In regards to the Relationship idea, it is a good one. However, there are some cases where we do want to delete the entire relationship tree (not often and only by IT).  Also, there are other validations that take place besides that one that cannot be accommodated by the system (too lengthy to describe here).  Finally, the plugin allows me to present a better error message to the user that gives a detailed explanation as to why the Biz cannot be deleted along with an opportunity to notify IT that a user is trying to delete a Business record that is an actual client.

    I hope that made sense as I tend to ramble. :/  Thanks again.

    Sincerely,
    Glen

    ------------------------------
    Glen Wolinsky
    Lead Applications Developer
    Miller & Martin PLLC
    Chickamauga GA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 9.  RE: Strange Query Issue From Plugin

    TOP CONTRIBUTOR
    Posted Apr 09, 2020 10:27 AM
    That makes sense. It's not possible that your plugin is registered to run asynchronously is it?

    ------------------------------
    Mark Carrington
    Chief Technologist
    Data8
    Chester
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 10.  RE: Strange Query Issue From Plugin

    TOP CONTRIBUTOR
    Posted Apr 09, 2020 10:48 AM
    Edited by Glen Wolinsky Apr 09, 2020 10:49 AM
    Good question, but no.  It is running synchronously.  I also experimented with changing the query.NoLock setting but results were the same.

    Here is more weirdness. I registered two steps for this entity:
    1. Delete - Pre-Validation
    2. SetStateDynamicEntity to trap the Deactivate trigger - Pre-Operation

    Both events are Synchronous and the same two query functions service BOTH events (ClientsCount() and ContactsCount()).  The Delete step query usage will ONLY work during Pre-Validate.  The SetStateDynamicEntity query usage works fine as Pre-Op events.  Same queries, same code, different events.

    That is kinda why I was speculating that maybe there was some quirk about the Delete event that I was missing.

    It's a quandary, I tell you!  :o)

    ------------------------------
    Glen Wolinsky
    Lead Applications Developer
    Miller & Martin PLLC
    Chickamauga GA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 11.  RE: Strange Query Issue From Plugin

    TOP CONTRIBUTOR
    Posted Apr 09, 2020 12:47 PM
    As you're on-prem, can you use SQL Server Profiler to see if anything is issuing DELETE commands that is removing the child records before your plugin can run?

    ------------------------------
    Mark Carrington
    Chief Technologist
    Data8
    Chester
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 12.  RE: Strange Query Issue From Plugin

    TOP CONTRIBUTOR
    Posted Apr 09, 2020 01:40 PM
    Eureka!!! Which is funny, because I'm binge watching that series again.  But, I digress.

    I didn't run SQL profiler as I don't currently have it loaded (new dev box) and I didn't have time.  So, I did the next best thing.

    1. Changed my Delete message to run Pre-Op.
    2. Started a debug session with a stop immediately after the queries of clients and contacts but before the delete happens. (I keep a test Throw Error in the routine to always stop the delete from happening during my testing)
    3. Ran code.
    4. Execution stopped as planned.
    5. Opened a new browser window and opened the Business I was trying to delete to see what it would load.
    6. Low and behold, the Clients datagrid and the Contacts datagrid were both empty!!!!!
    Conclusions
    1. The Delete process deletes the children AFTER Pre-Validate and BEFORE Pre-Operation
    2. This deletion of children MUST be wrapped in a client or db transaction so if the delete fails or is aborted through code, the data is restored.
    3. The transaction starts AFTER Pre-Validation, then the child deletion and then any custom Pre-Operation processing.
    If I have made any incorrect assumptions or made any incorrect conclusions, please let me know.

    Thanks,
    Glen




    ------------------------------
    Glen Wolinsky
    Lead Applications Developer
    Miller & Martin PLLC
    Chickamauga GA
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 13.  RE: Strange Query Issue From Plugin
    Best Answer

    TOP CONTRIBUTOR
    Posted Apr 09, 2020 01:56 PM

    P.S. - I "brilliantly" figured out what has been known since 2012 (or earlier) (heavy sigh).

    While the linked article below was written in 2012, it seems that the underlying behavior of CRM has not changed this aspect.

    http://www.ariclevin.com/Blog/Post/prevalidation_events_delete_plugins

    Thanks to all for your input.

    Sincerely,
    Glen



    ------------------------------
    Glen Wolinsky
    Lead Applications Developer
    Miller & Martin PLLC
    Chickamauga GA
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role