Question posted in
The official documentation can be found here.

Asp.net – How to join tables from multiple dbcontexts in one ViewModel?

So I’ve got two dbcontexts, to get data from various tables and I want to join them in a ViewModel. I know I should probably use .Join(), but I wondered if something like this was also possible:

My Controller:

public class HomeController : Controller
{
    private readonly NAV_FS_DBContext _navContext;
    private readonly DostebaPpsContext _baseDataContext;

    public HomeController(NAV_FS_DBContext navContext, DostebaPpsContext baseDataContext)
    {
        _navContext = navContext;
        _baseDataContext = baseDataContext;
    }

    // GET: Home
    public async Task<IActionResult> Index()
    {
        List<SpeweSalesLine> speweSalesList = await _navContext.SpeweSalesLines.ToListAsync();
        List<SpeweProdOrderLine> spewePoList = await _navContext.SpeweProdOrderLines.ToListAsync();
        List<Variable> baseDataList = await _baseDataContext.Variables.ToListAsync();
        List<PoItemViewModel> poItems = await _navContext.SpeweProductionOrder
            .Select(x => new PoItemViewModel
            {
                Status = x.Status,
                PONr = x.No,
                ItemNr = x.SourceNo,
                FormType = x.Description,
                Quantity = x.Quantity,
                StartingDate = x.StartingDate,
                EndingDate = x.EndingDate,
                ShipmentDate = speweSalesList.Where(y => y.No == x.No).Select(y => y.ShipmentDate).FirstOrDefault(),
                Priority = spewePoList.Where(y => y.ProdOrderNo == x.No).Select(y => y.Priority).FirstOrDefault(),
                Molds = baseDataList.Where(y => y.SourceNo == x.SourceNo).Select(y => y.MoldPerDay).FirstOrDefault()
            }).ToListAsync();

        return View(poItems);
    }
}

The ViewModel:

public class PoItemViewModel
{
    public string? ItemNr { get; set; }
    public int? Status { get; set; }
    public string? FormType { get; set; }
    public decimal? Quantity { get; set; }
    public int? Molds { get; set; }
    public decimal? Days { get; set; }
    public string? PONr { get; set; }
    public DateTime? StartingDate { get; set; }
    public DateTime? EndingDate { get; set; }
    public DateTime? ShipmentDate { get; set; }
    public int? Priority { get; set; }
}

NAV_FS_DBContext:

public partial class NAV_FS_DBContext : DbContext
{
    public NAV_FS_DBContext()
    {
    }

    public NAV_FS_DBContext(DbContextOptions<NAV_FS_DBContext> options)
        : base(options)
    {
    }

    public virtual DbSet<SpeweItem> SpeweItem { get; set; }
    public virtual DbSet<SpeweProductionOrder> SpeweProductionOrder { get; set; }

    public virtual DbSet<SpeweItemLedgerEntry> SpeweItemLedgerEntries { get; set; }

    public virtual DbSet<SpeweSalesLine> SpeweSalesLines { get; set; }

    public virtual DbSet<SpeweValueEntry> SpeweValueEntries { get; set; }
    public virtual DbSet<SpeweProdOrderLine> SpeweProdOrderLines { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseSqlServer("Name=ConnectionStrings:CS2");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.UseCollation("Latin1_General_100_CI_AS");

        modelBuilder.Entity<SpeweItem>(entity =>
        {
            entity.HasKey(e => e.No).HasName("SPEWE$Item$0");

            entity.ToTable("SPEWE$Item", "dbo");
            //etc...
            });
            OnModelCreatingPartial(modelBuilder);
        }

        partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
    }
}

SpeweProductionOrder Model (from dbcontext):

public partial class SpeweProductionOrder
{
    public byte[] Timestamp { get; set; } = null!;

    public int Status { get; set; }

    public string No { get; set; } = null!;

    public string Description { get; set; } = null!;

    public string SearchDescription { get; set; } = null!;

    public string Description2 { get; set; } = null!;

    public DateTime CreationDate { get; set; }

    public DateTime LastDateModified { get; set; }

    public int SourceType { get; set; }

    public string SourceNo { get; set; } = null!;

    public string RoutingNo { get; set; } = null!;

    public string InventoryPostingGroup { get; set; } = null!;

    public string GenProdPostingGroup { get; set; } = null!;

    public string GenBusPostingGroup { get; set; } = null!;

    public DateTime StartingTime { get; set; }

    public DateTime StartingDate { get; set; }

    public DateTime EndingTime { get; set; }

    public DateTime EndingDate { get; set; }

    public DateTime DueDate { get; set; }

    public DateTime FinishedDate { get; set; }

    public byte Blocked { get; set; }

    public string ShortcutDimension1Code { get; set; } = null!;

    public string ShortcutDimension2Code { get; set; } = null!;

    public string LocationCode { get; set; } = null!;

    public string BinCode { get; set; } = null!;

    public string ReplanRefNo { get; set; } = null!;

    public int ReplanRefStatus { get; set; }

    public int LowLevelCode { get; set; }

    public decimal Quantity { get; set; }

    public decimal UnitCost { get; set; }

    public decimal CostAmount { get; set; }

    public string NoSeries { get; set; } = null!;

    public string PlannedOrderNo { get; set; } = null!;

    public string FirmPlannedOrderNo { get; set; } = null!;

    public string SimulatedOrderNo { get; set; } = null!;

    public DateTime StartingDateTime { get; set; }

    public DateTime EndingDateTime { get; set; }

    public int DimensionSetId { get; set; }

    //etc...
}

SpeweSalesLine Model (from dbcontext):

public partial class SpeweSalesLine
{
    public byte[] Timestamp { get; set; } = null!;

    public int DocumentType { get; set; }

    public string DocumentNo { get; set; } = null!;

    public int LineNo { get; set; }

    public string SellToCustomerNo { get; set; } = null!;

    public int Type { get; set; }

    public string No { get; set; } = null!;

    public string LocationCode { get; set; } = null!;

    public string PostingGroup { get; set; } = null!;

    public DateTime ShipmentDate { get; set; }

    public string Description { get; set; } = null!;

    public string Description2 { get; set; } = null!;

    public string UnitOfMeasure { get; set; } = null!;

    public decimal Quantity { get; set; }

    public decimal OutstandingQuantity { get; set; }

    public decimal QtyToInvoice { get; set; }

    public decimal QtyToShip { get; set; }

    public decimal UnitPrice { get; set; }

    public decimal UnitCostLcy { get; set; }

    public decimal Vat { get; set; }

    public decimal LineDiscount { get; set; }

    public decimal LineDiscountAmount { get; set; }

    public decimal Amount { get; set; }

    public decimal AmountIncludingVat { get; set; }

    public byte AllowInvoiceDisc { get; set; }

    public decimal GrossWeight { get; set; }

    public decimal NetWeight { get; set; }

    public decimal UnitsPerParcel { get; set; }

    public decimal UnitVolume { get; set; }

    public int ApplToItemEntry { get; set; }

    public string ShortcutDimension1Code { get; set; } = null!;

    public string ShortcutDimension2Code { get; set; } = null!;

    public string CustomerPriceGroup { get; set; } = null!;

    public string JobNo { get; set; } = null!;

    public string WorkTypeCode { get; set; } = null!;

    public byte RecalculateInvoiceDisc { get; set; }

    public decimal OutstandingAmount { get; set; }

    public decimal QtyShippedNotInvoiced { get; set; }

    public decimal ShippedNotInvoiced { get; set; }

    public decimal QuantityShipped { get; set; }

    public decimal QuantityInvoiced { get; set; }

    public string ShipmentNo { get; set; } = null!;

    public int ShipmentLineNo { get; set; }

    public decimal Profit { get; set; }

    public string BillToCustomerNo { get; set; } = null!;

    public decimal InvDiscountAmount { get; set; }

    public string PurchaseOrderNo { get; set; } = null!;

    public int PurchOrderLineNo { get; set; }

    public byte DropShipment { get; set; }

    public string GenBusPostingGroup { get; set; } = null!;

    public string GenProdPostingGroup { get; set; } = null!;

    public int VatCalculationType { get; set; }

    public string TransactionType { get; set; } = null!;

    public string TransportMethod { get; set; } = null!;

    public int AttachedToLineNo { get; set; }

    public string ExitPoint { get; set; } = null!;

    public string Area { get; set; } = null!;

    public string TransactionSpecification { get; set; } = null!;

    public string TaxCategory { get; set; } = null!;

    public string TaxAreaCode { get; set; } = null!;

    public byte TaxLiable { get; set; }

    public string TaxGroupCode { get; set; } = null!;

    public string VatClauseCode { get; set; } = null!;

    public string VatBusPostingGroup { get; set; } = null!;

    public string VatProdPostingGroup { get; set; } = null!;

    public string CurrencyCode { get; set; } = null!;

    public decimal OutstandingAmountLcy { get; set; }

    public decimal ShippedNotInvoicedLcy { get; set; }

    public int Reserve { get; set; }

    public string BlanketOrderNo { get; set; } = null!;

    public int BlanketOrderLineNo { get; set; }

    public decimal VatBaseAmount { get; set; }

    public decimal UnitCost { get; set; }

    public byte SystemCreatedEntry { get; set; }

    public decimal LineAmount { get; set; }

    public decimal VatDifference { get; set; }

    public decimal InvDiscAmountToInvoice { get; set; }

    public string VatIdentifier { get; set; } = null!;

    public int IcPartnerRefType { get; set; }

    public string IcPartnerReference { get; set; } = null!;

    public decimal Prepayment { get; set; }

    public decimal PrepmtLineAmount { get; set; }

    public decimal PrepmtAmtInv { get; set; }

    public decimal PrepmtAmtInclVat { get; set; }

    public decimal PrepaymentAmount { get; set; }

    public decimal PrepmtVatBaseAmt { get; set; }

    public decimal PrepaymentVat { get; set; }

    public int PrepmtVatCalcType { get; set; }

    public string PrepaymentVatIdentifier { get; set; } = null!;

    public string PrepaymentTaxAreaCode { get; set; } = null!;

    public byte PrepaymentTaxLiable { get; set; }

    public string PrepaymentTaxGroupCode { get; set; } = null!;

    public decimal PrepmtAmtToDeduct { get; set; }

    public decimal PrepmtAmtDeducted { get; set; }

    public byte PrepaymentLine { get; set; }

    public decimal PrepmtAmountInvInclVat { get; set; }

    public decimal PrepmtAmountInvLcy { get; set; }

    public string IcPartnerCode { get; set; } = null!;

    public decimal PrepmtVatAmountInvLcy { get; set; }

    public decimal PrepaymentVatDifference { get; set; }

    public decimal PrepmtVatDiffToDeduct { get; set; }

    public decimal PrepmtVatDiffDeducted { get; set; }

    public int DimensionSetId { get; set; }

    public decimal QtyToAssembleToOrder { get; set; }

    public decimal QtyToAsmToOrderBase { get; set; }

    public string JobTaskNo { get; set; } = null!;

    public int JobContractEntryNo { get; set; }

    public string DeferralCode { get; set; } = null!;

    public DateTime ReturnsDeferralStartDate { get; set; }

    //etc...
}

Variable Model (from dbcontext)

public partial class Variable
{
    public string SourceNo { get; set; } = null!;

    public string Type { get; set; } = null!;

    public int PiecePerPallett { get; set; }

    public int Lotsize { get; set; }

    public int MoldPerDay { get; set; }

    public int FormAmount { get; set; }

    public int CavityAmount { get; set; }

    public decimal FormTime { get; set; }

    public decimal FillTime { get; set; }

    public decimal CarouselRotation { get; set; }

    public decimal CompletionTime { get; set; }

    public decimal Cycle { get; set; }
}

Obviously it doesn’t work as I get an InvalidOperationException, but I mean, I do load all data beforehand and save it as a list, so why can’t I retrieve / compare data from, for example "spewePoList"?

It would be nice to get some thoughts on this, and I thank you in advance for the help.

Tags:

2

Answers


  1. 0

    At first, remove not needed meterialization of speweSalesList and spewePoList. All what we can do with single context, we should reuse.

    Prepared example how to do that without navigation properties. It would be simplier and error prone to use navigation properties instead of explicit subqueries.

    var baseDataList = await _baseDataContext.Variables
    .Select(v => new { v.SourceNo, v.MoldPerDay } )
    .ToListAsync();

// Just IQueryable shortucts
var speweSalesList = _navContext.SpeweSalesLines;
var spewePoList = _navContext.SpeweProdOrderLines;

var poItems = await _navContext.SpeweProductionOrder
    .Select(x => new PoItemViewModel
    {
        Status = x.Status,
        PONr = x.No,
        ItemNr = x.SourceNo,
        FormType = x.Description,
        Quantity = x.Quantity,
        StartingDate = x.StartingDate,
        EndingDate = x.EndingDate,
        ShipmentDate = speweSalesList.Where(y => y.No == x.No).Select(y => y.ShipmentDate).FirstOrDefault(),
        Priority = spewePoList.Where(y => y.ProdOrderNo == x.No).Select(y => y.Priority).FirstOrDefault(),
        Molds = baseDataList.Where(y => y.SourceNo == x.SourceNo).Select(y => y.MoldPerDay).FirstOrDefault()
    }).ToListAsync();

    Please check that it works, Molds can be problematic and may need post postprocessing.
    Also review your business logic, you are loading whole tables into the memory.

    Login or Signup to reply.
  2. 0

    IEnumerable vs IQueryable

    You have to be aware of the difference between an IEnumerable and an IQueryable.

    An IEnumerable represents a sequence of similar items. You can ask for the first element of the sequence, and as long as you’ve got an element of the sequence you can ask for the next element of the sequence, until no more elements are left. The IEnumerable holds everything to fetch the elements of the sequence.

    An IQueryable seems like an IEnumerable, however, it does not represent an Enumerable sequence, it represents the potential to create an Enumerable sequence.

    For this, the IQueryable holds an Expression and a Provider. The Expression holds in some generic format what must be fetched, the Provider knows where the data will be fetched (usually a Database Management System), and what language is used to communicate with this DBMS (usually something like SQL).

    The IQueryable also implements IEnumerable. When you ask the IQueryable to GetEnumerator, the Expression is sent to the Provider, who will translate the Expression into SQL, and execute the query. The fetched data is represented as an IEnumerable, of which GetEnumerator is called. Some smart Providers will only fetch data when the first element is enumerated.

    But what does this have to do with my question?

    Your two DbContexts represent two different database management systems. A DBMS is extremely optimized to combine tables and to select data from the result. The slower part of a query is the transport of the selected data from the DBMS to your local process. Hence it is wise to limit the amount of data being transferred.

    By using await ...ToListAsync() you fetch a data from _navContext to your local process. Similarly you fetch some data from _baseDataContext to your local process.

    Your final query sends all the fetched data from your local process to the _navContext database to execute your Select and Where. The selected records are then sent back from the DBMS to your local process. Although this will probably give you the required results, it is not very efficient.

    It would be way more efficient to only fetch the data from _baseDataContext, then send the selected data to the _navContext DBMS, which will combine this data with data from tables SpeweSalesLines, SpeweProdOrderLines, and SpeweProductionOrder to create the required result.

    Advantages are:

    • you don’t send the complete contents of tables SpeweSalesLines, and SpeweProdOrderLines to your local process, nor return this data to _navContext DBMS.
    • only the data that you plan to use will be fetched from _navContext.

    The improved query would be (in small steps):

    The following won’t execute the query, it will only create the query.
    There is no communication with _navContext, no data will be selected.

    IQueryable<SpeweSalesLine> sales = _navContext.SpeweSalesLines;
IQueryable<SpeweProdOrderLine> prodOrders = _navContext.SpeweProdOrderLines;

    The query below will be executed. Data will be transferred from _baseDataContext
    to your local process:

    List<Variable> baseDataVariables = await _baseDataContext.Variables.ToListAsync();

    Use the fetched data, and use the other two queries to create a new query. Again there is no communication with _navContext yet.

    IQueryable<PoItemViewModel> poItemQuery = _navContext.SpeweProductionOrder
    .Select(x => new PoItemViewModel
    {
        Status = x.Status,
        PONr = x.No,
        ItemNr = x.SourceNo,
        FormType = x.Description,
        Quantity = x.Quantity,
        StartingDate = x.StartingDate,
        EndingDate = x.EndingDate,

        // Select the first ShipmentDate from the spewSales
        // that has a value for No that equals this x.No
        ShipmentDate = sales
           .Where(sale => sale.No == x.No)
           .Select(sale => sale.ShipmentDate)
           .FirstOrDefault(),

        // Select the first Priority from the speweProdOrders
        // that has a value for 
        Priority = speweProdOrders
            .Where(prodOrder => prodOrder.ProdOrderNo == x.No)
            .Select(prodOrder => prodOrder.Priority)
            .FirstOrDefault(),

        // Select the first MoldPerDay from the baseDataVariables that was fetched
        // from the _baseDataContext that has a SourceNo that equals this SourceNo
        Molds = baseDataList
            .Where(baseDataVariable => baseDataValues.SourceNo == x.SourceNo)
            .Select(baseDataVariable => baseDataVariable.MoldPerDay)
            .FirstOrDefault(),
        });

    Now execute the query to create the desired result. The fetched baseDataVariables are sent to DBMS _navContext. The necessary tables are combined and the requested data is selected. Finally only the selected data will be sent to the local process:

    List<<PoItemViewModel> poItems = await poItemQuery.ToListAsync().

    Result:

    • only the data that you use will be transferred from _baseDataContext to your local process, and then to _navContext.
    • SpeweSalesLines and SpeweOrderLines are not transferred to your local process.
    • Only the data that you plan to use is transferred from _navContext to your local process.

    Of course, if desired you can put this all together in one big LINQ statement. It won’t improve efficiency, however I’m sure it will deteriorate readability.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search