Single database call pulling data from multiple tables in EF Core

entity-framework entity-framework-core

Question

The following code currently opens a connection three times to my database, to pull out each object.

Is there a better way to craft the query so the database is only hit once and pulls back all the objects I'm looking for?

var metadataResult = new MetadataViewModel
            {
                Milestones = goalsContext.Milestones.Select(m => new MilestoneViewModel
                {
                    Id = m.Id,
                    Name = m.Name,
                    Year = m.Year,
                    Date = m.Date
                }),
                Aggregates = goalsContext.Aggregates.Select(a => new AggregateViewModel
                {
                    Id = a.Id,
                    Name = a.Name
                }),
                Metrics = goalsContext.Metrics.Select(m => new MetricViewModel
                {
                    Id = m.Id,
                    Name = m.Name,
                    Description = m.Description
                })
            };
1
1
6/27/2016 8:21:16 PM

Accepted Answer

If your view models are a fairly similar shape then you should be able to use Union to get everything in one query and then transform the rows into appropriate ViewModel instances afterwards. Something like the following -

var combinedResults =
    context.Products.Select(p => new
    {
        Type = "Product",
        ID = p.ProductID,
        Name = p.ProductName,
        SupplierName = p.Supplier.CompanyName
    })
    .Union(
        context.Categories.Select(c => new
        {
            Type = "Category",
            ID = c.CategoryID,
            Name = c.CategoryName,
            SupplierName = (string)null
        })
    )
    .ToList();

var viewModel = new ViewModel
{
    Products = combinedResults
        .Where(x => x.Type == "Product")
        .Select(x => new ProductViewModel
        {
            ID = x.ID,
            Name = x.Name,
            SupplierName = x.SupplierName
        }),
    Categories = combinedResults
        .Where(x => x.Type == "Category")
        .Select(x => new CategoryViewModel
        {
            ID = x.ID,
            Name = x.Name
        })
};
4
6/27/2016 8:51:42 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow