v3.2 新功能【嵌套查询】意见征集

[2881099]

需求版本：v3.2.666-preview (最新版本)

文档地址：https://github.com/dotnetcore/FreeSql/wiki/%e5%b5%8c%e5%a5%97%e6%9f%a5%e8%af%a2

目前 API 还未正式确定和发布，这里是征集嵌套查询的相关意见。

如果你有好的想法和建议欢迎发表意见，建议同时提供对应的伪代码，以便大家沟涌。

谢谢大家！

[LeaFrock]

这里记录一下踩的一个坑，尽管应该不是bug，不过使用者需要注意。

下面的代码是统计未参加某场考试的学生数，TAID是TestAnswer表的主键（int）。

var missingStudentCount = await FreeSql.Select<ClassStudent, TestAnswer>()
                .LeftJoin(p => p.t1.StudentNo == p.t2.StudentNo && p.t2.TestID == 1)
                .Where(p => new int[]{1,2,3}.Contains(p.t1.ClassId))
                .WithTempQuery(p => new { p.t1.StudentNo, p.t2.TAID })
                .Where(t => t.TAID == 0)
                .CountAsync();

生成SQL：

SELECT count(1) as1 
FROM ( 
    SELECT a.[StudentNo], b.[TAID]
    FROM [ClassStudent] a 
    LEFT JOIN [TestAnswer] b ON a.[StudentNo] = b.[StudentNo] AND b.[TestID] = 1
    WHERE (((a.[ClassId]) in (1,2,3)) ) a 
WHERE (a.[TAID] = 0)

在代码层面TAID是不为Null的int类型，但数据库执行层面，左联后的结果里未匹配行的TAID是Null值。这导致生成的SQL最后Where条件始终返回False。

做一点微小修改即可避免此问题：

                .WithTempQuery(p => new { p.t1.StudentNo, TAID = SqlExt.IsNull(p.t2.TAID, default) })

生成SQL：

SELECT count(1) as1 
FROM ( 
    SELECT a.[StudentNo], isnull(b.[TAID], 0) [TAID] 
    FROM [ClassStudent] a 
    LEFT JOIN [TestAnswer] b ON a.[StudentNo] = b.[StudentNo] AND b.[TestID] = 1
    WHERE (((a.[ClassId]) in (1，2，3))) ) a 
WHERE (a.[TAID] = 0)

[2881099]

NULL 问题 Where 确实存在，点赞

[2881099]

如果TAID 是 int?，可以使用语法糖

TAID = p.t2.TAID ?? default

[luoyunchong]

var missingStudentCount = await FreeSql.Select<ClassStudent, TestAnswer>()
                .LeftJoin(p => p.t1.StudentNo == p.t2.StudentNo && p.t2.TestID == 1&&p.t2.TAID==0)
                .Where(p => new int[]{1,2,3}.Contains(p.t1.ClassId))
                .WithTempQuery(p => new { p.t1.StudentNo, p.t2.TAID })
                .CountAsync();

或者条件都写在join中?

[LeaFrock]

@luoyunchong 不行的。没参加考试的话，对应的StudentNo是没有TestAnswer数据的，因此采用left join，通过判断右表TestAnswer的数据是否存在就知道是否参加了考试。这里我只取了主键TAID。代码层面不存在的数据主键默认值应该是0，但数据库层面其实是Null。on条件里加了TAID=0的话右表将始终为空表了。

[luoyunchong]

@LeaFrock 哦哦，是统计未参加。那只能用二次过滤。

[Rorschach331]

var items= fsql.Select<TableA,TableB>()
    ..InnerJoin(o => o.t1.Id== o.t2.AId)
    .WithTempQuery(o=>new Dto
    {
        Id = o.t1.Id,
        //需要把Dto映射写完整 ，自动推导的话两个表有列名相同报错          
    })
    .......
    .ToList();

请问大佬，WithTempQuery查询Dto属性名相同的问题在3.2.6+的计划中吗？

[2881099]

目前确实有这个缺陷，尽量吧。

[2881099]

v3.2.670 解决同别名问题。

var sql18 = fsql.Select<TwoTablePartitionBy_User>()
    .WithTempQuery(a => new { UserId = a.Id, a.Nickname })
    .From<TwoTablePartitionBy_UserExt>()
    .InnerJoin((a, b) => a.UserId == b.UserId)
    .WithTempQuery((a, b) => new
    {
        User = a,
        UserExt = b,
        rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.UserId).ToValue()
    })
    .Where(a => a.rownum == 1 && a.UserExt.UserId > 0 && a.User.UserId > 0)
    .WithTempQuery(a => new
    {
        Item = a,
        a.User,
        a.UserExt,
        a.rownum
    })
    .Where(a => a.rownum == 1 && a.UserExt.UserId > 0 && a.User.UserId > 0)
    .Where(a => a.Item.UserExt.UserId > 0 && a.Item.User.UserId > 0)
    .ToSql();

SELECT * 
FROM ( 
    SELECT a.[UserId], a.[Nickname], a.[UserId2], a.[Remark], a.[rownum], a.[UserId] [UserId3], a.[Nickname] [Nickname2], a.[UserId2] [UserId22], a.[Remark] [Remark2], a.[rownum] [rownum2] 
    FROM ( 
        SELECT a.[UserId], a.[Nickname], b.[UserId] [UserId2], b.[Remark], row_number() over( partition by a.[Nickname] order by a.[UserId]) [rownum] 
        FROM ( 
            SELECT a.[Id] [UserId], a.[Nickname] 
            FROM [TwoTablePartitionBy_User] a ) a 
        INNER JOIN [TwoTablePartitionBy_UserExt] b ON a.[UserId] = b.[UserId] ) a 
    WHERE (a.[rownum] = 1 AND a.[UserId2] > 0 AND a.[UserId] > 0) ) a 
WHERE (a.[rownum2] = 1 AND a.[UserId22] > 0 AND a.[UserId3] > 0) AND (a.[UserId2] > 0 AND a.[UserId] > 0)

[yang-HL]

this._repo.Orm.Select<TUserLineUpGroup, TUserLineUp, TStaffReservation, TUserSap, TStaffDoctor>()
.Where((a, b, c, d, e) => a.LineUpId == b.Id && b.StaffReservationId == c.Id && c.Uid == d.Uid && c.SapNumber == d.SapNumber && c.DoctorId == e.Id)
.WithTempQuery((a, b, c, d, e) => new
{
lgroup = a,
lup = b,
sr = c,
user = d,
doc = e
})
.UnionAll(
this._repo.Orm
.Select<TUserLineUpGroupHistory, TUserLineUpHistory, TStaffReservation, TUserSap, TStaffDoctor>()
.Where((a, b, c, d, e) => a.LineUpId == b.Id && b.StaffReservationId == c.Id && c.Uid == d.Uid && c.SapNumber == d.SapNumber && c.DoctorId == e.Id)
.WithTempQuery((a, b, c, d, e) => new
{
lgroup = a,
lup = b,
sr = c,
user = d,
doc = e
})
)
TUserLineUpGroup, TUserLineUp 和TUserLineUpGroupHistory, TUserLineUpHistory表明虽然不同但字段是一样的，这样的情况需要怎么解决？

[2881099]

提供控制台，可运行的测试项目，遇到什么问题也在代码里注释说明。