Joining another entity with list navigation property of the queried entity

[chintanr97]

Ask a question

Include your code

I have 3 entity classes (automatically derived from SQL table schema) as follows:

public partial class Vehicle
{
	public Vehicle()
	{
		Wheels = new HashSet<Wheel>();
	}

	public string Id { get; set; }
	public string Name { get; set; }
	public string Description { get; set; }

	public virtual ICollection<Wheel> Wheels { get; set; }
}

public partial class Part
{
	public Part() {}

	public string Id { get; set; }
	public string Name { get; set; }
	public int Count { get; set; }
}

public partial class Wheel
{
	public Wheel()
        {
              Vehicle = new Vehicle();
        }

	public string Id { get; set; }
	public string PartId { get; set; }
        public string VehicleId { get; set; }

       public virtual Vehicle Vehicle { get; set; }
}

Further the query command is written as follows: The intention of the query is to include details of wheel

var id = "<sample-id>";
var vehicles = await this.context.Vehicles
                .Where(vehicle =>
                    vehicle.Id == id)
                .GroupJoin(
                    this.context.Wheels
                        .Join(
                            this.context.Parts,
                            wheel => wheel.PartId,
                            part => part.Id,
                            (wheel, part) => new { Part = part })
                        .ToList(),
                    vehicle => vehicle.Id,
                    wheelsWithPart => wheelsWithPart.VehicleId,
                    (vehicle, wheelsWithPart) =>
                        new { Vechicle = vechicle, WheelsWithPart = wheelsWithPart })
                .ToListAsync(cancellationToken);

However, the following query does not work and gives error as highlighted in the stack trace below. I wanted to understand how we can optimally join the list navigation property with a third-property in a single query using EF core. Ideally we would have had the foreign key relationship between the wheel and the part - but for our use case we cannot have that in this scenario.

Include stack traces

Include the full exception message and stack trace for any exception you encounter.

 ---> System.InvalidOperationException: The LINQ expression 'DbSet<Vehicle>()
    .Where(vehicle => vehicle.Id == __id_0)
    .GroupJoin(
        inner: __p_2,
        outerKeySelector: vehicle => vehicle.Id,
        innerKeySelector: wheelsWithPart => wheelsWithPart.VehicleId,
        resultSelector: (vehicle, wheelsWithPart) => new {
            Vehicle = vehicle,
            WheelsWithPart = wheelsWithPart
        }
    )' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPreprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)

Include provider and version information

EF Core version: 5.0
Database provider: Microsoft.Data.SqlClient
Target framework: .NET 5.0
Operating system: Windows 10
IDE: Visual Studio Enterprise 2019 16.9.7

[roji]

Duplicate of #19930

[chintanr97]

Thank you @roji for the link. I was able to get the results with the following query:

var vehicles = await this.context.Vehicles
	.Select(vehicle =>
		new
		{
			Vehicle = vehicle,
			WheelsWithPart = vehicle.Wheels
				.Join(
					this.context.Parts
						.Include(part => part.Dealer)
						.Include(part => part.Sponsor),
					wheel => wheel.PartId,
					part => part.Id,
					(wheel, part) => new { Wheel = wheel, Part = part })
				.ToList(),
		})
	.AsSingleQuery()
	.FirstOrDefaultAsync(vehicle => vehicle.Vehicle.Id == id, cancellationToken);

We had to use AsSingleQuery as we are using EF core 5.0 yet and I saw that EF core 6.0 has the changes for supporting split queries for non-navigation collections. We will try upgrading to EF core 6.0 at some time.