子查询Select 多个字段组合时,语句错误 返回

SqlSugar 老数据
6 1925
该叫什么 lyl 发布于2021/9/9
悬赏:0 飞吻

C#代码:

 FromDisplayName = SqlFunc.Subqueryable<User>()

                                        .Where(uTemp => uTemp.UserID == d.From_UserID && uTemp.Published == "1")

                                        .Select(uTemp => uTemp.LastName + " " + uTemp.FirstName),

Sql生成代码:

(

SELECT TOP 1 [uTemp].[LastName] + ' ' + [uTemp].[FirstName]

FROM [User]

WHERE [UserID] = [d].[From_UserID]

AND [Published] = '1'

) AS [FromDisplayName]


查询后反馈 没有找到uTemp  是否应该把select的 uTemp去掉?

热忱回答6

  •     var list4 = db.Queryable<Order>().Select(it => new
        {
                    customName = SqlFunc.Subqueryable<Custom>().Where(s=>s.Name=="").Select(s => s.Name+""+s.Name)
    
         }).ToList();

    最新版本未能重现

    SELECT  (SELECT TOP 1 (( [Name] + @Name0 ) +  [Name] ) FROM [Custom] WHERE ( [Name] = @Name1 )) AS [customName]  FROM [Order] it


    0 回复
  • 如果有还有问题,发完整用例

    0 回复
  • lyl lyl VIP0
    2021/9/10

    @fate stay night


    单表确实没问题,连表会出错。


    Db.Queryable<User,UserRole>((u,ur)=>new object[] { 

                    JoinType.Left,u.ID==ur.UserID

                  

                }).Select((u, ur) => new

                {

                    customName = SqlFunc.Subqueryable<User>().Where(s => s.UserName == u.UserName).Select(s => s.UserName + "" + s.UserName)


                }).ToPageList(Page.P, Page.PageSize);


    0 回复
  • lyl lyl VIP0
    2021/9/13

    @fate stay night:可否尽快处理下,我们这边着急用,谢谢

    0 回复
  • lyl lyl VIP0
    2021/9/13
      static void Main(string[] args)
      {
                 var db = new SqlSugarScope(new SqlSugar.ConnectionConfig()
                            {
                                ConnectionString = "server=.;uid=sa;pwd=sa;database=test",
                                DbType = DbType.SqlServer,
                                IsAutoCloseConnection = true
                            });
                
                            //建表 
                            if (!db.DbMaintenance.IsAnyTable("User_Test001", false))
                            {
                                db.CodeFirst.InitTables<User_Test001>();
                            }
                            if (!db.DbMaintenance.IsAnyTable("UserRole_Test001", false))
                            {
                                db.CodeFirst.InitTables<UserRole_Test001>();
                            }
                
                            //用例代码 
                            var result = db.Queryable<User_Test001, UserRole_Test001>((u, ur) => new object[] {
                
                                JoinType.Left,u.ID==ur.UserID
                
                
                
                            }).Select((u, ur) => new
                
                            {
                
                                customName = SqlFunc.Subqueryable<User_Test001>().Where(s => s.UserName == u.UserName).Select(s => s.UserName + "" + s.UserName)
                
                
                
                            }).ToPageList(1, 10);
                
                            Console.WriteLine(result);
                            Console.WriteLine("用例跑完");
                            Console.ReadKey();
     }
     public class User_Test001
            { 
            
                public int ID { get; set; }
                public string UserName { get; set; }
            }
    
            public class UserRole_Test001
            {
    
                public int ID { get; set; }
                public int UserID { get; set; }
            }


    0 回复
  • @lyl:先这样用吧 ,后续修复

             //用例代码 
                var result = db.Queryable<User_Test001, UserRole_Test001>((u, ur) => new object[] {
    
                                JoinType.Left,u.ID==ur.UserID
    
    
    
                            }).Select((u, ur) => new
    
                            {
    
                                customName = SqlFunc.Subqueryable<User_Test001>().Where(s => s.UserName == u.UserName).Select(s =>SqlFunc.MergeString( s.UserName,s.UserName) )
    
    
    
                            }).ToPageList(1, 10);


    0 回复