[Enh]: Add relationship support for entities of object type `view`.

[JerryNixon]

What?

Extend relationship support so entities backed by views can participate in relationships:

• view → view
• view → table
• table → view

No new config syntax. Uses existing entities.<name>.relationships settings (including relationship.fields and optional linking.* for M:N).

Why?

Today DAB supports relationships between tables entities, but we do not support relationships where either side is a view.

Background

Even though views often lack PK/FK constraints in the database, DAB already allows relationships to be defined in config without requiring DB constraints. If we can build JOIN predicates from configured relationship fields, we should be able to do the same when the underlying objects are views.

Motivation

This unlocks common patterns where views are the preferred read model.

How?

Scope

• Support relationship JOIN generation for Read operations when either source or target entity is source.type: "view".
• Relationships must be fully defined in config (no reliance on DB FK constraints).
• This enhancement is only supported when data-source.database-type is mssql or dwsql . Basically, SQL Server.

Requirements

1. Entity types allowed

   • If Entity.Source.Type is table or view, it may be used in relationships.
   • Stored procedures remain out of scope.

2. Join predicate source

   • Use relationship.fields (sourceFields ↔ targetFields) to build join predicates.
   • Do not require PK/FK constraints to exist in the database.

3. Nullability

   • Don’t assume FK nullability based on DB constraints for views. Use existing behavior, but avoid throwing solely because metadata lacks constraints.

4. M:N relationships

   • Linking object must remain a table (practical requirement for join + existing behavior).
   • Allow source/target to be views, but linking object stays table.

5. Unsupported

   • Multi-create mutations against view-backed entities are not part of this enhancement. We can tackle this later if we see customer demand, but this would EXPLODE the scope and complexity of this feature right now.

Config example (view → table)

"entities": {
  "BooksView": {
    "source": { "object": "dbo.vw_books_details", "type": "view" },
    "permissions": [ { "role": "anonymous", "actions": [ "*" ] } ],
    "relationships": {
      "publisher": {
        "cardinality": "one",
        "target.entity": "Publisher",
        "relationship.fields": [ "publisher_id:id" ]
      }
    }
  }
}

Config example (view → view)

"relationships": {
  "authors": {
    "cardinality": "many",
    "target.entity": "AuthorsView",
    "relationship.fields": [ "id:book_id" ]
  }
}

Notes

• This feature is “just” about: read relationship config → build JOIN predicate even when underlying objects are views.
• Existing relationship schema stays the same.
• This feature only supports SQL Server/TSQL (mssql or dwsql).

Implementation

Key code paths to update (these are the likely choke points for “tables only” assumptions):

• Relationship metadata hydration / FK definition creation:

  • src/Core/Services/MetadataProviders/SqlMetadataProvider.cs
    • currently builds relationship metadata and FK definitions; ensure it can represent relationships when DatabaseObject is a view (or at least not reject it).

• SQL join predicate construction used by query builders:

  • src/Core/Resolvers/Sql Query Structures/BaseSqlQueryStructure.cs
    • AddJoinPredicatesForRelationship(...) and related helpers should work when either side is a view-backed DatabaseObject.

• GraphQL schema relationship field generation (mostly type-level, but ensure no “table-only” validation trips):

  • src/Service.GraphQLBuilder/Sql/SchemaConverter.cs (GenerateFieldForRelationship(...))

Core logic

https://github.com/Azure/data-api-builder/search?q=GetType%28%29+%21%3D+typeof%28DatabaseTable%29&type=code