Dynamics CRM Plugin Query TotalRecordCount returning -1

pbu-ts
pbu-ts used Ask the Experts™
on
I am working on a plugin and have written a function to pull back any incidents that have the ticket number passed into the function. A fairly simple function but for some reason I am receiving back a -1 value from the property TotalRecordCount on the EntityCollection. My understanding is that this means it is not finding any records at all but I know that there is a record with the ticket number being passed in. I must be missing something anyone have any thoughts?
 internal EntityReference fetchCaseByCaseNumber(string caseNumber, IOrganizationService service, ITracingService tracingService)
        {
            Entity incident;

            //tracingService.Trace("Creating QueryByAttribute");
            //QueryByAttribute querybyattribute = new QueryByAttribute("incident");
            //querybyattribute.ColumnSet = new ColumnSet("incidentid", "ticketnumber");
            //querybyattribute.Attributes.AddRange("ticketnumber");
            //querybyattribute.Values.AddRange(caseNumber);
            //EntityCollection incidents = service.RetrieveMultiple(querybyattribute);


            tracingService.Trace("Creating Query...");
            QueryExpression query = new QueryExpression
            {
                EntityName = "incident",
                //PageInfo = new PagingInfo() {count },
                ColumnSet = new ColumnSet("incidentid", "ticketnumber"),
                Criteria = new FilterExpression
                {
                    Conditions =
                                {
                                    new ConditionExpression
                                    {
                                        AttributeName = "ticketnumber",
                                        Operator = ConditionOperator.Equal,
                                        Values = {caseNumber}
                                    }
                                }
                }
            };
            tracingService.Trace("Running Query");
            EntityCollection incidents = service.RetrieveMultiple(query);
            tracingService.Trace("fetching total count");
            int count = incidents.TotalRecordCount;
//            incident = incidents.Entities.First();
            if (count == 1)
            {
                incident = incidents.Entities.First();
            }
            else
            {
                tracingService.Trace(String.Format("{0} incidents found with ticketnumber",incidents.TotalRecordCount.ToString()));
                throw new InvalidPluginExecutionException("Duplicate ticket number error");
            }
            EntityReference caseReference = new EntityReference("incident",incident.Id);
            return caseReference;
        }

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Feridun KadirPrincipal Consultant

Commented:
I think -1 means that you didn't ask for the total record count to be returned.

You need to set ReturnTotalRecordCount  property to true as per this MSDN article: https://msdn.microsoft.com/en-us/library/gg334688.aspx

Author

Commented:
Feridan I was able to get the TotalRecordCount to show per your recommendation but I am still not getting any records back. TotalRecordCount = 0

I am passing in the string value 000003 and I am getting zero records returned. Even though this SQL pulls exactly one record as expected
SELECT TicketNumber, *
FROM IncidentBase
WHERE TicketNumber = '000003'

Open in new window


Here is the Updated Plugin Code as well.

internal EntityReference fetchCaseByCaseNumber(string caseNumber, IOrganizationService service, ITracingService tracingService)
        {
            Entity incident;
            tracingService.Trace("Creating Query...");
            QueryExpression query = new QueryExpression
            {
                EntityName = "incident",
                PageInfo = new PagingInfo() { ReturnTotalRecordCount = true },
                ColumnSet = new ColumnSet("incidentid", "ticketnumber"),
                Criteria = new FilterExpression
                {
                    Conditions =
                                {
                                    new ConditionExpression
                                    {
                                        AttributeName = "ticketnumber",
                                        Operator = ConditionOperator.Equal,
                                        Values = {caseNumber}
                                    }
                                }
                }
            };
            tracingService.Trace("Running Query");
            EntityCollection incidents = service.RetrieveMultiple(query);
            tracingService.Trace("fetching total count for incidents with ticket number: {0}", caseNumber);
            if (incidents.TotalRecordCount == 1)
            {
                incident = incidents.Entities.First();
            }
            else
            {
                tracingService.Trace(String.Format("{0} incidents found with ticketnumber",incidents.TotalRecordCount.ToString()));
                throw new InvalidPluginExecutionException("Duplicate ticket number error");
            }
            EntityReference caseReference = new EntityReference("incident",incident.Id);
            return caseReference;
        }

Open in new window

Author

Commented:
Does this method work to turn on tracing within Microsoft Dynamics CRM 2016?

https://support.microsoft.com/en-us/help/907490/how-to-enable-tracing-in-microsoft-dynamics-crm


I am not seeing any of those registry keys in the registry, should I just create them?

Will this tracing give me more information on the SQL or Fetch XML that is being run against the server?
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Chinmay PatelChief Technology Ninja
Distinguished Expert 2018
Commented:
Hi pbu-ts,

To turn on the tracing in Dynamics 2016 (OnPremise only) onwards, use Monitor and troubleshoot Microsoft Dynamics 365

I was wondering have you tried debugging your plugin? I think maybe the code itself is not being called. One of the old tricks I used was to throw exception and see how far my code execution goes.

In case, you already have tried debugging, let me know and I will try to recreate the scenario in my environment.

Regards,
Chinmay.
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
Hi pbu-ts,

Tracing will give you SQL and not the Fetch (at least in this case). I have one more suggestion, why don't you use FetchXml to run this query?

Use http://www.xrmtoolbox.com/ and run the Fetch and see if you are getting result there. If you do, then simply use that FetchXML, instead of QueryExpression to get the results in your Plugin as well.

And one more thing, are you sure the user (In whose context the plugin is  running has permissions to retrieve this data? The biggest disadvantage w.r.t. Dynamics CRM is if you try to query data bypassing its filtered views, you will end up bypassing security restrictions applied by Security Roles.

Regards,
Chinmay.
Feridun KadirPrincipal Consultant

Commented:
A couple of things, is your SQL code literally what you ran?  I ask because the string you test for (000003) is not a valid case number in CRM.
Another thing to point out is that querying the base tables bypasses security, whereas your code will be running in the context of a CRM user so could it be that the user account under which the code is being tested doesn't have rights to query cases?  A bit of a long shot, but I thought I'd check.

With regard to the registry values, you should add them if they are not there. Another approach might be to use the SQL Profile to record the queries hitting the CRM database. You'll need to use some filtering otherwise you'll be swamped with data.
Principal Consultant
Commented:
Looking at your code (and I'm not really a developer) I noticed that the way you construct the criteria and conditions is not the same as shown in an MSDN example (see the example here https://msdn.microsoft.com/en-us/library/gg334688.aspx, for example).

Perhaps what you have done amounts to the same, in which case, my apologies.

Author

Commented:
First, I enabled tracing on the server and so far I am looking at the trace logs in C:\Program Files\Microsoft Dynamics CRM\Trace and it is not showing me any SQL or much more then what I see in the GUI tracelog. I am just getting the exception I throw wrote in line 33. Am I looking in the right place for trace logs? Any thoughts on how to see more?

Second, the SQL I gave is the literal SQL. I have another pre-create plugin that creates custom incident numbers that are a incrementing 6 digit integer.

Third, I changed the user to run under a user with full access and there was no change.

As to the code I can see that it runs in the built in trace logs in the console(new plugin trace logs in 2016). It processes all the way through line 32 and throws the exception in line 33. I even see the record count in the trace log as 0 from line 32 and the ticket number as 000003 from line 25.

Any thoughts?

Author

Commented:
So per the suggestion I tried to turn on on premise trace using the powershell on https://technet.microsoft.com/en-us/library/hh699694.aspx
I have never used powershell so forgive my ignorance. I ran the following commands and got an error.

Add-PSSnapin Microsoft.Crm.PowerShell
Get-CRMSetting TraceSettings

"Get-CRMSetting :  The underlying connection was closed: An unexpected error ocurred on a send."

Microsoft talks about needed to register the powershell commandlets in the article. Do I have to register the XRM Toolkit as discussed here to be able to do anything with CRM using powershell?
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
Hi pbu-ts,

XRMTooling is not required for your scenario. Try : https://technet.microsoft.com/en-us/library/dn531202.aspx

What happens when you just import the snap-in? Do you get any error then?

Regards,
Chinmay.

Author

Commented:
Chinmay,

Good to know. I do not get an error when I run "Add-PSSnapin Microsoft.Crm.PowerShell" In fact if I run a command like "Get-Help *Crm*" before adding the snappin it comes back blank but when I add the snapping and rerun the command. I get a list of Crm related commandlets so it seems like it is successfully adding the snappin. Do I need to set up any sort of connection or anything before running the powershell commandlets?

Author

Commented:
I found the answer to this question. The case number that is being passed into this function had a space in it. Thank you to everyone that helped out.
Feridun KadirPrincipal Consultant

Commented:
Wow, that was simple in the end!
Glad you got there.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial