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
})
};
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
})
};
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow