Версия 2.2, MS SQL Server 2016

Есть две таблицы:

[Table("Authors")]
public class Author
{
    [PrimaryKey, Identity]
    public int Id { get; set; }

    [Column(CanBeNull = false)]
    public string Name { get; set; }
}

[Table("Books")]
public class Book
{
    [PrimaryKey, Identity]
    public int Id { get; set; }

    [Column(CanBeNull = false)]
    public int AuthorId { get; set; }

    [Column(CanBeNull = false)]
    public string Title { get; set; }
}

Обращение к ним идёт через две переменные:

var authors = db.GetTable<Author>();
var books = db.GetTable<Book>();

Запрос

from author in authors
let booksCount =
(
    from book in books
    where book.AuthorId == author.Id
    select Sql.Ext.Count(book.Id).ToValue()
).Single()
where booksCount > 42
select new
{
    author.Name,
    BooksCount = booksCount
}

генерирует следующий SQL код:

SELECT
        [t3].[Name],
        [t2].[c1] as [c11],
        [t2].[c2] as [c21]
FROM
        [Authors] [t3]
                OUTER APPLY (
                        SELECT
                                COUNT([t1].[Id]) as [c1],
                                1 as [c2]
                        FROM
                                [Books] [t1]
                        WHERE
                                [t1].[AuthorId] = [t3].[Id]
                ) [t2]
WHERE
        (
                SELECT
                        COUNT([t4].[Id])
                FROM
                        [Books] [t4]
                WHERE
                        [t4].[AuthorId] = [t3].[Id]
        ) > 42

Запрос получается неестественный и, возможно, не оптимальный.

Запрос

from author in authors
let booksAgg =
(
    from book in books
    where book.AuthorId == author.Id
    select new
    {
        Count = Sql.Ext.Count(book.Id).ToValue()
    }
).Single()
where booksAgg.Count > 42
select new
{
    author.Name,
    BooksCount = booksAgg.Count
}

падает:

LinqToDB.Linq.LinqException: 'Table([Books]).Where(book => (book.AuthorId == <>h__TransparentIdentifier0.author.Id)).Select(book => new <>f__AnonymousType5`1(Count = Sql.Ext.Count(book.Id).ToValue())).Single().Count' cannot be converted to SQL.
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertToSql(IBuildContext context, Expression expression, Boolean unwrap) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.SqlBuilder.cs:line 1029
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertCompare(IBuildContext context, ExpressionType nodeType, Expression left, Expression right) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.SqlBuilder.cs:line 1588
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertPredicate(IBuildContext context, Expression expression) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.SqlBuilder.cs:line 1370
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSearchCondition(IBuildContext context, Expression expression, List`1 conditions) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.SqlBuilder.cs:line 2529
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildWhere(IBuildContext parent, IBuildContext sequence, LambdaExpression condition, Boolean checkForSubQuery, Boolean enforceHaving) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.SqlBuilder.cs:line 48
   at LinqToDB.Linq.Builder.WhereBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\WhereBuilder.cs:line 23
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.cs:line 175
   at LinqToDB.Linq.Builder.SelectBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SelectBuilder.cs:line 36
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.cs:line 175
   at LinqToDB.Linq.Builder.ExpressionBuilder.Build[T]() in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.cs:line 146
   at LinqToDB.Linq.Query`1.CreateQuery(IDataContext dataContext, Expression expr) in C:\projects\linq2db\Source\LinqToDB\Linq\Query.cs:line 280
   at LinqToDB.Linq.Query`1.GetQuery(IDataContext dataContext, Expression& expr) in C:\projects\linq2db\Source\LinqToDB\Linq\Query.cs:line 233
   at LinqToDB.Linq.ExpressionQuery`1.GetQuery(Expression& expression, Boolean cache) in C:\projects\linq2db\Source\LinqToDB\Linq\ExpressionQuery.cs:line 84
   at LinqToDB.Linq.ExpressionQuery`1.get_SqlText() in C:\projects\linq2db\Source\LinqToDB\Linq\ExpressionQuery.cs:line 53
   at ConsoleApp.Program.WorkWithDb(DbMain db) in C:\Src\Personal\TestPolygon\ConsoleApp\Program.cs:line 68
   at ConsoleApp.Program.WorkWithDb(String dataSource, String initialCatalog) in C:\Src\Personal\TestPolygon\ConsoleApp\Program.Db.cs:line 57
   at ConsoleApp.Program.Main() in C:\Src\Personal\TestPolygon\ConsoleApp\Program.Core.cs:line 21

Если делать фильтрацию попозже:

var query =
    from author in authors
    let booksAgg =
    (
        from book in books
        where book.AuthorId == author.Id
        select new
        {
            Count = Sql.Ext.Count(book.Id).ToValue()
        }
    ).Single()
    select new
    {
        author.Name,
        BooksCount = booksAgg.Count
    };

query = query.Where(x => x.BooksCount > 42);

то падает с другим исключением:

System.NotImplementedException: The method or operation is not implemented.
   at LinqToDB.Linq.Builder.SelectContext.ProcessMemberAccess[T](Expression expression, MemberExpression levelExpression, Int32 level, Func`6 action) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SelectContext.cs:line 974
   at LinqToDB.Linq.Builder.SelectContext.IsExpressionInternal(Expression expression, Int32 level, RequestFor requestFlag) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SelectContext.cs:line 731
   at LinqToDB.Linq.Builder.SelectContext.IsExpression(Expression expression, Int32 level, RequestFor requestFlag) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\SelectContext.cs:line 627
   at LinqToDB.Linq.Builder.ExpressionContext.IsExpression(Expression expression, Int32 level, RequestFor requestFlag) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionContext.cs:line 116
   at LinqToDB.Linq.Builder.ExpressionBuilder.<>c__DisplayClass97_0.<CheckSubQueryForWhere>b__0(Expression expr) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.SqlBuilder.cs:line 90
   at LinqToDB.Expressions.Extensions.Visit(Expression expr, Func`2 func) in C:\projects\linq2db\Source\LinqToDB\Expressions\Extensions.cs:line 373
   at LinqToDB.Expressions.Extensions.Visit(Expression expr, Func`2 func) in C:\projects\linq2db\Source\LinqToDB\Expressions\Extensions.cs:line 418
   at LinqToDB.Linq.Builder.ExpressionBuilder.CheckSubQueryForWhere(IBuildContext context, Expression expression, Boolean& makeHaving) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.SqlBuilder.cs:line 149
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildWhere(IBuildContext parent, IBuildContext sequence, LambdaExpression condition, Boolean checkForSubQuery, Boolean enforceHaving) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.SqlBuilder.cs:line 32
   at LinqToDB.Linq.Builder.WhereBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\WhereBuilder.cs:line 23
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo) in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.cs:line 175
   at LinqToDB.Linq.Builder.ExpressionBuilder.Build[T]() in C:\projects\linq2db\Source\LinqToDB\Linq\Builder\ExpressionBuilder.cs:line 146
   at LinqToDB.Linq.Query`1.CreateQuery(IDataContext dataContext, Expression expr) in C:\projects\linq2db\Source\LinqToDB\Linq\Query.cs:line 280
   at LinqToDB.Linq.Query`1.GetQuery(IDataContext dataContext, Expression& expr) in C:\projects\linq2db\Source\LinqToDB\Linq\Query.cs:line 233
   at LinqToDB.Linq.ExpressionQuery`1.GetQuery(Expression& expression, Boolean cache) in C:\projects\linq2db\Source\LinqToDB\Linq\ExpressionQuery.cs:line 84
   at LinqToDB.Linq.ExpressionQuery`1.get_SqlText() in C:\projects\linq2db\Source\LinqToDB\Linq\ExpressionQuery.cs:line 53
   at ConsoleApp.Program.WorkWithDb(DbMain db) in C:\Src\Personal\TestPolygon\ConsoleApp\Program.cs:line 22
   at ConsoleApp.Program.WorkWithDb(String dataSource, String initialCatalog) in C:\Src\Personal\TestPolygon\ConsoleApp\Program.Db.cs:line 56
   at ConsoleApp.Program.Main() in C:\Src\Personal\TestPolygon\ConsoleApp\Program.Core.cs:line 21

Для меня больше важен вариант с

from book in books
where book.AuthorId == author.Id
select new
{
    Count = Sql.Ext.Count(book.Id).ToValue()
}

потому что на практике надо считать по подзапросу не один агрегат, а несколько.