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

Answer 1

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