oracle/dotnet-db-samples

bug: dotnet ef dbcontext scaffold can not complete

1257960069 opened this issue · 10 comments

1257960069 commented

run this cmd will stop work at the process sql step

.net version: net8
nuget: Oracle.EntityFrameworkCore
version:8.23.70

  1. cmd:

dotnet ef dbcontext scaffold "User Id=xxx;Password=xxx;Data Source=xxx:1521/xxx.xxx" Oracle.EntityFrameworkCore --data-annotations --no-onconfiguring --no-pluralize --force --json --verbose --prefix-output --no-build --context-dir MyApps --output-dir MyApps/Models

  1. log:

verbose: 2025-01-10 14:38:56.482024 ThreadID:1 (SQL) OracleDatabaseModelFactory.GetColumnsCombined() : select u.*, v.trigger_name, v.table_name, v.column_name, v.table_owner from (SELECT sys_context('userenv', 'current_schema') as schema, c.table_name, c.column_name, c.column_id, c.data_type, c.char_length, c.data_length, c.data_precision, c.data_scale, c.nullable, c.identity_column, c.collation, c.data_default, c.virtual_column, c.hidden_column, co.comments FROM user_tab_cols c INNER JOIN user_col_comments co ON co.table_name=c.table_name AND co.column_name=c.column_name INNER JOIN (select distinct object_name as table_name from user_objects where object_type in ('TABLE', 'VIEW', 'MATERIALIZED VIEW')) t ON t.table_name=c.table_name WHERE t.table_name <> '__EFMigrationsHistory' )u left join USER_TRIGGER_COLS v on u.table_name = v.table_name and u.column_name = v.column_name and u.schema = v.table_owner ORDER BY u.column_id
reason:
the sql is not good query, because user_col_comments table's comments column exist a lot of null value. will cause unuseful joinning.

  1. fix advice:

replate sql
INNER JOIN user_col_comments co ON co.table_name=c.table_name ANDco.column_name=c.column_name
with new sql

 LEFT JOIN user_col_comments co ON comments IS NOT NULL AND co.table_name = c.table_name
                                               AND co.column_name = c.column_name
alexkeh commented

@1257960069 I ran your scaffold command successfully without an error. Do you see this problem occur with every schema you scaffold or a specific one or few? If it's a limited number, can you provide the schema CREATE scripts that would allow me to recreate the error?

1257960069 commented

@1257960069 I ran your scaffold command successfully without an error. Do you see this problem occur with every schema you scaffold or a specific one or few? If it's a limited number, can you provide the schema CREATE scripts that would allow me to recreate the error?

The root cause of this issue is that our company's Oracle database has a user_col_comments table with a large number of columns, and 90% of the comments column values are null. This results in excessive and unnecessary joins, which in turn causes the query to hang. This SQL needs optimization of the joins to resolve this issue.

1257960069 commented

I think the base sql is

SELECT 
    sys_context('userenv', 'current_schema') AS schema, 
    c.table_name, 
    c.column_name, 
    c.column_id, 
    c.data_type, 
    c.char_length, 
    c.data_length, 
    c.data_precision, 
    c.data_scale, 
    c.nullable, 
    c.identity_column, 
    c.collation, 
    c.data_default, 
    c.virtual_column, 
    c.hidden_column, 
    co.comments,
    v.trigger_name, 
    v.table_name AS v_table_name, 
    v.column_name AS v_column_name, 
    v.table_owner 
FROM user_tab_cols c 
LEFT JOIN (
    SELECT * 
    FROM user_col_comments 
    WHERE comments IS NOT NULL
) co 
    ON co.table_name = c.table_name 
    AND co.column_name = c.column_name 
LEFT JOIN USER_TRIGGER_COLS v  
    ON c.table_name = v.table_name 
    AND c.column_name = v.column_name 
    AND sys_context('userenv', 'current_schema') = v.table_owner 
WHERE c.table_name IN (
    SELECT DISTINCT object_name 
    FROM user_objects 
    WHERE object_type IN ('TABLE', 'VIEW', 'MATERIALIZED VIEW')
)
AND c.table_name <> '__EFMigrationsHistory'
ORDER BY c.column_id;

Additionally, I think filtering before joining and filtering constants within the join are the same.
1.filtering before joining：

LEFT JOIN (
    SELECT * 
    FROM user_col_comments 
    WHERE comments IS NOT NULL
) co 
    ON co.table_name = c.table_name 
    AND co.column_name = c.column_name

2.filtering constants within the join are the same.

LEFT JOIN user_col_comments co 
    ON co.comments IS NOT NULL 
    AND co.table_name = c.table_name 
    AND co.column_name = c.column_name
alexkeh commented

I filed bug 37474702 to track this issue and have the dev team investigate further.

1257960069 commented