Dynamics CRM Plugin Query TotalRecordCount returning -1

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

pbu-tsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Feridun KadirPrincipal ConsultantCommented:
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
1
pbu-tsAuthor 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

0
pbu-tsAuthor 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?
0
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Start Today!
Chinmay PatelChief Technical NinjaCommented:
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.
0
Chinmay PatelChief Technical NinjaCommented:
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.
0
Feridun KadirPrincipal ConsultantCommented:
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.
0
Feridun KadirPrincipal ConsultantCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pbu-tsAuthor 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?
0
pbu-tsAuthor 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?
0
Chinmay PatelChief Technical NinjaCommented:
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.
0
pbu-tsAuthor 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?
0
pbu-tsAuthor 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.
0
Feridun KadirPrincipal ConsultantCommented:
Wow, that was simple in the end!
Glad you got there.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Dynamics

From novice to tech pro — start learning today.