In Entity Framework Core, how can my EF entities return a value from another table that is not an en-CodePudding

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?

CodePudding user response：

As EF context does not support cross database queries. Therefore, workaround can be 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, StoreProcedure and View to achieve that. Afterwards, you should define that within a POCO Class finally call that on your conext.For instaces, here I am showing the example using 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 fields I would use in first database tableUserId, 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; }
            
            protected 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: