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.
  • What the list sends (DataProviderOdataService.select): $filter from filterInfo (ANDed with a $filter written in endpoint.uri), $orderby from sortInfo, $top/$skip from the datasource page and always $count=true (the pager total comes from @odata.count); the other query options written in endpoint.uri (e.g. $expand) are kept. With a non-paged grid (pageSize 0) there is no $top/$skip and @odata.nextLink is followed. Server paging only happens with md_server_side_operations on, the same rule as the grid's [lazy] (flag missing = in-memory paging, every row is read). A page over 1000 rows is read in blocks of 1000, at most 100,000 rows (also when following @odata.nextLink): beyond that, or when a block fails, the rows already read are kept and a partial-data warning is shown. The eqor operator (multi-value lookup) becomes field in (v1,v2,...); on text columns the values are always quoted. On date and date/time columns the value is an unquoted ISO literal with the offset (Edm.DateTimeOffset): a Date in UTC with Z, a text with an offset unchanged, a text without an offset with the browser's offset. On date-only columns (UI date) the filter is date(field) op yyyy-MM-dd with the browser's local day: the server compares the stored day with no time zone conversion (an instant, with different time zones on browser and server, landed on the wrong day). The server translates date(field) into year*10000 + month*100 + day on the column: the result is correct, but no DBMS can use an index on the column (worth considering for large tables).
  • What the list reads on date-only columns: the OData service exposes them as an instant with the server's offset (2026-10-13T00:00:00+02:00) or as Edm.Date; the lib keeps them as the server's day at local midnight without offset (2026-10-13T00:00:00), like the metadata provider, so a browser west of the server no longer shows the previous day. A day that was read and not changed is written back in the server's form (same offset or Edm.Date).
  • 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: nested $expand up to odataMaxExpandDepth levels (a($expand=b) = 2, see AppSettings); sub-options inside the expand ($select, $filter, $orderby, $top, e.g. $expand=stateProvince($select=...)) are not applied — .Include loads the entire related entity. Columns of the expanded entity that are denied to the user are left out of the response.

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).
  • Rows per response: at most odataMaxTop (default 1000), also without $top; a larger $top answers 400. A read without query options uses the route page size (md_service_page_size, when set; md_pagesize is the grid page size and does not apply here) within the same limit. With $count=true, when the page was cut by the limit (no $top), the wrapper also carries @odata.nextLink: the same request with the $skip of the next page. The plain array has no link: when it returns exactly the row limit, continue with $skip.

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. Only expanded and authorized navigations appear in the response: the others stay null.
  • 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.
  • $compute, $apply and $search are not applied (only $filter, $orderby, $skip, $top, $count, $select and $expand are honored): for non-superadmins they answer 400 errors.input.invalid. Also for non-superadmins, a query with more than odataMaxNodeCount nodes in $filter, $orderby and $expand (default 100) answers 400.

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.

- Creation: the current password is entered again (since 1.7.28; MetaService.createApiToken with current_password): missing or wrong, 403 errors.auth.password_confirmation_failed, and every error counts as a failed login for the attempt limit. A stolen session is no longer enough to obtain a token.

- 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 and on the external-client tools: it does not work on the rest of /api/*, so a token cannot manage other tokens.

- Scopes nl-query ("Data questions (WUIC Assistant)") and app-generator ("App generator (WUIC Assistant)", superadmin only): they work only on /api/nl-query/tools and /api/app-generator/tools, for "Ask your data" and the app generator from WUIC Assistant and Claude Code (see Ask your data, section "From WUIC Assistant and Claude Code"). A token with only the tool scopes does not read /odata.

- 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.