0

I have model named Record which is a collection of properties from the database table and it is working fine (I display the Record collection in the html table on a page).

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.ApplyConfiguration(new RecordConfiguration());
}
public DbSet<Record> Records { get; set; }

But now I have an requirement that also in that table I need to display additional attributes for which I don't have any foreign keys (like ManagerId or similar) and the new model looks like this

public class RecordWithManager
{
    public Record Record{ get; set; }
    public string ManagerFirstName { get; set; }
    public string ManagerLastName { get; set; }
}

  • I don't want to add these new attributes to the database table and also I don't have any way how to get these data from the source table through navigation properties.

  • Also, I don't want to create new DTO object, which will duplicate the Records attributes and populate its data from some database view (I would have to create it), because then I would loose the navigation properties from the original Record object.

Can I somehow retrieve the Records objects and also join this object with some view (for example containing the Record.ID and new attributes) and store it to the RecordWithManager object? I think this kind of requirement is common but I do not have simple solution for it.

Muflix
  • 6,192
  • 17
  • 77
  • 153
  • Why not have `Manager` in a new table and add a `ManagerId` FK to `Record`? That seems like a pretty standard thing to do. – Xerillio Mar 15 '21 at 19:12
  • Because Manager is in different database and it is not exactly a table but result of SQL query (so it also might change in time, because it is not part of the record table). – Muflix Mar 15 '21 at 19:19
  • 1
    You might want to add that detail (two different databases) to the question. That's pretty key information. In that case take a look at [this answer](https://stackoverflow.com/a/52163009/3034273). Either you need a stored procedure or you need to query each DB individually and join the data in the application. EF Core doesn't support joining data between databases – Xerillio Mar 15 '21 at 19:24
  • Xerillio, thank you, so I will get the Manager data individually (because I use SQL Server synonym object it is still the same EF context) and I will foreach the Record collection and populate the additional attributes manually. That does not sound too bad. – Muflix Mar 15 '21 at 19:29
  • 1
    Sounds like a solution. If you're going with that, I'd recommend avoiding a query for each record and instead do something like: `SELECT * FROM [Managers] WHERE [Id] in (1,2,3,4...)` or the equivalent in EF if you want to use EF for it. Then you only need one roundtrip for all the managers – Xerillio Mar 15 '21 at 19:39

0 Answers0