FreeSql.Generator 工具生成model失败

[frisktale]

问题描述及重现步骤：

AdventureWorks 示例数据库中的 AdventureWorks2019.bak 备份还原而来的数据库，无法用工具生成model，会报

Unhandled exception. System.Exception: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

错误

数据库的具体版本

sqlserver 2019 express

安装的包

FreeSql.Generator 工具

.net framework/. net core？ 及具体版本

.net 6 rc 1 sdk

生成的sql

use [AdventureWorks2019];
select * from (
select
 a.object_id
,b.name 'owner'
,a.name 'name'
,(select value from sys.extended_properties where major_id = a.object_id AND minor_id = 0 AND name = 'MS_Description') 'comment'
,'TABLE' type
from sys.tables a
inner join sys.schemas b on b.schema_id = a.schema_id
where not(b.name = 'dbo' and a.name = 'sysdiagrams')
union all
select
 a.object_id
,b.name 'owner'
,a.name 'name'
,(select value from sys.extended_properties where major_id = a.object_id AND minor_id = 0 AND name = 'MS_Description') 'comment'
,'VIEW' type
from sys.views a
inner join sys.schemas b on b.schema_id = a.schema_id
union all
select
 a.object_id
,b.name 'owner'
,a.name 'name'
,(select value from sys.extended_properties where major_id = a.object_id AND minor_id = 0 AND name = 'MS_Description') 'comment'
,'StoreProcedure' type
from sys.procedures a
inner join sys.schemas b on b.schema_id = a.schema_id
where a.type = 'P' and charindex('diagram', a.name) = 0
) ft_dbf
order by type desc, [owner], [name];
use [AdventureWorks2019];

use [AdventureWorks2019];

select
isnull(e.name,'') + '.' + isnull(d.name,'')
,a.object_id
,a.name 'column'
,b.name 'type'
,case
 when b.name in ('text', 'ntext', 'image') then -1
 when b.name in ('nchar', 'nvarchar') then a.max_length / 2
 else a.max_length end 'length'
,b.name + case
 when b.name in ('char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary') then '(' +
  case when a.max_length = -1 then 'MAX'
  when b.name in ('nchar', 'nvarchar') then cast(a.max_length / 2 as varchar)
  else cast(a.max_length as varchar) end + ')'
 when b.name in ('numeric', 'decimal') then '(' + cast(a.precision as varchar) + ',' + cast(a.scale as varchar) + ')'
 else '' end as 'sqltype'
,( select value from sys.extended_properties where major_id = a.object_id AND minor_id = a.column_id AND name = 'MS_Description') 'comment'

,a.is_nullable 'isnullable'
,a.is_identity 'isidentity'
,f.text as 'defaultvalue'
from sys.columns a
inner join sys.types b on b.user_type_id = a.user_type_id
left join sys.tables d on d.object_id = a.object_id
left join sys.schemas e on e.schema_id = d.schema_id
left join syscomments f on f.id = a.default_object_id

where (a.object_id in (423672557,439672614,455672671,503672842,535672956,519672899,407672500,631673298,551673013,567673070,583673127,711673583,695673526,471672728,487672785,599673184,615673241,679673469,663673412,647673355,1125579048,901578250,933578364,1701581100,1893581784,2101582525,2099048,98099390,702625546,1029578706,1077578877,1285579618,1333579789,1381579960,1429580131,1493580359,1845581613,226099846,274100017,418100530,450100644,1118627028,1157579162,1557580587,1733581214,66099276,130099504,482100758,722101613,770101784,834102012,882102183,914102297,1010102639,1074102867,1122103038,1154103152,1186103266,1218103380,1266103551,1330103779,670625432,1230627427,1310627712,1390627997,1502628396,1582628681,1378103950,1506104406,1602104748,734625660,1422628111,1461580245,1525580473,1589580701,1621580815,1653580929,386100416,1810105489,1922105888,30623152,62623266,254623950,318624178,350624292,430624577,606625204,846626059,926626344,1070626857,1182627256))
union all

select
isnull(e.name,'') + '.' + isnull(d.name,'')
,a.object_id
,a.name 'column'
,b.name 'type'
,case
 when b.name in ('text', 'ntext', 'image') then -1
 when b.name in ('nchar', 'nvarchar') then a.max_length / 2
 else a.max_length end 'length'
,b.name + case
 when b.name in ('char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary') then '(' +
  case when a.max_length = -1 then 'MAX'
  when b.name in ('nchar', 'nvarchar') then cast(a.max_length / 2 as varchar)
  else cast(a.max_length as varchar) end + ')'
 when b.name in ('numeric', 'decimal') then '(' + cast(a.precision as varchar) + ',' + cast(a.scale as varchar) + ')'
 else '' end as 'sqltype'
,( select value from sys.extended_properties where major_id = a.object_id AND minor_id = a.parameter_id AND name = 'MS_Description') 'comment'

,cast(0 as bit) 'isnullable'
,a.is_output 'isidentity'
,'' as 'defaultvalue'
from sys.parameters a
inner join sys.types b on b.user_type_id = a.user_type_id
left join sys.tables d on d.object_id = a.object_id
left join sys.schemas e on e.schema_id = d.schema_id
where (a.object_id in (887674210,903674267,919674324,935674381,997578592,981578535,999674609,951674438,967674495,983674552))
;use [AdventureWorks2019];

全部错误信息

Unhandled exception. System.Exception: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at Microsoft.Data.SqlClient.SqlDataReader.Read()
   at FreeSql.Internal.CommonProvider.AdoProvider.ExecuteReaderMultiple(Int32 multipleResult, DbConnection connection, DbTransaction transaction, Action`2 fetchHandler, Action`2 schemaHandler, CommandType cmdType, String cmdText, Int32 cmdTimeout, DbParameter[] cmdParms) in C:\Users\28810\Desktop\github\FreeSql\FreeSql\Internal\CommonProvider\AdoProvider\AdoProvider.cs:line 646
ClientConnectionId:ae3ad09c-95d7-4666-bc50-633d9cff5250
Error Number:512,State:1,Class:16
   --- End of inner exception stack trace ---
   at FreeSql.Internal.CommonProvider.AdoProvider.LoggerException(IObjectPool`1 pool, PrepareCommandResult pc, Exception ex, DateTime dt, StringBuilder logtxt, Boolean isThrowException) in C:\Users\28810\Desktop\github\FreeSql\FreeSql\Internal\CommonProvider\AdoProvider\AdoProvider.cs:line 92
   at FreeSql.Internal.CommonProvider.AdoProvider.ExecuteReaderMultiple(Int32 multipleResult, DbConnection connection, DbTransaction transaction, Action`2 fetchHandler, Action`2 schemaHandler, CommandType cmdType, String cmdText, Int32 cmdTimeout, DbParameter[] cmdParms) in C:\Users\28810\Desktop\github\FreeSql\FreeSql\Internal\CommonProvider\AdoProvider\AdoProvider.cs:line 688
   at FreeSql.Internal.CommonProvider.AdoProvider.ExecuteReader(DbConnection connection, DbTransaction transaction, Action`1 fetchHandler, CommandType cmdType, String cmdText, Int32 cmdTimeout, DbParameter[] cmdParms) in C:\Users\28810\Desktop\github\FreeSql\FreeSql\Internal\CommonProvider\AdoProvider\AdoProvider.cs:line 559
   at FreeSql.Internal.CommonProvider.AdoProvider.ExecuteArray(DbConnection connection, DbTransaction transaction, CommandType cmdType, String cmdText, Int32 cmdTimeout, DbParameter[] cmdParms) in C:\Users\28810\Desktop\github\FreeSql\FreeSql\Internal\CommonProvider\AdoProvider\AdoProvider.cs:line 700
   at FreeSql.Internal.CommonProvider.AdoProvider.ExecuteArray(CommandType cmdType, String cmdText, DbParameter[] cmdParms) in C:\Users\28810\Desktop\github\FreeSql\FreeSql\Internal\CommonProvider\AdoProvider\AdoProvider.cs:line 695
   at FreeSql.SqlServer.SqlServerDbFirst.GetTables(String[] database, String tablename, Boolean ignoreCase) in C:\Users\28810\Desktop\github\FreeSql\Providers\FreeSql.Provider.SqlServer\SqlServerDbFirst.cs:line 307
   at FreeSql.SqlServer.SqlServerDbFirst.GetTablesByDatabase(String[] database) in C:\Users\28810\Desktop\github\FreeSql\Providers\FreeSql.Provider.SqlServer\SqlServerDbFirst.cs:line 137
   at FreeSql.Generator.ConsoleApp..ctor(String[] args, ManualResetEvent wait) in C:\Users\28810\Desktop\github\FreeSql\Extensions\FreeSql.Generator\ConsoleApp.cs:line 241
   at FreeSql.Generator.Program.<>c__DisplayClass0_0.<Main>b__0() in C:\Users\28810\Desktop\github\FreeSql\Extensions\FreeSql.Generator\Program.cs:line 16
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()

[luoyunchong]

select * from sys.extended_properties where major_id = 2099048 

这个子查询应该要加一个class=1的判断

,( select value from sys.extended_properties where major_id = a.object_id AND minor_id = a.column_id AND name = 'MS_Description') 'comment'

[2881099]

@luoyunchong 记得PR，表和字段的 class 值可能不一样