Recently I added Navigation Properties
to a EF Core
project that didn't have any FK in order to use Include
instead of Join
in as many queries as possible, but I've found that either the sintax doesn't suit my needs in pretty much all the queries (requires to split into multiple queries) or I'm missing something so I'll put an example, and I want to know, using Navigation Properties
how would you get all the Stops with its Line list for a specific dayType?:
Point |
pointId |
stopId? |
routeId |
Route |
routeId |
serviceDetailId |
ServiceDetail |
serviceDetailId |
serviceHeaderId |
ServiceHeaderDay |
serviceHeaderDayId |
serviceHeaderId |
dayType |
ServiceHeader |
serviceHeaderId |
lineId |
The current working query using Join
that I would like to translate using Include
:
var query = (await context.Stop
.Join(
context.Point,
(stop) => stop.stopId,
(point) => point.stopId,
(stop, point) => new { Stop = stop, Point = point })
.Join(
context.Route,
(join) => join.Point.routeId,
(route) => route.routeId,
(join, route) => new { Stop = join.Stop, Route = route })
.Join(
context.ServiceDetail,
(join) => join.Route.routeId,
(serviceDetail) => serviceDetail.routeId,
(join, serviceDetail) => new { Stop = join.Stop, ServiceDetail = serviceDetail })
.Join(
context.ServiceHeader,
(join) => join.ServiceDetail.serviceHeaderId,
(serviceHeader) => serviceHeader.serviceHeaderId,
(join, serviceHeader) => new { Stop = join.Stop, ServiceHeader = serviceHeader })
.Join(
context.ServiceHeaderDay,
(join) => join.ServiceHeader.serviceHeaderId,
(serviceHeaderDay) => serviceHeaderDay.serviceHeaderId,
(join, serviceHeaderDay) => new { Stop = join.Stop, ServiceHeader = join.ServiceHeader, ServiceHeaderDay = serviceHeaderDay })
.Join(
context.Line,
(join) => join.ServiceHeader.lineId,
(line) => line.lineId,
(join, line) => new { Stop = join.Stop, ServiceHeaderDay = join.ServiceHeaderDay, Line = line })
.Where(e => e.ServiceHeaderDay.DayType == "L")
.Select(e => new { Stop = e.Stop, Line = e.Line })
.Distinct();
.ToListAsync())
// The query ends here, this next step is just grouping by Stops and inserting each Line list into them.
.GroupBy(e => e.Stop.stopId)
.Select(e =>
{
var stop = e.First().Stop;
stop.Lines = e.Select(e => e.Line).ToList();
return stop;
})
One of the failed attemps made using Include
:
context.Stop
.Include(e => e.Points)
.ThenInclude(e => e.Route)
.ThenInclude(e => e.ServiceDetail)
.ThenInclude(e => e.ServiceHeader)
.ThenInclude(e => e.ServiceHeaderDay
Where(e => e.DayType = "L")
// Now I need to Include Line from ServiceHeader, but this is a of type ServiceHeaderDay
// and I think you can't use anonymous objects to carry all the tables you just include
// so I found people repeating the includes like this:
.Include(e => e.Point)
.ThenInclude(e => e.Route)
.ThenInclude(e => e.ServiceDetail)
.ThenInclude(e => e.ServiceHeader)
.ThenInclude(e => e.Line)
// This doesn't seem to work, but also how would be the select to get the Stops with all
// the lines for each Stop here?
.Select(e => ?)
If I understand your problem correctly, your query can be simplified a lot. Include
usually is not for querying but for loading related data for modification purposes.
var query = context.Stop
.Where(s => s.Points.Any(p => p.Route.ServiceDetail.ServiceHeader.ServiceHeaderDay.DayType = 'L'))
.Select(s => new
{
Stop = s,
Lines = s.Points.Where(p => p.Route.ServiceDetail.ServiceHeader.ServiceHeaderDay.DayType = 'L')
.Select(p => p.Route.ServiceDetail.ServiceHeader.Line)
.ToList()
});