search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.79k stars 3.19k forks source link

操作oracle 进行分页出现表别名错乱。 #19582

Closed wmm1989 closed 2 years ago

wmm1989 commented 4 years ago

efcore版本 2.2.4 数据库 oracle 11g oracle dll版本 Oracle.EntityFrameworkCore 2.19.30 发布工具 IIS 部署服务器windowsever2012 standard 。

经常出现报未找到字段错误，根据日志发现生成的sql语句表别名出现错乱情况，导致找不到字段。

var data = await iQueryable.Skip(index * size).Take(size).OrderByBatch(orderBy).ToListAsync();

生成sql语句： Select K0 "ID", K1 "CLINICLABELID", K2 "CLINICLABELNAME", K3 "CREATE_BY", K4 "CREATE_TIME", K5 "ISDEL", K6 "ISUPLOAD", K7 "ORG_CODE", K8 "PYM", K9 "UPDATE_BY", K10 "UPDATE_TIME", K11 "UPLOAD_TIME", K12 "WBM" from( select "m2".*, rownum r2 from ( SELECT "f"."ID" K0, "f"."CLINICLABELID" K1, "f"."CLINICLABELNAME" K2, "f"."CREATE_BY" K3, "f"."CREATE_TIME" K4, "f"."ISDEL" K5, "f"."ISUPLOAD" K6, "f"."ORG_CODE" K7, "f"."PYM" K8, "f"."UPDATE_BY" K9, "f"."UPDATE_TIME" K10, "f"."UPLOAD_TIME" K11, "f"."WBM" K12 FROM "HOS_CLINIC_LABEL" "m1" --此处m1正常应该是f WHERE ("f"."ISDEL" = 0) ORDER BY "f"."ID" NULLS FIRST ) "m2" ) "m1" where r2 > :p_0 and r2 <= (:p_0 + :p_1)

生成的sql语句 正常分页内表表别是f，结果为分页后的m1。 不定期出现这种情况，iis上api重启后恢复正常，后续不定期再出现。 原先部署在windowserver2008上正常。

ajcvickers commented 4 years ago

Machine translation:

efcore version 2.2.4 Database oracle 11g oracle dll version Oracle.EntityFrameworkCore 2.19.30 Publishing Tool IIS Deployment server windowsever2012 standard.

Frequently reported field not found errors, according to the log found that the generated sql statement table aliases are disordered, resulting in no field found.

var data = await iQueryable.Skip (index * size) .Take (size) .OrderByBatch (orderBy) .ToListAsync ();

Generate sql statement: Select K0 "ID", K1 "CLINICLABELID", K2 "CLINICLABELNAME", K3 "CREATE_BY", K4 "CREATE_TIME", K5 "ISDEL", K6 "ISUPLOAD", K7 "ORG_CODE", K8 "PYM", K9 "UPDATE_BY" , K10 "UPDATE_TIME", K11 "UPLOAD_TIME", K12 "WBM" from (select "m2". *, Rownum r2 from (SELECT "f". "ID" K0, "f". "CLINICLABELID" K1, "f" . "CLINICLABELNAME" K2, "f". "CREATE_BY" K3, "f". "CREATE_TIME" K4, "f". "ISDEL" K5, "f". "ISUPLOAD" K6, "f". "ORG_CODE" K7 , "f". "PYM" K8, "f". "UPDATE_BY" K9, "f". "UPDATE_TIME" K10, "f". "UPLOAD_TIME" K11, "f". "WBM" K12 FROM "HOS_CLINIC_LABEL" " m1 "--here m1 should normally be f WHERE (" f "." ISDEL "= 0) ORDER BY" f "." ID "NULLS FIRST)" m2 ")" m1 "where r2>: p_0 and r2 < = (: p_0 +: p_1)

Generated sql statement The table type in normal paging is f, and the result is m1 after paging. This happens from time to time. After restarting the API on IIS, it returns to normal. The original deployment was normal on windowserver2008.

ajcvickers commented 4 years ago

@wmm1989 Please attach a small, runnable project or post a complete code listing that reproduces the behavior you are seeing.

ajcvickers commented 4 years ago

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.

wmm1989 commented 4 years ago

@wmm1989 Please attach a small, runnable project or post a complete code listing that reproduces the behavior you are seeing.

Sorry, this is demo git repository ： https://github.com/wmm1989/netcoreDemo.git Thank you

ajcvickers commented 4 years ago

@wmm1989 Thanks for posting the repro code. First just let me point out that .NET Core 2.2 and EF Core 2.2 are now out-of-support. Please update to the 3.1 versions.

Second, I get the following error when attempting to run your code:

FileNotFoundException: Could not load file or assembly 'C:\Repros\netcoreDemo-master\netcoreDemo-master\JQ.Base.Api\bin\Debug\netcoreapp2.2\JQ.Common.Services.dll'. The system cannot find the file specified.
System.Runtime.Loader.AssemblyLoadContext.LoadFromPath(IntPtr ptrNativeAssemblyLoadContext, string ilPath, string niPath, ObjectHandleOnStack retAssembly)

FileNotFoundException: Could not load file or assembly 'C:\Repros\netcoreDemo-master\netcoreDemo-master\JQ.Base.Api\bin\Debug\netcoreapp2.2\JQ.Common.Services.dll'. The system cannot find the file specified.
System.Runtime.Loader.AssemblyLoadContext.LoadFromPath(IntPtr ptrNativeAssemblyLoadContext, string ilPath, string niPath, ObjectHandleOnStack retAssembly)
System.Runtime.Loader.AssemblyLoadContext.LoadFromAssemblyPath(string assemblyPath)
System.Reflection.Assembly.LoadFile(string path)
JQ.Base.Api.Startup.ConfigureServices(IServiceCollection services) in Startup.cs
+ var assemblysServicesCommon = Assembly.LoadFile(servicesCommonDllFile);
Microsoft.AspNetCore.Hosting.ConventionBasedStartup.ConfigureServices(IServiceCollection services)
Microsoft.AspNetCore.Hosting.Internal.WebHost.EnsureApplicationServices()
Microsoft.AspNetCore.Hosting.Internal.WebHost.Initialize()
Microsoft.AspNetCore.Hosting.Internal.WebHost.BuildApplication()

Is this the issue that you're reporting? If not, can you both explain what is is that you are reporting as a bug and also update the repro code to generated the error you are seeing?

ajcvickers commented 4 years ago

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.

wmm1989 commented 4 years ago

@ajcvickers Sorry, the code was uploaded without testing and it has been repaired. I also want to upgrade 3.1, but Oracle dll is not supported. image

ajcvickers commented 4 years ago

@wmm1989 Oracle owns the EF Core provider for Oracle. You'll need to contact them about a 3.1 release--I'm pretty sure it doesn't exist yet, but they may be working on it.

Alternatively, there is at least one third-party provider available, although I believe it costs money.

wmm1989 commented 4 years ago

@ajcvickers Trouble can confirm, is the problem with efcore2.2 or the dll problem provided by oracle. It's so confused now, it's not clear where the problem lies.

ajcvickers commented 4 years ago

@wmm1989 You need to contact Oracle and report the issue to them.

wmm1989 commented 4 years ago

@ajcvickers OK, let's try the 3.1 version and try the paid dll of devart. Thank you anyway.

34481694 commented 4 years ago

请问下，这个问题你现在解决了吗，我们现在也遇到了一样的问题，生成的SQL，别名是错的或者根本没有这个别名的表名

wmm1989 commented 4 years ago

没解决。作者建议说使用3.X版本试试，但是3.X版本的oracle dll 还没有。 所以用了devart 再尝试。 但是devart的速度感人。。。

------------------ 原始邮件 ------------------ 发件人: "34481694"<notifications@github.com>; 发送时间: 2020年4月22日(星期三) 上午9:55 收件人: "dotnet/efcore"<efcore@noreply.github.com>; 抄送: "王淼苗"<albertwmm@vip.qq.com>;"Mention"<mention@noreply.github.com>; 主题: Re: [dotnet/efcore] 操作oracle 进行分页出现表别名错乱。 (#19582)

请问下，这个问题你现在解决了吗，我们现在也遇到了一样的问题，生成的SQL，别名是错的或者根本没有这个别名的表名

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

34481694 commented 4 years ago

我们也是在window server 2012 出现的问题 你在其他非2012window or linux 上出现过这个问题吗 @wmm1989

wmm1989 commented 4 years ago

正常安装的2008 也出现相同的问题。 linux没试过。 但是阿里云上的2008是没问题的，你们可以试下。

------------------ 原始邮件 ------------------ 发件人: "34481694"<notifications@github.com>; 发送时间: 2020年4月22日(星期三) 上午10:16 收件人: "dotnet/efcore"<efcore@noreply.github.com>; 抄送: "王淼苗"<albertwmm@vip.qq.com>;"Mention"<mention@noreply.github.com>; 主题: Re: [dotnet/efcore] 操作oracle 进行分页出现表别名错乱。 (#19582)

我们也是在window server 2012 出现的问题 你在其他非2012window or linux 上出现过这个问题吗 @wmm1989

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

wmm1989 commented 4 years ago

请问你们解决了吗

------------------ 原始邮件 ------------------ 发件人: "34481694"<notifications@github.com>; 发送时间: 2020年4月22日(星期三) 上午10:16 收件人: "dotnet/efcore"<efcore@noreply.github.com>; 抄送: "王淼苗"<albertwmm@vip.qq.com>;"Mention"<mention@noreply.github.com>; 主题: Re: [dotnet/efcore] 操作oracle 进行分页出现表别名错乱。 (#19582)

我们也是在window server 2012 出现的问题 你在其他非2012window or linux 上出现过这个问题吗 @wmm1989

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

34481694 commented 4 years ago

没有解决，在nuget的oracle.efcore.dll的2.19.60版本中有这么一句话： Within the SELECT statement, there are two nested SELECTs. The generated SQL will encounter a ORA-00904 : "invalid identifier" error with Oracle Database 11.2 since it has a restriction where it does not recognize outer select table alias "i" in the inner nested select query.

我们oracle刚好也是11.2的，准备在19c中试试 或者 等oracle.dll 3+

pijiuok commented 4 years ago

好像说的不是这个事吧