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?:

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 => ?)

CodePudding user response：

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