NotImplementedException when filtering on a sub-query

[TobiasMorell]

Describe your issue

I'm trying to produce a query that gathers data from multiple tables (in the example provided below Item and WarehouseStock) and projects that to an aggregate type (ItemStockSummary), which is subsequently filtered by entries in a related table using a sub-query. This produces a NotImplementedException.

It worth noting that if I change the filter to also use a sub-query, it will work. However, that is not possible for me, as I use the HotChocolate GraphQL project in tandem with linq2db and let HotChocolate generate all my SQL queries based on the incoming GraphQL. The example provided here is a cleaned-up version of linq2db's debug output.

Exception message: The method or operation is not implemented.
Stack trace:
System.NotImplementedException
The method or operation is not implemented.
   at LinqToDB.Linq.Builder.SelectContext.GetContext(Expression expression, Int32 level, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.PassThroughContext.GetContext(Expression expression, Int32 level, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionContext.GetContext(Expression expression, Int32 level, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.TableBuilder.BuildSequence(ExpressionBuilder builder, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.AllAnyBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.MethodCallBuilder.BuildSequence(ExpressionBuilder builder, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.GetSubQuery(IBuildContext context, MethodCallExpression expr)
   at LinqToDB.Linq.Builder.ExpressionBuilder.GetSubQueryContext(IBuildContext context, MethodCallExpression expr)
   at LinqToDB.Linq.Builder.ExpressionBuilder.SubQueryToSql(IBuildContext context, MethodCallExpression expression)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertToSql(IBuildContext context, Expression expression, Boolean unwrap, ColumnDescriptor columnDescriptor, Boolean isPureExpression)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertPredicate(IBuildContext context, Expression expression)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSearchCondition(IBuildContext context, Expression expression, List`1 conditions)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildWhere(IBuildContext parent, IBuildContext sequence, LambdaExpression condition, Boolean checkForSubQuery, Boolean enforceHaving)
   at LinqToDB.Linq.Builder.WhereBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.MethodCallBuilder.BuildSequence(ExpressionBuilder builder, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.Build[T]()
   at LinqToDB.Linq.Query`1.CreateQuery(ExpressionTreeOptimizationContext optimizationContext, ParametersContext parametersContext, IDataContext dataContext, Expression expr)
   at LinqToDB.Linq.Query`1.GetQuery(IDataContext dataContext, Expression& expr, Boolean& dependsOnParameters)
   at LinqToDB.Linq.ExpressionQuery`1.GetQuery(Expression& expression, Boolean cache, Boolean& dependsOnParameters)
   at LinqToDB.Linq.ExpressionQuery`1.GetForEachAsync(Action`1 action, CancellationToken cancellationToken)
   at LinqToDB.AsyncExtensions.ToArrayAsync[TSource](IQueryable`1 source, CancellationToken token)
   at Tests.UserTests.UserTest.Test() in /path/to/InventoryQueryTests.cs:line 85
   at Tests.UserTests.UserTest.Test() in /path/to/InventoryQueryTests.cs:line 89
   at Xunit.Sdk.TestInvoker`1.<>c__DisplayClass46_0.<<InvokeTestMethodAsync>b__1>d.MoveNext() in /_/src/xunit.execution/Sdk/Frameworks/Runners/TestInvoker.cs:line 253
--- End of stack trace from previous location ---
   at Xunit.Sdk.ExecutionTimer.AggregateAsync(Func`1 asyncAction) in /_/src/xunit.execution/Sdk/Frameworks/ExecutionTimer.cs:line 48
   at Xunit.Sdk.ExceptionAggregator.RunAsync(Func`1 code) in /_/src/xunit.core/Sdk/ExceptionAggregator.cs:line 90

Steps to reproduce

I've allowed myself to provide a snippet that uses xunit and Testcontainers to spin up a test SQL database for ease of use. In order to run it as is, please install the following packages:

• Testcontainers.MsSql (I use 3.7.0)
• xunit (I use 2.7.0)

using LinqToDB;
using LinqToDB.Data;
using LinqToDB.DataProvider.SqlServer;
using LinqToDB.Mapping;
using System.Data.SqlClient;
using Testcontainers.MsSql;

#nullable disable

namespace Tests.UserTests;

[Table("Item")]
public class Item
{
    [Column("Id", IsPrimaryKey = true, CanBeNull = false)]
    public string Id { get; set; }
}

[Table("WarehouseStock")]
public class WarehouseStock
{
    [Column("Id", IsPrimaryKey = true, CanBeNull = false)]
    public string ItemId { get; set; }

    [Column("QuantityAvailable", CanBeNull = false)]
    public int QuantityAvailable { get; set; }

    [Column("WarehouseId", IsPrimaryKey = true, CanBeNull = false)]
    public string WarehouseId { get; set; }
}

[Table("Review")]
public class Review
{
    [Column("Id", IsPrimaryKey = true, CanBeNull = false)]
    public string ItemId { get; set; }

    [Column("UserId", IsPrimaryKey = true, CanBeNull = false)]
    public string UserId { get; set; }

    [Column("Score", CanBeNull = false)]
    public int Score { get; set; }
}

public class ItemStockSummary
{
    public string ItemId { get; set; }
    public int TotalAvailable { get; set; }

    public IEnumerable<Review> Reviews { get; set; }
}

public class UserTest
{
    [Fact]
    public async Task Test()
    {
        var msSqlContainer = new MsSqlBuilder().Build();
        await msSqlContainer.StartAsync();

        var options = new DataOptions().UseConnection(
            SqlServerTools.GetDataProvider(SqlServerVersion.v2017),
            new SqlConnection(msSqlContainer.GetConnectionString())
        );

        await using var connection = new DataConnection(options);

        await SeedSampleData(connection);

        var query =
            from item in connection.GetTable<Item>()
            from stock in connection
                .GetTable<WarehouseStock>()
                .LeftJoin(s => s.ItemId == item.Id)
                .GroupBy(s => s.ItemId)
            select new ItemStockSummary()
            {
                ItemId = item.Id,
                TotalAvailable = stock.Sum(s => s.QuantityAvailable),
                Reviews = connection.GetTable<Review>().Where(r => r.ItemId == item.Id)
            };

        var filteredByScore = query.Where(i => i.Reviews.Any(r => r.Score > 95));
        // The following statement works and produces the correct results
        // var filteredByScore = query.Where(
        //     i =>
        //         connection
        //             .GetTable<Review>()
        //             .Where(r => r.ItemId == i.ItemId)
        //             .Any(r => r.Score > 95)
        // );

        var result = await filteredByScore.ToArrayAsync();
        var itemSummary = Assert.Single(result);
        Assert.Equal("1", itemSummary.ItemId);
        Assert.Equal(10, itemSummary.TotalAvailable);
        Assert.Equal(2, itemSummary.Reviews.Count());
    }

    private static async Task SeedSampleData(DataConnection connection)
    {
        await connection.CreateTableAsync<Item>();
        await connection.CreateTableAsync<WarehouseStock>();
        await connection.CreateTableAsync<Review>();

        await connection.BulkCopyAsync(
            new[]
            {
                new Item() { Id = "1", },
                new Item() { Id = "2", },
                new Item() { Id = "3", },
            }
        );
        await connection.BulkCopyAsync(
            new[]
            {
                new WarehouseStock()
                {
                    ItemId = "1",
                    QuantityAvailable = 10,
                    WarehouseId = "A",
                }
            }
        );
        await connection.BulkCopyAsync(
            new[]
            {
                new Review()
                {
                    ItemId = "1",
                    UserId = "1",
                    Score = 100,
                },
                new Review()
                {
                    ItemId = "1",
                    UserId = "2",
                    Score = 90,
                },
                new Review()
                {
                    ItemId = "2",
                    UserId = "1",
                    Score = 89,
                },
                new Review()
                {
                    ItemId = "2",
                    UserId = "4",
                    Score = 93,
                },
                new Review()
                {
                    ItemId = "3",
                    UserId = "5",
                    Score = 91,
                },
            }
        );
    }
}

Environment details

Linq To DB version: 5.4.0

Database (with version): SQL Server 2017

ADO.NET Provider (with version): Microsoft.Data.SqlClient 4.8.5

Operating system: Ubuntu 22.04

.NET Version: 8.0.100 runtime, project uses .net7

[MaceWindu]

Well, HotChocolate generates some query expression, that linq2db cannot parse. To make a progress we need to know exact expression if HC could dump it for debug or have complete test setup to debug it.

[MaceWindu]

or your test code is enough to reproduce issue?

[TobiasMorell]

My test code should be enough to reproduce the issue.

[sdanyliv]

I'm avare how LINQ translation works and I'm not sure that we can translate query that you have shown.

Can you rewrite your query in the following way?

var grouping = 
    from stock in connection.GetTable<WarehouseStock>()
    group stock by new { stock.ItemId } into g
    select new 
    {
        g.Key.ItemId,
        TotalAvailable = g.Sum(s => s.QuantityAvailable)
    };

var query =
    from item in connection.GetTable<Item>()
    from stock in grouping
        .LeftJoin(s => s.ItemId == item.Id)
    select new ItemStockSummary()
    {
        ItemId = item.Id,
        TotalAvailable = stock.TotalAvailable,
        Reviews = connection.GetTable<Review>().Where(r => r.ItemId == item.Id)
    };

Or via subquery:

var query =
    from item in connection.GetTable<Item>()
    select new ItemStockSummary()
    {
        ItemId = item.Id,
        TotalAvailable = connection.GetTable<WarehouseStock>()
            .Where(s => s.ItemId == item.Id)
            .Sum(s => s.QuantityAvailable),
        Reviews = connection.GetTable<Review>().Where(r => r.ItemId == item.Id)
    };

[TobiasMorell]

@sdanyliv thank you for the response. Please allow me to clarify; it's not the join onto WarehouseStock that causes the issues here, it's specifically the sub-query and subsequent filter on Reviews that are causing problems for me:

This is fine:

var query =
    from item in connection.GetTable<Item>()
    from stock in connection
        .GetTable<WarehouseStock>()
        .LeftJoin(s => s.ItemId == item.Id)
        .GroupBy(s => s.ItemId)
    select new ItemStockSummary()
    {
        ItemId = item.Id,
        TotalAvailable = stock.Sum(s => s.QuantityAvailable),
        Reviews = connection.GetTable<Review>().Where(r => r.ItemId == item.Id)
    };

var unfiltered = async query.ToArrayAsync();

This is not:

var query =
    from item in connection.GetTable<Item>()
    from stock in connection
        .GetTable<WarehouseStock>()
        .LeftJoin(s => s.ItemId == item.Id)
        .GroupBy(s => s.ItemId)
    select new ItemStockSummary()
    {
        ItemId = item.Id,
        TotalAvailable = stock.Sum(s => s.QuantityAvailable),
        Reviews = connection.GetTable<Review>().Where(r => r.ItemId == item.Id)
    };

var filteredByScore = query.Where(i => i.Reviews.Any(r => r.Score > 95));
//                            ^ It's this filter that causes the exception to occur

[sdanyliv]

Well, I see, it is not translatable until version 6.0 arrive This version has much better LINQ translator and allows such tricks.

@MaceWindu, maybe we can start publishing apha/beta/gamma packages for linq2db 6.0 to Azure Artifacts?

[MaceWindu]

@sdanyliv we can

[TobiasMorell]

Any news on this? An expected release date? Or a preview NuGet package that I may use to assert whether or not version 6.0 fixes this problem?

[MaceWindu]

We expect to release first preview in a couple of weeks after parser PR merged to version_6 branch.