OData

OData - documentazione operativa e tecnica

advancedstable

Overview

OData

OData integration in the framework for metadata-driven routes, with controlled fallback to alternative providers. Full read + CRUD + $expand + $count=true wrapper support.

Scope

  • Using OData endpoints as a data source for wuic-data-source.
  • Alignment with table/column metadata for UI rendering and runtime behavior.
  • Relationship with the initial scaffolding flow and project bootstrap.
  • Navigation properties auto-generated from lookupByID columns to enable native $expand.

Examples

Metadata References

  • Routing and base configuration remain driven by metadata (md_route_name, columns, filters, sort, paging).
  • In md_props_bag you can force the provider via endpoint (example: {"endpoint":{"type":"odata","uri":"/odata/Orders"}}).
  • Lookup columns can also point to a custom OData endpoint via mc_props_bag.lookup.endpoint (see Widget Lookup).
  • The datasource continues to use filterInfo, sortInfo, pageInfo; on the OData provider side, translation into an OData query occurs.
  • For cross-cutting metadata details see Metadata.

Auto-generated Navigation Properties (lookupByID → $expand)

The dynamic EF generator (MetadataModelGenerator) automatically emits a navigation property on every column with mc_ui_column_type = 'lookupByID' and mc_ui_lookup_entity_name pointing to a route exposed in WebAPI. Naming convention: strip the Id / ID suffix from the FK name → nav property name (e.g., stateProvinceID → StateProvince). With EnableLowerCamelCase() active, the EDM exposes the nav in camelCase (stateProvince).

Practical effects:

  • the $metadata contains <NavigationProperty Name="stateProvince" Type="...Stateprovinces" /> on the Cities entity;
  • GET /odata/Cities?$expand=stateProvince returns each row with the related object inline ({... "stateProvince": { "stateProvinceID": 1, "stateProvinceName": "Alabama" }});
  • the EntitiesController.Get controller translates $expand=navProp into .Include(navProp) EF Core (SQL LEFT JOIN).

Current limits: only top-level $expand (no nested $expand=a($expand=b)); OData sub-options after the nav name (e.g., $expand=stateProvince($select=...)) are ignored — .Include loads the entire related entity. Columns of the expanded entity that are denied to the user still come back empty.

Response Shape and $count=true Wrapper

  • Default (without $count): the endpoint returns a plain JSON array ([{...}, {...}]) for compatibility with legacy framework consumers (DataProviderOdataService.select, DataProviderWebserviceService, etc.) that read response as any[].
  • Opt-in OData v4 ($count=true in the query): the endpoint returns the standard wrapper { "value": [...], "@odata.count": N } where N is the total computed after $filter/$orderby but before $skip/$top. Use when you need the paginated total (e.g., UI pager).

Full CRUD via EntitiesController

The generic OData endpoint exposes the entire RESTful suite on /odata/{EntitySet}:

  • GET /odata/{EntitySet} — list with $top/$skip/$filter/$orderby/$select/$expand/$count=true.
  • POST /odata/{EntitySet} — insert, JSON body with columns (null columns in the payload are skipped so SQL applies the DEFAULT DB-side, automatically supporting IDENTITY/SEQUENCE/GUID PKs and columns with temporal/audit defaults).
  • PATCH /odata/{EntitySet}({key}) — partial update, JSON body with subset of columns; PK in path.
  • DELETE /odata/{EntitySet}({key}) — delete by single key.

Gating on the metadata side (_metadati__tabelle row):

  • mdexposeinwebapi = 1 (required for any CUD, otherwise 403).
  • mdserviceenableinsert, mdserviceenableedit, mdserviceenabledelete = 1 for the respective operations.
  • See also the "Web service" metadata tab in the metadata-editor.

Permissions and row constraints (since 1.7.21)

OData applies to the session's user (or the token's owner) the same permissions and constraints as the UI's CRUD: nothing can be read or written through OData that the UI would not show. The superadmin has no user, role or company constraints and no default filter; as in the CRUD, however, logically deleted rows stay hidden.

  • Route: without read permission on the route (per-user, per-role or per-company authorizations, md_grant_by_default) the response is 403 errors.auth.route_read_forbidden.
  • Columns: a column the user cannot see, used in $select, $filter, $orderby or $expand, returns 403 (filtering on a hidden column would reveal its value). Without $select the column comes back empty. The server still reads the value from the DB and empties it in the response, so it never reaches the client; the CRUD instead excludes it from the SELECT. With $select (without $expand) only the requested columns are read.
  • Rows: the query starts from the CRUD's SELECT with the user's system constraints: per-user, per-role or per-company restriction (md_record_restriction_key_user_field_list), default filter (md_default_filter, always applied) and logical delete. The client's $filter, $orderby, $skip, $top and $count are applied outside, with AND, and can only narrow the result: with the per-user restriction, $filter=name eq 'X' or 1 eq 1 still returns only the user's rows.
Snippet 1SQL
  SELECT ... FROM ( SELECT t.* FROM t <join> WHERE <user constraints> ) s
  WHERE <client $filter>
  ORDER BY ... OFFSET ... FETCH ...
  • `$expand`: the JOIN is done by EF (.Include, LEFT JOIN) on the physical table of the target route, without the framework's WHERE. For non-superadmins it is allowed only towards a readable route without logical delete and without row constraints, otherwise 403; denied columns of the expanded entity come back empty. Reticular routes return 403.
  • Writes: POST, PATCH and DELETE go through the framework's CRUD (insertRecord, updateRecord, deleteRecord). So insert, edit and delete permissions apply, as do read-only columns, server triggers and callbacks, logging fields (insert and update user and date), the change log, workflows and the privilege-escalation checks on users and roles. The md_service_enable_* flags remain an extra switch.

- DELETE on a route with logical delete sets the flag instead of deleting the row, for the superadmin too.

- PATCH or DELETE on a row hidden from the user by the constraints: 404. DELETE without read permission on the route: 403.

- The POST response keeps its previous shape (payload plus the generated key).

- A payload column the user cannot see: 403. A visible but non-editable column is ignored by the CRUD, as in the UI.

- Entity sets without an exposed route: superadmin only, written directly with EF (without the CRUD).

  • Multi-company: the data connection is chosen on every request from the user's company (also after the superadmin's switchAzienda), as in the CRUD. A company whose data DB is configured but cannot be resolved returns an error, never the main DB. A company without its own data DB uses the main DB, as in the CRUD.
  • Constraints that cannot be built (malformed default filter, {user.x} parameter the user does not have): 403 errors.auth.route_read_forbidden, never 500.
  • Superadmin: no check on $expand, so expanded rows can include the target's logically deleted rows. Deleted rows are hidden from the superadmin only through the route's logical delete key (md_has_logic_delete), not through the global logicDeleteField setting. Reticular routes and entity sets without a route: full access as before.
  • $apply is not applied: only $filter, $orderby, $skip, $top, $count, $select and $expand are honored.

The same row rule now applies to the CRUD too: updateRecord, deleteRecord and deleteRecordByID on a row hidden by the restriction return 403 errors.auth.route_read_forbidden (reason row_not_visible). Up to 1.7.20 the UPDATE/DELETE WHERE clause only contained the key.

Authentication and personal tokens (since 1.7.21)

  • Every /odata endpoint requires a valid session (k-user cookie) or a personal token: without one, 401 errors.auth.unauthenticated. This also applies to /odata, /odata/$metadata and /odata/openapi.json, unless odataPublicMetadata=true (see AppSettings), which reopens only the discovery, not the data.
  • Personal tokens (apiTokensEnabled=true): each user creates them from the user menu, "API tokens" item (name, lifetime in days, read-only or read and write). The token (wuic_pat_...) is shown only once: the DB keeps only its SHA-256 and a recognizable prefix.

- Lifetime: 90 days by default, at most apiTokenMaxLifetimeDays (default 365). No token without an expiry; at most 20 active per user.

- It acts with the owner's permissions, in the company where it was created, and only on /odata: it does not work on /api/*, so a token cannot manage other tokens.

- Scope odata.read: POST, PATCH, PUT, DELETE and $batch (even read-only) return 403 errors.auth.api_token_scope. Scope odata.read odata.write: writes with that user's normal checks.

- Power BI / Excel: "OData feed" with the URL https://<host>/odata, "Basic" credentials: any user name, the token as the password. With tokens enabled, the 401 without credentials carries WWW-Authenticate: Basic, so the client asks for them.

- Scripts: header Authorization: Bearer wuic_pat_....

Snippet 2Bash
    curl -H "Authorization: Bearer wuic_pat_..." "https://<host>/odata/Cities?\$top=5"

- Errors: 401 errors.auth.api_token_invalid (unknown, revoked or expired token, or deleted owner); 401 errors.auth.api_tokens_disabled (feature off); 429 after 5 failed attempts from the same IP within 5 minutes, for 60 seconds.

- Revocation takes effect immediately; the superadmin sees and revokes everyone's tokens. Last use is updated at most once a minute.

"Web Service" Tab Metadata (OData/API)

In the table metadata editor, the Web Service tab governs API exposure and operational gates on the service side.

  • md_expose_in_webapi: exposes the route in Web API/OData services.
  • md_service_apply_default_filter: deprecated since 1.7.21. OData always applies md_default_filter to users who are not superadmin, as the UI's CRUD does (see "Permissions and row constraints"): the flag no longer turns it off.
  • md_service_enable_delete: enables delete from the service endpoint.
  • md_service_enable_edit: enables update/edit from the service endpoint.
  • md_service_enable_insert: enables insert from the service endpoint.
  • md_service_enable_detail: enables record detail endpoint.
  • md_service_enable_clone: enables record clone on the service side.
  • md_service_enable_logging: enables operation logging on the service side.
  • md_service_page_size: service-side page size (Pagesize in the UI tab).

Recommended inline snippet:

  • md_expose_in_webapi=true, md_service_enable_insert=true, md_service_enable_edit=true, md_service_enable_delete=true, md_service_page_size=100.

Practical note:

  • md_pagesize controls the standard UI paging; md_service_page_size controls service endpoint paging.
  • For OData, it is advisable to keep service flags aligned with the backend's actual capabilities (read-only vs read/write).

Scaffolding References

  • The bootstrap (firstRun) prepares the application context and initial connections; after scaffolding, you can attach routes to OData endpoints.
  • During initial setup, it is advisable to immediately validate routes, table/column metadata, and minimum mappings to avoid OData/metadata mismatches.
  • Operational reference: Initial Scaffolding.

Recommended Operational Flow

1. Define/validate route metadata and minimum columns.

2. Configure the OData endpoint provider in md_props_bag.endpoint.

3. Verify list reading with filter/sort/paging from the UI.

4. If using OData auth, align appsettings keys (enableODATAAuthentication and related policies).

Practical Notes

  • OData and metadata are not alternatives: metadata remains the source of UI behavior, OData the data source.
  • In case of schema differences, first update column metadata (mc_*) and then refine the OData endpoint/query.
  • Since 1.7.13 the /odata/{EntitySet} data endpoints require a valid session (a k-user cookie that passes the same validation as the rest of the API; a guest session is not enough), regardless of enableODATAAuthentication: without it they answer 401 errors.auth.unauthenticated. Since 1.7.21 /odata, /odata/$metadata and /odata/openapi.json require a session (or a personal token) too, unless odataPublicMetadata=true: see "Authentication and personal tokens".
  • Spatial columns (point, geometry, polygon, sdo_geometry and the like) are not exposed in the entity sets, with the same behavior on SQL Server, MySQL, PostgreSQL and Oracle: for geographic data use the framework grid or map. All other columns are exposed with the same type on all four DBMSs.