Including a Model from different DbContext

Question

I am not sure how to achieve the relation between 2 DbContexts. PurchaseOrderDbContext is a Code first approach & AgencyDbContext is an existing database. How can I include the "Division" from AgencyDbContext based on PurchaseOrder DivisionId?

To start off here is a very simplified version of my code.

Purchase Order Model

   namespace Website.Models.PurchaseOrders
  {
    public class PurchaseOrder
    {
    public int ID { get; set; }
    public DateTime OrderDate { get; set; } 
    public string Name { get; set; }     
    public int DivisionId { get; set; }
    public int StatusID { get; set; }    
    public Agency.Division Division { get; set; }
    } 
  }

Division Model (this is in a different DbContext)

    namespace Website.Models.Agency
{
    public class Division
    {
            public int DivisionId { get; set; }
            public string DivisionName { get; set; }
            public string DivisionShortName { get; set; }
            public string DivisionAbbrev { get; set; }
            public int? DivisionDirectorEmpId { get; set; }

    }
}

Agency DbContext

    namespace Website.Models.Agency
{
    public class AgencyDbContext : DbContext
    {


        public Agency DbContext(DbContextOptions<AgencyDbContext> options) : base(options)
        {

        }

        public virtual DbSet<Division> Division { get; set; }
        public virtual DbSet<Section> Section { get; set; }


        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {

        }
    }
}

PurchaseOrderDbContext

  namespace Website.Models.PurchaseOrders
{
    public class PurchaseOrderDbContext : DbContext
    {

        public PurchaseOrderDbContext(DbContextOptions<PurchaseOrderDbContext> options) : base(options)
        {}
        public DbSet<Status> Statuses { get; set; }
        public DbSet<PurchaseOrder> PurchaseOrder { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
        }

    }

    }

I get an the error InvalidOperationException: Lambda expression used inside Include is not valid. This is referring to the Include extension on Division.

                 var purchaseOrder = _context.PurchaseOrder
                                    .Include(p => p.Division)
                                    .Include(p => p.Status)
                                    .OrderByDescending(p => p.OrderDate);

Thank you in advance!

are contexts targeting same db? then you would need to include division into purchaseorderdbcontext, to make include possible. if not, then you would need to do 2 queries, first on purchase orders, and then get linked divisions through agency db by division ids you got from the first query — Yehor Androsov, May 22 '20 at 18:38
Only two DB are used, PurchaseOrder and Agency. Would I add a Virtual Dbset to PurchaseOrderDbContext then? — Jet813, May 22 '20 at 19:07
if Division table does not exist in purchase order db, then it would not help. EF can be used only to make queries within single database. — Yehor Androsov, May 22 '20 at 19:33
If the databases are on a single server and it supports views or synonyms (like SQL server or Azure SQL Database Managed Instance then you can) have a DbContext that uses tables from both databases by mapping some entities to views/synonyms. You would have to prevent Migrations from managing tables for entities mapped to a view or synonym. — David Browne - Microsoft, May 22 '20 at 21:57
found similar question https://stackoverflow.com/questions/26446145/joining-tables-from-two-databases-using-entity-framework — Yehor Androsov, May 23 '20 at 08:10

score 0 · Answer 1 · answered May 22 '20 at 19:42

Probably the only way to resolve is to make a query to the first context for items you are looking for, and then populate Division property with entries from second context

public class PurchaseOrderService
{
    private readonly PurchaseOrderDbContext purchaseOrderDbContext;
    private readonly AgencyDbContext agencyDbContext;

    public PurchaseOrderService(PurchaseOrderDbContext purchaseOrderDbContext,
        AgencyDbContext agencyDbContext)
    {
        this.purchaseOrderDbContext = purchaseOrderDbContext;
        this.agencyDbContext = agencyDbContext;
    }

    public PurchaseOrder Get(int id)
    {
        var purchaseOrder = purchaseOrderDbContext.PurchaseOrder.FirstOrDefault(x => x.ID == id);

        if (purchaseOrder == null)
        {
            return null;
        }

        purchaseOrder.Division = agencyDbContext.Division.FirstOrDefault(x => x.DivisionId == purchaseOrder.DivisionId);

        return purchaseOrder;
    }
}

What about if I am receiving more than one PurchaseOrders? FirstorDefault wont work. — Jet813, May 22 '20 at 20:15
Adding a foreach and assigning division that way might work. How will this effect performance? — Jet813, May 22 '20 at 20:28
any decision will be slower than the native one due to poor database design. for multiple items you can apply .Where(x => divisionIds.Contains(x.DivisionId)).ToList() , where divisionIds is array of integers. — Yehor Androsov, May 23 '20 at 07:34

Including a Model from different DbContext

1 Answers1