Look for EF backlink optimisations

[JayVDZ]

Summary

Use scalar FK properties (e.g. dependent.AttributeId) instead of navigation properties (e.g. dependent.Attribute.Name) wherever possible, to eliminate unnecessary .Include() chains and avoid AsSplitQuery materialisation bugs (dotnet/efcore#33826).

The original motivation was CSOAV.Attribute → CSOAV.AttributeId, but this applies broadly across the codebase.

Current State (March 2026)

✅ Completed

• CSOAV model already has both Attribute (navigation) and AttributeId (scalar FK) properties
• Phase 4 sync page-load optimisation (Feb 2026) eliminated the most critical AsSplitQuery chains in sync methods, removing ~150 lines of post-load repair code (RepairReferenceValueMaterialisationAsync, RepairMvoAttributeValueMaterialisationAsync)
• Hot paths in sync/drift detection already use ID-based filtering (e.g. av.AttributeId == attribute.Id)
• Model layer uses ID-based comparisons in in-memory collections

🔶 Remaining (~50 instances)

Area	Files	Pattern	Priority
Search/display queries	ConnectedSystemRepository.cs	.Attribute.Name in ILike filters for display name / external ID resolution	Moderate
Export evaluation	ExportEvaluationServer.cs, DriftDetectionService.cs	.Attribute.Type for change building	Moderate
Connector exports	LDAP connector, File connector	.Attribute.Name for field mapping	Lower
UI sorting	MetaverseRepository.cs	Sort by .Attribute.Name	Lower

There are also ~25 remaining AsSplitQuery calls across 9 repository files, mostly for UI/admin queries where concurrent write pressure is low.

⚠️ Caution

A recent fix (commit 2479859, Mar 2026) showed that removing navigation property population can break downstream code that expects it (e.g. ExportChangeHistoryBuilder produced empty records when Attribute navigation was null but AttributeId was set). Each conversion needs careful tracing of all consumers.

Assessment

The highest-impact work is done. The critical sync-path optimisations (Phase 4) have been completed. Remaining opportunities offer diminishing returns and are best tackled opportunistically — when touching those files for other reasons, convert .Attribute.Name patterns to ID-based lookups where practical.

Recommendation: Downgrade priority. Keep open as a backlog item for incremental improvement rather than a dedicated effort.

[JayVDZ]

Giving this a high priority as it could potentially speed up Imports.

[JayVDZ]

Closing — scope absorbed into #427 (Systematic database index and query performance analysis).

The EF backlink optimisation work (using scalar FK properties instead of navigation properties to eliminate unnecessary .Include() chains) is a specific instance of the broader query efficiency analysis tracked in #427. The remaining items from this issue are now checklist entries in #427's expanded scope.