Single database call pulling data from multiple tables in EF Core

Question

Single database call pulling data from multiple tables in EF 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
                })
            };

twilliams

View more on Stack Overflow

Dan Roberts · Accepted Answer · 6/27/2016 8:51:42 PM

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
        })
};

Single database call pulling data from multiple tables in EF Core

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related