Return an IQueryable that joins two tables

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.

Answer 1

I don't necessarily think you shouldn't use a navigation property here. They are virtual and a query is generated base on the Linq statement.

I also don't think a separate DTO model makes sense in this case. I'm making an assumption, since you mentioned a navigation property, that you have a 1-many relationship. My guess is that the vehicle should be scheduled with a collection of repairs. If that is the case, then maybe a GroupJoin would work. It is supported by EF: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/supported-and-unsupported-linq-methods-linq-to-entities

I added a property to hold the repairs to the Vehicle:

public IEnumerable<ServiceInterval> RepairsToSchedule { get; set; }

Then the query ends up looking something like this:

public static IQueryable<Vehicle> GetVehicles()
{
    return _context.Vehicles
        .Where(v => v.Schedule && !v.Suspend)
        .GroupJoin(_context.ServiceIntervals,
            v => v.Id,
            si => si.VehicleId,
            (v, si) => SetServiceIntervals(v, si));
}

I used the static method below to add the ServiceIntervals to the Vehicle:

private static Vehicle SetServiceIntervals(Vehicle v, IEnumerable<ServiceInterval> si)
{
    v.RepairsToSchedule = si;
    return v;
}

The entire source code is below: Edited: adjusted for two separate database (note, on same server)

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;

namespace StackoverFlow
{
    class Program
    {
        private static FakeDatabaseContext _context = new FakeDatabaseContext();
        private static FakeDatabaseContext2 _context2 = new FakeDatabaseContext2();

        static void Main(string[] args)
        {
            CleanContext();
            LoadContext();

            foreach (var vehicle in GetVehicles())
            {
                Console.WriteLine(JsonConvert.SerializeObject(vehicle, Formatting.Indented));
                Console.WriteLine();
            }
            Console.ReadKey();
        }

        public static IQueryable<Vehicle> GetVehicles()
        {
            return _context.Vehicles
                .Where(v => v.Schedule && !v.Suspend)
                .GroupJoin(_context.ServiceIntervals(new DateTime(), new DateTime()),
                    v => v.Id,
                    si => si.VehicleId,
                    (v, si) => SetServiceIntervals(v, si));
        }

        private static Vehicle SetServiceIntervals(Vehicle v, IEnumerable<ServiceInterval> si)
        {
            v.RepairsToSchedule = si;
            return v;
        }

        #region EF Context
        public class FakeDatabaseContext : DbContext
        {
            public DbSet<Vehicle> Vehicles { get; set; }
            private DbSet<ServiceInterval> _serviceIntervals { get; set; }

            public IQueryable<ServiceInterval> ServiceIntervals(DateTime startingDate, DateTime endingDate)
            {
                return _serviceIntervals.FromSql($@"
                    SELECT *
                    FROM Stackoverflow2.dbo.ServiceIntervals"
                );
            }

            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.UseSqlServer(
                    @"Server=(localdb)\mssqllocaldb;Database=Stackoverflow;Integrated Security=True");
                optionsBuilder
                    .ConfigureWarnings(w => w.Throw(RelationalEventId.QueryClientEvaluationWarning));
            }
        }

        // Used to load a seperate database
        public class FakeDatabaseContext2 : DbContext
        {
            public DbSet<ServiceInterval> ServiceIntervals { get; set; }

            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.UseSqlServer(
                    @"Server=(localdb)\mssqllocaldb;Database=Stackoverflow2;Integrated Security=True");
                optionsBuilder
                    .ConfigureWarnings(w => w.Throw(RelationalEventId.QueryClientEvaluationWarning));
            }
        }

        public class Vehicle
        {
            public string Id { get; set; }
            public bool Schedule { get; set; }
            public bool Suspend { get; set; }
            public IEnumerable<ServiceInterval> RepairsToSchedule { get; set; }
        }

        public class ServiceInterval
        {
            public string Id { get; set; }
            public string VehicleId { get; set; }
        }
        #endregion EF Context

        #region Seed methods
        private static Random _random = new Random();
        private static bool _randomBool => _random.Next() % 2 == 1;

        private static void LoadContext()
        {
            var maxVehicles = 10;
            for (int i = 1; i < maxVehicles; i++)
            {
                _context.Vehicles.Add(new Vehicle { Id = i.ToString(), Schedule = _randomBool, Suspend = _randomBool });

                for (int o = 1; o < _random.Next(10); o++)
                {
                    _context2.ServiceIntervals.Add(new ServiceInterval { Id = ((maxVehicles * i) + o).ToString(), VehicleId = i.ToString() });
                }
            };

            _context.SaveChanges();
            _context2.SaveChanges();
        }

        private static void CleanContext()
        {
            _context.Vehicles.RemoveRange(_context.Vehicles.ToArray());
            _context2.ServiceIntervals.RemoveRange(_context2.ServiceIntervals.ToArray());
            _context.SaveChanges();
            _context2.SaveChanges();
        }
        #endregion Seed methods
    }
}

IntelliTrace showed this query was executed:

SELECT [v].[Id], [v].[Schedule], [v].[Suspend], [si].[Id], [si].[VehicleId]
FROM [Vehicles] AS [v]
LEFT JOIN (

                        SELECT *
                        FROM Stackoverflow2.dbo.ServiceIntervals
) AS [si] ON [v].[Id] = [si].[VehicleId]
WHERE ([v].[Schedule] = 1) AND ([v].[Suspend] = 0)
ORDER BY [v].[Id]

I tested this a few times, and everything appears to be working fine.

Setup Project Notes:

1. This is a .NET Core 2.1 Console Application
2. Install-Package Microsoft.EntityFrameworkCore -Version 2.2.6
3. Install-Package Microsoft.EntityFrameworkCore.Design -Version 2.2.6
4. Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 2.2.6
5. Install-Package Microsoft.EntityFrameworkCore.SqlServer.Design -Version 1.1.6
6. Open cmd window and navigate to project folder
7. Run in Console: dotnet ef migrations add InitialCreate
8. Run in Console: dotnet ef database update

Answer 2

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).