Known Issue: Web UI grid pages may show error with error in webui.log if custom property names have mismatched case between *Property_MT and UIItem_MT database tables: "An item with the same key has already been added" (IOJ-2172965)

[#1]

Summary

Web UI grid pages such as All Assets, All Contracts and others may be show an error if custom properties are configured, and where the case differs in custom property names specified in the following table columns in the compliance database:

• <Object type>Property.PropertyName
• UIItem.ItemResourceName

Details

The following error message is displayed when this problem occurs: 

We have a problem. Unexpected error
Sorry, but something unpleasant has happened. Please let us know what you were trying to do so that we can fix the problem. We suggest starting again from the dashboard, or trying again later.

Logging in the webui.log file may show details like the following:

[ERROR 2021-01-11 06:07:24,413 1127412ms UnhandledErrors                        ] An unexpected error occurred (f6dbcf18-5eeb-45d1-90ce-22973ad9909f)
System.Web.HttpException (0x80004005): Error executing child request for handler 'System.Web.Mvc.HttpHandlerUtil+ServerExecuteHttpHandlerAsyncWrapper'. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: An item with the same key has already been added.
   at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
   at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
   at DevExpress.Xpo.Metadata.ReflectionClassInfo.QueryOwnMember(String memberName)
   at DevExpress.Xpo.Metadata.XPClassInfo.FindMember(String memberName)
   at DevExpress.Xpo.Metadata.Helpers.MemberInfoCollection.FindMember(XPClassInfo currentClassInfo, String match)
   at DevExpress.Xpo.Metadata.Helpers.MemberInfoCollection..ctor(XPClassInfo classInfo, String[] matches, Boolean addNonPersistent, Boolean throwOnError)
   at DevExpress.Xpo.Metadata.XPClassInfo.ParsePath(String path)
   at DevExpress.Xpo.Parser.MemberAccess(MemberExpression expression, CriteriaOperator parent, XPClassInfo currentClassInfo)
[...]
   at DevExpress.Xpo.XPQueryBase.Where(MethodCallExpression call)
   at DevExpress.Xpo.XPQueryBase.Call(MethodCallExpression call)
   --- End of inner exception stack trace ---
   at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
[...]
   at ManageSoft.Database.Xpo.XpoQueryCustom`1.CreateQuery(Expression expression, XPQueryBase col)
[...]
   at Castle.Proxies.IDisposableProxy_12.GetChangedDevice()
   at Flexera.Web.Presentation.Controllers.AssetsController.GetAssets[T](AssetState assetState)
[...]
   at ASP._Page_Views_Assets_List_cshtml.Execute() in C:\Program Files (x86)\Flexera Software\FlexNet Manager Platform\WebUI\Views\Assets\List.cshtml:line 64

Alternatively, the following details may appear in logging: 

[ERROR 2022-02-15 18:31:45,695 1173697ms UnhandledErrors       ] An unexpected error occurred (245638ec-31e3-48c9-81bf-0879ed2117b8)
System.Web.HttpException (0x80004005): Error executing child request for handler 'System.Web.Mvc.HttpHandlerUtil+ServerExecuteHttpHandlerAsyncWrapper'. ---> System.ArgumentException: An item with the same key has already been added.
   at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
   at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
   at DevExpress.Xpo.Metadata.ReflectionClassInfo.QueryOwnMember(String memberName)
   at DevExpress.Xpo.Metadata.XPClassInfo.FindMember(String memberName)
   at DevExpress.Xpo.Metadata.XPClassInfo.GetMember(String memberName)
   at ManageSoft.Database.Xpo.XpoSession.OverrideSortColumn(Type p_ObjectType, String p_Column, Func`2 p_SortSqlFunc)
   at Flexera.Web.Core.Display.UiHelper_Grid.OverrideSortValueRangeColumn(String columnName, Type propertyType, XpoSession session, Type elementType, IEnumerable`1 dataSource)
   at Flexera.Web.Core.Display.UiHelper_Grid.ConfigureRangeColumn(UiHelper ui, MVCxGridViewColumn column, ModelMetadata propertyMetadata, GridCustomSettings settings)
   at Flexera.Web.Core.Display.UiHelper_Grid.ConfigureColumns(UiHelper ui, GridCustomSettings settings, Boolean allowScrolling)
   at Flexera.Web.Core.Display.UiHelper_Grid.ConfigureGrid(UiHelper ui, GridCustomSettings settings, Func`2 noResultsRazor, Boolean allowScrolling, Boolean delayLoad, Boolean showAllRecords)
   at Flexera.Web.Core.Display.UiHelper_Grid.<>c__DisplayClass2.<Grid>b__1(GridCustomSettings settings)
   at Flexera.Web.Core.Display.DevExpressHelper.GridViewCustom(ExtensionsFactory factory, Action`1 method)
   at Flexera.Web.Core.Display.UiHelper_Grid.RenderGridInternal(UiHelper ui, IViewModel modelType, IEnumerable modelList, Action`1 customSettings, GridCustomSettings baseSettings)
   at Flexera.Web.Core.Display.UiHelper_Grid.RenderGrid(UiHelper ui, IViewModel modelType, IEnumerable modelList, Action`1 customSettings, GridCustomSettings baseSettings)
   at Flexera.Web.Core.Display.UiHelper_Grid.GridForModel(UiHelper ui, Func`2 toolbarRazor, Func`2 noResultsRazor, Boolean allowSelection, Boolean crossPageSelection, Boolean showFilter, Boolean showSearch, Boolean delayLoad, Boolean showToolbar, GridCustomSettings baseSettings)
   at ASP._Page_Views_Assets_List_Grid_cshtml.Execute() in c:\Program Files (x86)\Flexera Software\FlexNet Manager Platform\WebUI\Views\Assets\List_Grid.cshtml:line 52
[...]
   at ASP._Page_Views_Assets_List_cshtml.Execute() in c:\Program Files (x86)\Flexera Software\FlexNet Manager Platform\WebUI\Views\Assets\List.cshtml:line 64

Resolution

To resolve this issue, ensure the case in the values in the <Object type>Property.PropertyName and UIItem.ItemResourceName columns exactly match so that there are no mismatches between uppercase and lowercase characters between these columns.

For example, the following SQL query can be executed against the compliance database to identify rows in the AssetTypeProperty view that have no corresponding rows in the UIItem view - if any rows are returned then this issue is likely to be triggered:

SELECT *
FROM dbo.AssetTypeProperty
WHERE PropertyName COLLATE SQL_Latin1_General_CP1_CS_AS NOT IN (
    SELECT ItemResourceName COLLATE SQL_Latin1_General_CP1_CS_AS
    FROM dbo.UIItem
)

Perform the following steps to correct problematic data:

1. Take a copy of data in the AssetTypeProperty view for future reference if needed. This can be simply done by copying the data from the output of running the following SQL query into a file (such as an Excel spreadsheet):

   SELECT * FROM dbo.AssetTypeProperty

2. Run the following SQL statements to update property names to match the case specified in the UIItem view:

   SELECT ItemResourceName
   INTO #TT
   FROM dbo.UIItem WHERE ItemResourceName IN (
     SELECT DISTINCT PropertyName
     FROM dbo.AssetTypeProperty
     WHERE PropertyName COLLATE SQL_Latin1_General_CP1_CS_AS NOT IN (
       SELECT ItemResourceName COLLATE SQL_Latin1_General_CP1_CS_AS FROM dbo.UIItem
     )
   )
   
   UPDATE t
   SET t.PropertyName = s.ItemResourceName
   FROM dbo.AssetTypeProperty t
     INNER JOIN #TT s ON t.PropertyName COLLATE SQL_Latin1_General_CP1_CI_AS = s.ItemResourceName COLLATE SQL_Latin1_General_CP1_CI_AS

3. Verify that re-running the initial query above now returns no rows to confirm that the problematic data has been corrected.

Similar steps to those described above can be followed for other object types (instead of the “Asset” object type). References to the AssetTypeProperty view should be replaced with the appropriate view name containing custom property definitions for the object type in question.

Cause

This issue is caused by case-insensitive comparisons that are done between property names in different tables since the FlexNet Manager Suite 2020 R2 release.

Fix status

This issue has been fixed in the following FlexNet Manager Suite releases: 2023 R2.4 / Apr 2024 (Cloud), 2024 R1 (On Premises)

Other information

Affected components: Web UI

Master issue ID: IOJ-2172965

Also known as: ITAM-3002

[tiloprobst]

This article says that issue IOJ-2172965 will not be adressed, yet in this support case it says that release 2022 R1 will fix it: https://flexeracommunity.force.com/Community/s/caselistview?id=5008Y00001mPS96 (#02518330)

[ChrisG]

@tiloprobst - Flexera Support case #02518330 does not appear to be related to the issue described in this article. That case is related to the following issue: Known Issue: Unable to download Oracle LMS evidence archive if file is 1GB or larger (IOJ-2215238)