Return an IQueryable that joins two tables

c# entity-framework-core linq

Question

This service method will return an IQueryable<Vehicle>:

public IQueryable<Vehicle> GetVehicles()
{
    return
        from v in _context.Vehicles
        where
            v.Schedule == true
            && v.Suspend == false
        select v;
}

If I want to include another table in the query:

public IQueryable<Vehicle> GetVehicles()
{

    return
        from v in _context.Vehicles
        join si in _context.ServiceIntervals on v.Id equals si.VehicleId into loj
        from rs in loj.DefaultIfEmpty()
        where
            v.Schedule == true
            && v.Suspend == false
        select new { vehicle = v, repair = rs };

}

What's the correct IQueryable<T> return type? IQueryable<{vehicle,repair}> isn't correct.

Is there a better way to compose the select statement?

** Edit **

I was hoping to keep this simple, but I think a clarification is useful.

ServiceIntervals is actually an IQueryable<T> that references a table-valued function from another SQL DB:

public IQueryable<ServiceInterval> ServiceIntervals(DateTime startingDate, DateTime endingDate) =>
    Query<ServiceInterval>().FromSql($@"
        SELECT *
        FROM OtherDatabase.Dbo.ServiceIntervals({startingDate}, {endingDate})"
    );

The origin query actually includes dates:

...
_context.ServiceIntervals(DateTime.Now.Date,DateTime.Now.Date)
...

As such, I don't think ServiceIntervals can be exposed as a navigation property on the Vehicle entity.

1
0
7/13/2019 4:07:06 PM

Popular Answer

Doing select new {} in LINQ creates an anonymous type, which by definition is anonymous and can't be used as a return type. If you want to return this variable, you have to create a type for it.

public class VehicleServiceDTO
{
   public Vehicle Vehicle { get; set; }
   public ServiceInterval Repair { get; set; }
}

public IQueryable<VehicleServiceDTO> GetVehicles()
{

    return
        from v in _context.Vehicles
        join si in _context.ServiceIntervals on v.Id equals si.VehicleId into loj
        from rs in loj.DefaultIfEmpty()
        where
            v.Schedule == true
            && v.Suspend == false
        select new VehicleServiceDTO() { Vehicle = v, Repair = rs };

}

You can change the types and variable names in the custom DTO class to match the type of _context.ServiceIntervals (I assumed it was called ServiceInterval).

3
7/10/2019 9:17:43 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow