How to query SQL view as a ComplexType (read-only) with OData 8.x?

[NGUYET25483-pki]

Hi, I would like to use OData to only GET (read-only) data from a SQL view which I have mapped as a ComplexType since there are no keys. I haven't been able to get this to work. The endpoints on my controller only shows when using EntitySet, but I cannot use an EntitySet with the keyless view. Is there some configuration or mapping that I am missing?

[NGUYET25483-pki]

I see that ODataModelBuilder enforces a key for EntitySet, which makes sense, but are there any work arounds or alternative approaches? @xuzhg and @hassanhabib - any help would be greatly appreciated.

[JL-Ikosoft]

Hi, Maybe some kind of

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
	modelBuilder.Entity<Customer>(eb =>
	{
		eb.HasNoKey();
		eb.ToView("CustomerView");
	});
}

[habbes]

@NGUYET25483-pki if you want to expose a collection of complex types, then instead of using an entity set, consider using an OData function that returns a collection of complex types. Functions do not require that return types have keys. Functions are also ideal for read-only data that does not have side-effects (Actions on the other hand are ideal when you have side-effects).

In your particular scenario, since you want a top-level endpoint that's not bound to a specific entity set, you should declare an unbound function and expose a function import on your OData model.

If you're using the ODataModel builder, it would look like:

var builder = new ODataConventionModelBuilder();
builder.ComplexType<MyComplexType>();
builder.Function("GetData").ReturnsCollection<MyComplexType>();

Then you can create a controller method like:

[HttpGet("GetData()")]
public IQueryable<MyComplexType> GetData()
{
   return ...;
}

Let me know if this helps.

[KenitoInc]

@NGUYET25483-pki Was your issue resolved?