Problems with BreezeJS + EF Core + Table Valued Functions

Question

I have a TVF (LoadUserInfo) in the DB that is being wired up to return data as per a class (UserInfo) in my .net project.

I am defining an IQueryable as follows:

 public IQueryable<UserInfo> LoadUserInfo(Guid? userId) 
    => FromExpression(() => LoadUserInfo(userId));

And then for EFCore in my OnModelCreating I setup as follows:

modelBuilder.HasDbFunction(typeof(AppDatabase).GetMethod(nameof(LoadUserInfo))!);

Now in my BreezeController I can easily return info directly from my TVF

  public IEnumerable<ReportInfo> UserInfos() {
    return PersistenceManager.Context.LoadUserInfo(User.GetUserId());
  }

The above works fine in terms of returning data from the API and also all metadata is correctly generated - ie even my UserInfo class is generated for use on the client side.

The problem is that when the metadata is returned during the API call it does NOT include typeinformation for UserInfo, it ignores it completely. I realized this was probably because I never declared UserInfo as a DbSet in the dbContext for EF Core. I went ahead and did that...

public DbSet<UserInfo> UserInfos => Set<UserInfo>();

Now UserInfos is included in the metadata but now when the server tries to return the data from the Breezecontroller it fails with the following message:

Invalid column name 'PeriodEnd'.
Invalid column name 'PeriodStart'.

Those are standard built in EF Core columns and I am suspecting that since UserInfo is now declared in the dbSet its expecting them to be there. But given that its not a real table, its just a class wired up to a TVF, they don't exist and its failing.

What is the correct way to set this up so that it doesn't fail? Thanks

Answer 1

I think you can solve this on the Breeze client. You don't need to add that DbSet.

Although the entity type is not returned in the query results, you can tell the Breeze client what type to expect by using .toType in your query:

var query = EntityQuery.from('UserInfos').toType('UserInfo');

Alternatively, you can patch the metadata (on the client) to tell Breeze what entity type to expect from that endpoint:

var meta = manager.metadataStore;
meta.setEntityTypeForResourceName('UserInfos', 'UserInfo');

See the Breeze docs for more info.