If you are using Oracle DB and you run a .NET Entity Framework Core LINQ query, but you receive the error below:
ORA-01652: unable to extend temp segment in tablespace
One of the common solutions is to increase the temp segment in Oracle tablespace disk space. But what happens if it is not working, or if it happens after some time?
One of the reasons the error raised by Oracle DB is the SQL query generated by EF Core is too complex and requires a lot of temp tablespaces to temporarily hold the query data. Below is one example generated by EF Core for the where criteria
WHERE ((((:No_0 IS NULL ) OR ((("m"."NO" IS NOT NULL) AND ((("m"."NO" LIKE :no_0 || N'%') AND (SUBSTR("m"."NO", 1, LENGTH(:no_0)) = :no_0)))))))
The SQL query seems complex with many parentheses and causes the oracle to be out of temp segment space. For the same query, if try in Oracle SQL Developer will get the same error but if remove the null and length check, it gains better performance and fix out of segment space issue.
WHERE (("m"."NO" IS NOT NULL) AND ("m"."NO" LIKE N'1234%'))
It seems like cannot get the similar SQL script on above to generate by EF Core, but found if using Dynamic LINQ which with System.Linq.Dynamic.Core, it can generate the query on above. So, the issue was solved.
Malaysia C# Developer and Writing✍ Blog 