Dataverse reverse engineering - foreign keys

[MarkMpn]

When I reverse engineer tables from Microsoft Dataverse, all primary keys and therefore foreign keys are ignored and so I cannot use properties to access related records in the way I am used to with standard SQL Server databases.

I have a change that I would like to submit to extend the index and foreign key handling in the SQL Server reverse engineering logic to allow it to work with Dataverse - is this something you would be interested in?

To join two tables in Dataverse today requires code like:

var results = ctx.Accounts
  .Join(ctx.Contacts, a => a.Primarycontactid, c => c.Contactid, (a, c) => new { a, c })
  .Select(ac => new { ac.a.Name, ac.c.Fullname });

It would be much nicer to be able to use lookup properties like:

var results = ctx.Accounts
  .Select(a => new { a.Name, a.Primarycontact.Fullname });

This currently isn't possible because the indexes are ignored during reverse engineering, so the foreign keys can't be used because it doesn't recognise the corresponding primary key. dotnet ef dbcontext scaffold produces warnings like:

Could not scaffold the foreign key 'dbo.account(primarycontactid)'. A key for 'contactid' was not found in the principal entity type 'Contact'.

EF Core version: 8.0.8 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 9.0 Operating system: Windows 11 IDE: Visual Studio 2022 17.10

[ErikEJ]

That would be interesting to see a PR for - feel free to reach out, I created the original PR to enable Dataverse support.

[MarkMpn]

Thanks, I've submitted it as #34689