ISelect能否实现OUTER APPLY

[tky753]

想要实现这么一个查询

-- 查询歌曲名和第一作者（可能多个作者）
SELECT A.Name as Name, B.Name as AuthorName FROM Song A
OUTER APPLY(SELECT TOP 1 Name FROM Author WHERE A.ID = SongId Order By Id) B

OUTER APPLY没有在FreeSql的ISelect找到相应实现，也可能是我找的不够仔细，望指教。
如果没有的话，个人有一些小想法，看看可不可行.
想法一：
为ISelect提供FromRaw 方法，允许在Frow后面插入raw sql
为ISelectFromExpression提供Ignore<TX>方法，忽略对From实体的动作

var sql = fsql.Select<Song>()
    .From<Author>((a,b)=>a.Ignore<Author>()) //新增Ignore方法实现忽略对Author表的From动作
    .FromRaw("OUTER APPLY(SELECT TOP 1 Name FROM Author WHERE A.ID = SongId Order By Id) b") 
    .ToSql((a, b) => new SongDto
    {
        Name = a.Name,
        AuthorName = b.Name
    });
// 不过FrowRaw和WithSql有歧义，这是个问题，可以想想换个名字

想法二：
为ISelectFromExpression提供WithSql<TX>(string)方法，自定义对From实体的关联动作

var sql = fsql.Select<Song>()
    .From<Author>((a,b)=>a.WithSql<Author>("OUTER APPLY(SELECT TOP 1 Name FROM Author WHERE A.ID = SongId Order By Id)"))
    .ToSql((a, b) => new SongDto
    {
        Name = a.Name,
        AuthorName = b.Name
    });

想法三：
允许Join子ISelect，并且可以自定义 JOIN 关键字

var sql = fsql.Select<Song>()
    .From<Author>((a,b)=>
        a.WiseJoin(a1 => fsql.Select<Author>().Take(1).Where(b1 => a1.Id == b1.SongId), "OUTER APPLY")
    .ToSql((a, b) => new SongDto
    {
        Name = a.Name,
        AuthorName = b.Name
    });
// 这个感觉很难吧，要解析一个子ISelect
//灵感来源于SqlKata 的 
public Query Join(string table, Func<Join, Join> callback, string type = "inner join")

粗陋想法，希望能够助力FreeSql变得越来越强大。👍

[2881099]

不容易，除了要考虑语法实现，还要考虑多表查询时匹配。cross apply没使用过，其他数据库有类似的用法吗？

[tky753]

可以考虑先优化一下 join 子sql，虽然现在的ISelect<>.XXXJoin()方法支持传sql字符串，
但ISelectFromExpression<>.XXXJoin()不支持传sql字符串
导致后面ToXXX()的时候够不到 join 子sql的字段，希望能够提供解决方案。

var sql = fsql.Select<Song>()
    .InnerJoin("(SELECT * FROM Author) b ON a.Id = b.SongId")
    .From<Author>((a,b)=>a)
    .ToSql((a,b)=>new SongDto
    {
        Name = a.TitleName,
        AuthorName = b.Name
    });

--希望的sql
SELECT b.[Name] as1, a.[S_TITLE] as2, b.[Name] as3
FROM [Song] a
INNER JOIN (SELECT * FROM Author) b ON a.Id = b.SongId

--实际的sql
SELECT b.[Name] as1, a.[S_TITLE] as2, b.[Name] as3
FROM [Song] a, [Author] b   -- ,[Author] b 可以想法子去掉吗
INNER JOIN (SELECT * FROM Author) b ON a.Id = b.SongId

[2881099]

/// <summary>
/// 设置别名规则，可用于拦截表别名，实现类似 sqlserver 的 with(nolock) 需求<para></para>
/// 如：select.AsAlias((_, old) => $"{old} with(lock)")
/// </summary>
/// <param name="aliasRule"></param>
/// <returns></returns>
TSelect AsAlias(Func<Type, string, string> aliasRule);

哥们是否注意了这个方法，当时为实现 with(nolock) 功能做的。还有一个 AsTable 方法，AsTable 是 WithSql 的支撑。最近没怎么开电脑。

var otolst3 = g.sqlserver.Select<otot1, otot2>()
    .AsTable((type, old) => type == typeof(otot2) ? 
        "(SELECT TOP 1 Name FROM Author WHERE A.ID = SongId Order By Id)" : null)
    .InnerJoin((a, b) => a.id == b.id)
    .ToSql((a, b) => new
    {
        a,
        b
    });

得到的SQL：

（注意 AsTable 两侧要加括号才能生效，比较隐晦）

SELECT a.[id] as1, a.[name] as2, b.[id] as3, b.[title] as4 
FROM [otot1] a 
INNER JOIN (SELECT TOP 1 Name FROM Author WHERE A.ID = SongId Order By Id) b ON a.[id] = b.[id]

[tky753]

@2881099 谢谢，inner join 子sql 可以了

var sql = fsql.Select<Song>()
    .AsTable((type, old) => type == typeof(Author) ? 
        "(SELECT Name FROM Author WHERE Order By Id Desc)" : null)
    .From<Author>((a,b) => a.InnerJoin(a1 => a1.Id == b.SongId))
    .ToSql((a, b) => new SongDto
    {
//        Name = a.Name,
        AuthorName = b.Name
    });

--生成的sql
SELECT a."Name" as1, b."Name" as2
FROM "Song" a
INNER JOIN (SELECT Name FROM Author WHERE Order By Id Desc) b ON a."Id" = b."SongId"

[2881099]

/// <summary>
/// 在 JOIN 位置插入 SQL 内容<para></para>
/// 如：.RawJoin("OUTER APPLY ( select id from t2 ) b")
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
TSelect RawJoin(string sql);

看看，加这个方法是否可行。

.RawJoin("OUTER APPLY ( select id from t2 ) b")
.ToList(a => {
    a.Id,
    bId = Convert.ToInt32("b.id") //利用这个隐晦的功能
});

[tky753]

这个可以啊，不过是否和
TSelect LeftJoin(string sql, object parms = null)
TSelect InnerJoin(string sql, object parms = null)
TSelect RightJoin(string sql, object parms = null)
产生了一些重合

[2881099]

是有点重合，但是可以同时存在，老的方法已经有人在用了，可行的话就这样吧， RawJoin

[tky753]

好的

[tky753]

话说这隐晦功能是真够隐晦的 Convert.ToInt32("b.id")
如果括号内不是常量字符串就会变成 类型转换语句呢
比如 var s="b.Id"; Convert.ToInt32(s)
或者 Convert.ToInt32($"b.{nameof(Id)}")

[2881099]

是的是的，必须常量防止有正常的字符串需求