Querying ExtraProperties server-side — native JSON column type and runtime-defined property names #10863

3 Answer(s)

• 

  0

  AI-Bot created 20 days ago

  Support Team ABP Agent

  • Server-side filtering on ExtraProperties (without MapEfCoreProperty): No, not in a supported/official way today. In ABP’s EF Core integration, ExtraProperties is stored via a value conversion (JSON string <-> ExtraPropertyDictionary). Because of that, EF Core cannot translate arbitrary predicates over dictionary contents to SQL, so you effectively can’t get provider-side translation (e.g., JSON_VALUE, ->>, etc.) unless you promote the property to a real mapped column using ObjectExtensionManager.MapEfCoreProperty(...).

  • Mapping ExtraProperties to provider-native JSON column types (json / jsonb): No supported ABP extension point is documented to change the store type of ExtraProperties from its current string-based persistence to provider-native JSON types. ABP’s entity extension system is designed around:

    1. keeping truly dynamic properties inside the JSON bag (ExtraProperties) and
    2. promoting queryable properties into first-class mapped columns via MapEfCoreProperty.

  • Overriding the mapping yourself (HasColumnType("json") + custom converter) — is it “supported”?: There is no verified ABP guidance stating that changing the underlying column type / conversion strategy of ExtraProperties is supported or upgrade-safe. Since ABP modules and conventions assume ExtraProperties is serialized JSON stored in a regular column, overriding it may work technically for your EF Core provider, but it is not something ABP commits to as a stable customization surface (so it can break with framework/provider upgrades).

  • Runtime-only property names (user-defined after deployment): ABP’s intended path is not “query these runtime keys inside ExtraProperties in SQL”. The documented/established mechanism for queryability is design-time mapping with MapEfCoreProperty, which inherently can’t cover unknown-at-deploy keys. For your requirement (tenant-specific runtime schema + indexed JSON path querying), ExtraProperties is not a good fit as the primary query surface.

  • Recommendation (given your performance findings and requirements):

    • Keep using ABP’s ExtraProperties for what it’s best at: extensibility, UI/form metadata, occasional values, non-query-critical dynamic data.
    • For high-row-count, query-critical, runtime-defined attributes, prefer an application-owned JSON column (exactly as you tested), where you can:
      • use SQL Server native json + JSON index (or PostgreSQL jsonb + GIN),
      • query with provider-native JSON operators/functions,
      • index paths without per-property migrations,
      • keep the model aligned with “runtime schema” reality.

  If you want, you can still mirror a small subset of “hot” attributes into promoted ABP extension columns (via MapEfCoreProperty) for the cases where keys become stable and worth first-class columns, while keeping the long tail in your JSON attribute store.

  Link verified:

  • https://abp.io/docs/en/abp/latest/Customizing-Application-Modules-Extending-Entities

  ---

  This response was generated by AI. If it didn’t solve your problem, feel free to post a new answer — our support team will jump in to help you soon.

  Markdown supported.

  Copy, paste, or drag & drop images and files (max 100 MB per file, 100 MB total per post)

  Save Cancel
• 

  0

  AlessandroLittera created 20 days ago

  Thanks — the first three points are clear and useful, and the recommendation matches what our measurements pointed to, so the product decision is settled on our side.

  Two questions remain open, and I would appreciate a review from the team: the reply came from AI-Bot and, on exactly these two points, states that no guidance is "documented" or "verified" rather than answering them.

  1. Does anything inside ABP depend on the ExtraProperties column being a string-typed column? I am not asking whether overriding it is a supported customisation — you have answered that. I am asking what would actually break, so we can judge the risk ourselves. Specifically: ObjectExtensionManager, the audit log's property change tracking, distributed event serialisation, and the MongoDB provider.

  2. Is provider-native JSON storage for ExtraProperties (SQL Server json, PostgreSQL jsonb) on your roadmap, or has it been considered and rejected? "Not planned" is a perfectly useful answer for us — we need to know whether to design around its absence permanently or treat it as a temporary gap.

  Context, if useful: we are not looking for a workaround to push queryable data into ExtraProperties. We have accepted your recommendation and will keep query-critical dynamic attributes in an application-owned json column. Question 1 only matters for the smaller set of extended entities where the values already live in ExtraProperties today.

  Markdown supported.

  Copy, paste, or drag & drop images and files (max 100 MB per file, 100 MB total per post)

  Save Cancel
• 

  0

  maliming created 20 days ago

  Support Team Fullstack Developer

  Hi,

  Nothing in ABP reads or writes that column outside the value converter, so the store type is yours to pick.

  On the EF Core side, the only things configured for ExtraProperties are the column name, the converter and a value comparer (TryConfigureExtraProperties). There is no HasColumnType, no HasMaxLength and no default value — nvarchar(max) is what the provider picks for a converted string, not something the framework asks for.

  For the four areas you listed:

  • ObjectExtensionManager: MapEfCoreProperty adds a separate real column. Its only contact with the bag is in the converter, which strips mapped keys before serializing, and in AbpDbContext.FillExtraPropertiesForTrackedEntities / HandleExtraPropertiesOnSave, which copy values between the dictionary and that column. All on the CLR side.
  • Audit log property changes: EntityHistoryHelper reads propertyEntry.CurrentValue / OriginalValue, which are ExtraPropertyDictionary instances, and serializes them in C#. It never sees the provider value. It records the bag as one property change rather than per key, and truncates to 512 chars, but that is already the case on nvarchar.
  • Distributed events: nothing reads that column. The default EntityEto doesn't carry the entity's extra properties at all, and the ETOs that do expose them take them from the CLR dictionary. The outbox and inbox tables keep EventData as byte[].
  • MongoDB: ExtraProperties is mapped as the BSON extra-elements member, so the keys become top-level document fields. Different provider, different mapping, unaffected either way.

  ABP 10.6 also doesn't filter, sort or search the contents of that column in any of its built-in EF Core queries.

  For the override itself, go through ObjectExtensionManager rather than a single DbContext:

  ObjectExtensionManager.Instance
      .MapEfCoreEntity<IdentityUser>(b =>
      {
          b.Property(nameof(IHasExtraProperties.ExtraProperties)).HasColumnType("jsonb"); // json on SQL Server
      });
  

  That goes in your <YourProject>EfCoreEntityExtensionMappings.Configure(). The reason to prefer it: it lands in whichever DbContext maps the entity, including a module's own one at runtime. If you configure it only on your application DbContext, it covers your own entities and the modules you replace with [ReplaceDbContext] (the template does that for Identity and Saas), but a module you don't replace keeps its own model, and you end up with a converted column against a model that still says string — writes then fail with a type mismatch.

  Two more things to plan for:

  • Back up the database first, and try the whole thing on a copy before you touch the real tables. This rewrites a column in place on tables you can't afford to lose. Review the generated migration too, don't just apply it — it comes out as a plain AlterColumn, and every existing value has to be a JSON object or SQL NULL, not just valid JSON. An empty string fails the conversion, while an array or a string/number literal passes it and then throws on read, since the converter deserializes into a dictionary. On PostgreSQL the bare AlterColumn is rejected outright and you write the cast yourself: ALTER TABLE "AbpUsers" ALTER COLUMN "ExtraProperties" TYPE jsonb USING "ExtraProperties"::jsonb.
  • Changing the store type doesn't change how EF Core sees the column. It's still one converted string property, so x.ExtraProperties[...] stays untranslatable in LINQ.

  On the roadmap question: the tracked item is https://github.com/abpframework/abp/issues/23546, making ExtraProperties queryable at the database level. It's open and in the backlog, with no target release. The approach it explores is EF Core's owned-JSON mapping (OwnsOne(...).ToJson()), which needs every key declared up front — unconfigured keys can't be written or ordered by, which is why it stalled on exactly the requirement you care about. It doesn't cover switching the store type on its own.

  One last thing, since you are on SQL Server 2025 on-premises rather than Azure SQL: the native json type and CREATE JSON INDEX are both still in preview there — https://learn.microsoft.com/sql/relational-databases/json/json-data-sql-server#sql-server-2025-changes. Worth checking that against your production requirements before you commit the 445k-row table to it.

  Thanks

  Markdown supported.

  Copy, paste, or drag & drop images and files (max 100 MB per file, 100 MB total per post)

  Save Cancel