Combination of Where() + OrderBy() breaks query generation on transformed new objects; Works separately

[ghost]

Describe your issue

When DB entity is selected into a new object (let's say a DTO) and then both filtering and ordering is done on that DTO, then query generation breaks. When only filtering or only ordering is done on that DTO, then query is generated properly.

Exception message: Expression 'y.Currency' is not a Field.
Stack trace:
linq2db
   at LinqToDB.Linq.Builder.TableBuilder.TableContext.ConvertToSql(Expression expression, Int32 level, ConvertFlags flags)
   at LinqToDB.Linq.Builder.SelectContext.<>c.<ConvertToSql>b__42_0(ValueTuple`3 context, IBuildContext ctx, Expression ex, Int32 l)
   at LinqToDB.Linq.Builder.SelectContext.ProcessScalar[T,TContext](TContext context, Expression expression, Int32 level, Func`5 action, Func`2 defaultAction, Boolean throwOnError)
   at LinqToDB.Linq.Builder.SelectContext.ConvertToSql(Expression expression, Int32 level, ConvertFlags flags)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertToSql(IBuildContext context, Expression expression, Boolean unwrap, ColumnDescriptor columnDescriptor, Boolean isPureExpression)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertExpressions(IBuildContext context, Expression expression, ConvertFlags queryConvertFlag, ColumnDescriptor columnDescriptor)
   at LinqToDB.Linq.Builder.SelectContext.ConvertToSql(Expression expression, Int32 level, ConvertFlags flags)
   at LinqToDB.Linq.Builder.SelectContext.ConvertToIndexInternal(Expression expression, Int32 level, ConvertFlags flags)
   at LinqToDB.Linq.Builder.SelectContext.ConvertToIndex(Expression expression, Int32 level, ConvertFlags flags)
   at LinqToDB.Linq.Builder.SubQueryContext.ConvertToSql(Expression expression, Int32 level, ConvertFlags flags)
   at LinqToDB.Linq.Builder.SubQueryContext.ConvertToIndex(Expression expression, Int32 level, ConvertFlags flags)
   at LinqToDB.Linq.Builder.SetOperationBuilder.SetOperationContext.AddSequence(SubQueryContext sequence, SqlSetOperator setOperator)
   at LinqToDB.Linq.Builder.SetOperationBuilder.SetOperationContext..ctor(SubQueryContext sequence1, SubQueryContext sequence2, MethodCallExpression methodCall, SqlSetOperator setOperator)
   at LinqToDB.Linq.Builder.SetOperationBuilder.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.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.OrderByBuilder.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.SelectBuilder.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.get_SqlText()
   at LinqToDB.Linq.ExpressionQueryImpl`1.ToString()
   at Program.<Main>$(String[] args) in C:\src\c#\Linq2DbBug\Linq2DbBug\Program.cs:line 27

Steps to reproduce

A example contains 2 entities and 2 DTOs: TransactionEntity/TransactionDto and LineEntity/LineDto. The DTOs define an expression to transform from *Entity to *Dto.

// Entities

[Table]
public record TransactionEntity
{
    [PrimaryKey]
    public Guid Id { get; set; }

    [Column]
    public DateTime ValidOn { get; set; }

    [Association(ThisKey = nameof(Id), OtherKey = nameof(LineEntity.TransactionId))]
    public List<LineEntity> Lines { get; set; } = null!;
}

[Table]
public record LineEntity
{
    [PrimaryKey]
    public Guid Id { get; set; }

    [Column]
    public Guid TransactionId { get; set; }

    [Column]
    public decimal Amount { get; set; }

    [Column]
    public string Currency { get; set; } = null!;

    [Association(ThisKey = nameof(TransactionId), OtherKey = nameof(TransactionEntity.Id), CanBeNull = false)]
    public TransactionEntity Transaction { get; set; } = null!;
}

// DTOs

public record TransactionDto
{
    public Guid Id { get; set; }

    public DateTime ValidOn { get; set; }

    public IEnumerable<LineDto> Lines { get; set; } = Enumerable.Empty<LineDto>();

    [ExpressionMethod(nameof(FromEntityExpression))]
    public static TransactionDto FromEntity(TransactionEntity entity)
        => FromEntityExpression().Compile()(entity);

    static Expression<Func<TransactionEntity, TransactionDto>> FromEntityExpression() =>
        entity => new TransactionDto
        {
            Id = entity.Id,
            ValidOn = entity.ValidOn,
            Lines = entity.Lines.Select(line => LineDto.FromEntity(line))
        };
}

public record LineDto
{
    public Guid Id { get; set; }

    public decimal Amount { get; set; }

    public string Currency { get; set; } = null!;

    [ExpressionMethod(nameof(FromEntityExpression))]
    public static LineDto FromEntity(LineEntity entity)
        => FromEntityExpression().Compile()(entity);

    static Expression<Func<LineEntity, LineDto>> FromEntityExpression()
        => entity => new LineDto
        {
            Id = entity.Id,
            Amount = entity.Amount,
            Currency = entity.Currency
        };
}

To produce an error, following code can be used in program.cs:

using LinqToDB;
using LinqToDB.Data;
using LinqToDB.Mapping;
using System.Linq.Expressions;

var db = new DataConnection(
    new DataOptions()
        .UseSQLite("Data Source=:memory:;Version=3;New=True;")
);

db.CreateTable<TransactionEntity>();
db.CreateTable<LineEntity>();

var currencies = new[] { "A", "B" };

var q = db.GetTable<TransactionEntity>()
    .Select(x => new
    {
        Entity = x,
        Dto = TransactionDto.FromEntity(x)
    })
    .Where(x => x.Dto.Lines.Select(y => y.Currency).Intersect(currencies).Any()) // <-- FILTER
    .OrderBy(x => x.Dto.ValidOn) // <-- ORDER BY
    .Select(x => x.Dto)
;

Console.WriteLine("Query: " + q.ToString()); // <-- Exception thrown

q.ToList();

Console.ReadKey();

// Include the record definitions here...

Lines with comments <-- FILTER and <-- ORDER BY work only, if the other one is commented out. If both lines are applied, the query generation fails with exception described above.

Environment details

Linq To DB version: 5.3.2

Database (with version): N/A

ADO.NET Provider (with version): System.Data.SQLite.Core 1.0.118

Operating system: Windows 11 22H2

.NET Version: 7.0

[Seramis]

Just tested with Linq2Db 6.0.0-preview-1 and unfortunately neiteher the Where() nor OrderBy() could even be parsed on their own.
For Where() condition, I also tried a.Any(x => b.Any(y => x == y)) type of approach instead of Intersect(), but the result was same - exception that this part of the query could not be parsed.

Seems like the Linq translator has some general issues regarding projected results. I did notice that the Linq in exception defines some "alias" names in it, and i see the same name multiple times. Not sure if it is actual problem.

With only OrderBy() being used, i get this kind of exception:

The LINQ expression could not be converted to SQL.
Expression:
db
	.GetTable<TransactionEntity>()
	.Select(x => new { 
		Entity = x, 
		Dto = Sql.Alias<TransactionDto>(
			obj: new TransactionDto{ 
				Id = x.Id, 
				ValidOn = x.ValidOn, 
				Lines = x.Lines
					.Select(line => Sql.Alias<LineDto>(
						obj: new LineDto{ 
							Id = line.Id, 
							Amount = line.Amount, 
							Currency = line.Currency 
						}
						, 
						alias: "FromEntity")) 
			}
			, 
			alias: "FromEntity")
	 })
	.OrderBy(x => x.Dto.ValidOn)

BTW, I absolutely LOVE the new exceptions that actually show a expanded LINQ in it! ❤️ 🚀