Getting a "Item has already been added. Key in dictionary" error after move to SQL from Oracle

[Joe_Fox]

Back story - we are going to cloud with our OnBase system next year, so we have to migrate from Oracle 19c to SQL 2019.  I wrote a script that was gathering information regarding workview objects by running through a specified filter query.  This script would expand on the data, do some calculations and then produce XML for the document composition to create a concise report for the end user to view and give to the customer.  Currently on EP5 - 21.1.12.1000 

Current Issue -  After the migration to SQL, the same script that was working, is no longer working.  I am getting "Item has already been added. Key in dictionary: '169' Key being added: '169'" error when attempting to run it.  The last line below is the context of the where the error is occurring and the last line is where the actual error is being shown at.

Hyland.Unity.WorkView.FilterQuery fq = fFeedings.CreateFilterQuery();

fq.AddConstraint("LinkToEncounterID.EncounterID", Hyland.Unity.WorkView.Operator.Equal, intEncounterID);
fq.AddConstraint("Week", Hyland.Unity.WorkView.Operator.NotNull, null);
fq.AddConstraint("Meal", Hyland.Unity.WorkView.Operator.NotEqual, "Reserve");
fq.AddSort("FeedingDate", Hyland.Unity.WorkView.SortType.ASCENDING);
fq.AddSort("Meal", Hyland.Unity.WorkView.SortType.ASCENDING);

Hyland.Unity.WorkView.FilterQueryResultItemList feedings = fq.Execute(1000);

I know that normally that specific error comes from trying to add an item to a list or in this case filterqueryresultitemlist that does not have a unique key attached to the FilterQueryResultItem.  Based on the code, I don't have control over what comes out of the feedings line.  I have checked in the SQL DB and each of the lines that would come out of the Execute is unique and has different data.  

How do I fix this error? Is there another way around this error? Do we have to re-input the data/attributes back into the workview solution (based on a search through community)?

Thanks!

-Joe

[Stefan_Sulea · Accepted solution]

Hi Joe,

I don't have much experience with CreateFilterQuery as I prefer to use CreateDynamicFilterQuery, it removes the dependency of a specific filter. 

Nevertheless, this looks like there is a problem in the database, e.g. looks like the next free key which OnBase pulls from the hsi.rmkey table is already present in the database. 

As in your script you are adding constraints and sorts, what I would do is check the values in rmkey versus the values in the respective tables.

select * from hsi.rmkey where keyname in ('rmfilterconstraint', 'rmfiltersort')

select max(filterconstraintid) from hsi.rmfilterconstraint
select max(filtersortid) from hsi.rmfiltersort

If there is a mismatch (value in rmkey is lower than the value in actual table) then OnBase is trying to insert a duplicate key. In the screenshot below  all is fine.

Of course there could be a totally other key involved, but I would start with those two. 

Also in SQL Server you can easily start Profiler and run a trace to identify the offending statement.