In Entity Framework Core, how can my EF entities return a value from another table that is not an entity as a lookup?

Question

I'm using EF Core 6 on a project. Most of my tables have a field like CreatedBy/UpdatedBy that includes a user id. There are only a few occasions where I need to show the full name associated with the user id, but that value is in another table in a different database, but on the same server.

Is creating a view that joins to the needed table only way to handle this? Could I create function in the database where my EF Core entities are modeled? How would that work code wise?

Answer 1

As EF context does not support cross database queries. Therefore, workaround can be a SQL view.

Is creating a view that joins to the needed table only way to handle this?

Yes you can do that. While creating view you should consider below way:

SELECT {Your 1st Table Column}  [YourFirstDatabase].[dbo].[Your1stDbTable] WHERE UserId = (SELECT {Your 2nd Table Column} FROM [YourSecondDatabase].[dbo].[Your2ndDbTable] WHERE Category = 'Cat')

Could I create function in the database where my EF Core entities are modeled?

You could create function, stored procedure and view to achieve that. Afterwards, you should define that within a POCO Class finally call that on your context. For instance, here I am showing the example using a SQL view:

SQL view:

USE [DatabaseName_Where_You_Want_to_Create_View]

CREATE VIEW [ExecuteQueryFromOneDbToAnother]
AS
    SELECT UserId, UserType,CreatedDate 
    FROM [RentalDb].[dbo].[Users] 
    WHERE UserId = (SELECT AnimalId 
                    FROM [PetAnalyticsDb].[dbo].[Animal] 
                    WHERE Category = 'Cat')

Note: I am simulating the example where I have two database from that I have two table where these columns, I would use in first database table UserId, UserType, CreatedDate and in second database from Animal table from the AnimalId I will search the user

How would that work code wise?

Following example would guided you how the implementation in the code should be.

Database context:

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext (DbContextOptions<ApplicationDbContext > options) : base(options)
    {
    }

    public DbSet<MultipleDBQueryExecutionModel> multipleDBQueryExecutionModels { get; set; }
            
    override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MultipleDBQueryExecutionModel>().ToTable("ExecuteQueryFromOneDbToAnother");
    }
}

Note: put your view name while map in DbContext to table ToTable("ExecuteQueryFromOneDbToAnother");. Here ExecuteQueryFromOneDbToAnother is the view name.

POCO class:

public class MultipleDBQueryExecutionModel
{
        [Key]
        public Int  UserId { get; set; }
        public string UserType { get; set; }
        public DateTime CreatedDate { get; set; }
}

Controller:

    [HttpGet]
    public ActionResult GetDataFromDifferentDatabase()
    {
        var data = _context.multipleDBQueryExecutionModels.ToList();
        return Ok(data);
    }

Output: