【SQL】:SELECT [f].[Year] AS [Year] , [f].[January] AS [January] , [f].[JanuaryRemark] AS [JanuaryRemark] , [f].[February] AS [February] , [f].[FebruaryRemark] AS [FebruaryRemark] , [f].[March] AS [March] , [f].[MarchRemark] AS [MarchRemark] , [f].[April] AS [April] , [f].[AprilRemark] AS [AprilRemark] , [f].[May] AS [May] , [f].[MayRemark] AS [MayRemark] , [f].[June] AS [June] , [f].[JuneRemark] AS [JuneRemark] , [f].[July] AS [July] , [f].[JulyRemark] AS [JulyRemark] , [f].[August] AS [August] , [f].[AugustRemark] AS [AugustRemark] , [f].[September] AS [September] , [f].[SeptemberRemark] AS [SeptemberRemark] , [f].[October] AS [October] , [f].[OctoberRemark] AS [OctoberRemark] , [f].[November] AS [November] , [f].[NovemberRemark] AS [NovemberRemark] , [f].[December] AS [December] , [f].[DecemberRemark] AS [DecemberRemark] , [s].[Id] AS [SubGroupId] , [s].[Name] AS [SubGroupName] , [m].[Name] AS [GroupName] , [f].[Status] AS [Status] , [f].[Id] AS [Id] , [s].[GroupId] AS [GroupId] FROM [MKTForecast] [f] Left JOIN [SubGroup] [s] ON ( [f].[SubgroupId] = [s].[Id] ) Left JOIN [MarketGroup] [m] ON ( [s].[GroupId] = [m].[Id] ) WHERE (((((( @Const0 IS NULL ) OR ( [f].[Year] = @Year2 )) AND ( [s].[Name] IS NOT NULL )) AND ( [m].[Name] IS NOT NULL )) AND ( [f].[YearMonth] = @YearMonth5 )) & ( [f].[BrandCode] = @BrandCode6 ))
最后一个条件,解析成了 & 而不是and。
ISugarQueryable<T,T1,T2>,解析join 语句时,where条件的 Expression<Func<T,T1,T2,bool>> 报错 : Incorrect syntax near '&'. 此问题存在于环境: .net core 3.1 SqlSugarCore 5.1.4.69; 5.0.0.15 版本则没有问题。
Expression<Func<SystemUser, Role, RoleModule, Module, Employee, bool>> expression = null; expression = expression.And((u, r, rm, m, e) => u.IsDeleted == false && r.IsDeleted == false); if (moduleName != null) { expression = expression.And((u, r, rm, m, e) => m.Name == moduleName); } if (editable != null) { expression = expression.And((u, r, rm, m, e) => rm.Editable == editable); }var data = await base.QueryMuch<SystemUser, Role, RoleModule, Module, Employee, UserModel>( (u, r, rm, m, e) => new object[] { JoinType.Left, u.RoleId == r.Id, JoinType.Left, r.Id == rm.RoleId, JoinType.Left, m.Id == rm.ModuleId, JoinType.Left, e.Email == u.Email }, (u, r, rm, m, e) => new UserModel { Id = u.Id, Email = u.Email, IsInBusinessGroup = u.IsInBusinessGroup, Name = e.DisplayName, Role = r.RoleName, RoleId = r.Id }, expression ); QueryMuch 具体实现如下: public async Task<List<TResult>> QueryMuch<T, T2, T3, T4, T5, TResult>( Expression<Func<T, T2, T3, T4, T5, object[]>> joinExpression, Expression<Func<T, T2, T3, T4, T5, TResult>> selectExpression, Expression<Func<T, T2, T3, T4, T5, bool>> whereLambda = null) where T : class, new() { if (whereLambda == null) { return await SqlSugarClient.Queryable(joinExpression).Select(selectExpression).ToListAsync(); } return await SqlSugarClient.Queryable(joinExpression).Where(whereLambda).Select(selectExpression).ToListAsync(); }
2023/4/25 14:48:51| 【SQL Params】: @Const0 : 2023 @Year2 : 2023 @YearMonth5 : 202301 @BrandCode6 : B003
【SQL】:SELECT [f].[Year] AS [Year] , [f].[January] AS [January] , [f].[JanuaryRemark] AS [JanuaryRemark] , [f].[February] AS [February] , [f].[FebruaryRemark] AS [FebruaryRemark] , [f].[March] AS [March] , [f].[MarchRemark] AS [MarchRemark] , [f].[April] AS [April] , [f].[AprilRemark] AS [AprilRemark] , [f].[May] AS [May] , [f].[MayRemark] AS [MayRemark] , [f].[June] AS [June] , [f].[JuneRemark] AS [JuneRemark] , [f].[July] AS [July] , [f].[JulyRemark] AS [JulyRemark] , [f].[August] AS [August] , [f].[AugustRemark] AS [AugustRemark] , [f].[September] AS [September] , [f].[SeptemberRemark] AS [SeptemberRemark] , [f].[October] AS [October] , [f].[OctoberRemark] AS [OctoberRemark] , [f].[November] AS [November] , [f].[NovemberRemark] AS [NovemberRemark] , [f].[December] AS [December] , [f].[DecemberRemark] AS [DecemberRemark] , [s].[Id] AS [SubGroupId] , [s].[Name] AS [SubGroupName] , [m].[Name] AS [GroupName] , [f].[Status] AS [Status] , [f].[Id] AS [Id] , [s].[GroupId] AS [GroupId] FROM [MKTForecast] [f] Left JOIN [SubGroup] [s] ON ( [f].[SubgroupId] = [s].[Id] ) Left JOIN [MarketGroup] [m] ON ( [s].[GroupId] = [m].[Id] ) WHERE (((((( @Const0 IS NULL ) OR ( [f].[Year] = @Year2 )) AND ( [s].[Name] IS NOT NULL )) AND ( [m].[Name] IS NOT NULL )) AND ( [f].[YearMonth] = @YearMonth5 )) & ( [f].[BrandCode] = @BrandCode6 )) 最后一个条件,解析成了 & 而不是and。