Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

EF Core Scaffolding Issue with Indexes

055f3c76-df86-4f5d-bc24-15c2e82b36cbJul 31 2019 — edited Aug 1 2019

We are trying to scaffold a number of our schemas with a consistent issue almost every time:  System.ArgumentNullException: Value cannot be null.

Parameter name: column

   at Microsoft.EntityFrameworkCore.Utilities.Check.NotNull[T](T value, String parameterName)

   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.GetPropertyName(DatabaseColumn column)

   at System.Linq.Enumerable.WhereSelectListIterator`2.MoveNext()

   at System.Linq.Buffer`1..ctor(IEnumerable`1 source)

   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)

   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitIndex(EntityTypeBuilder builder, DatabaseIndex index)

   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitIndexes(EntityTypeBuilder builder, ICollection`1 indexes)

   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitTable(ModelBuilder modelBuilder, DatabaseTable table)

   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitTables(ModelBuilder modelBuilder, ICollection`1 tables)

   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.VisitDatabaseModel(ModelBuilder modelBuilder, DatabaseModel databaseModel)

   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.Create(DatabaseModel databaseModel, Boolean useDatabaseNames)

   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, IEnumerable`1 tables, IEnumerable`1 schemas, String namespace, String language, String contextDir, String contextName, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions)

   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)

   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)

   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_1.<.ctor>b__0()

   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()

   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)

I was able to try around a dozen schemas running the following in CLI and only one schema finished without the above exception.  Seeing it was having trouble visiting indexes, I looked in ALL_INDEXES and confirmed that the one schema that finished had only indexes of type NORMAL.

Scaffold-DbContext "User ID=/;Data Source=<instance>" Oracle.EntityFrameworkCore -schema <schema> –force -verbose

I did a little research to see if this is an EF Core issue and found this:  https://github.com/aspnet/EntityFrameworkCore/issues/10416   so it seems like the index type is the responsibility of the provider and that function-based indexes need to be addressed.

Is anyone else having trouble scaffolding with non-NORMAL indexes?  Are there any work arounds?  I tried even specifying just one table that only had normal indexes and that still went through the same logic that produced this error.

Comments

Frank Kulash
Answer

Hi,

Thanks for posting the sampled data.

Don't forget to post the exact results you want from that data.

Are these the results you want?

R_NUM FRI_S FRI_H          MON_S MON_H          THU_S THU_H

----- ----- -------------- ----- -------------- ----- --------------

    1 08:54 SEYMA DURLANIK 13:00 SEYHAN UNVER   08:30 ZUHRE YEL

    2 10:00 SENGUL AKBAS   13:30 SELMA CALISKAN 08:48 AYSEL POLAT

    3 13:30 ORHAN SAVAS    17:45 ESMA COMERT

    4 14:00 FATMA ETA

(I only included 3 days of the week, just to save space.)

Here's one way to get them:

WITH    got_r_num  AS

(

    SELECT  TO_CHAR (t.r_tarihi, 'DY')      AS gun

    ,       TO_CHAR (t.baslama, 'HH24:MI')  AS saat

    ,       h.adi || ' ' || h.soyadi        AS hasta

    ,       ROW_NUMBER () OVER ( PARTITION BY  TO_CHAR (t.r_tarihi, 'DY')

                                 ORDER BY   t.baslama

                               )            AS r_num

    FROM             randevu_entegre_deneme  t

    LEFT OUTER JOIN  hasta_deneme            h  ON   h.id  = t.hasta_id

    WHERE   r_tarihi   BETWEEN  TO_DATE ('20.5.2011', 'dd.mm.yyyy')

                       AND      TO_DATE ('20.5.2011', 'dd.mm.yyyy') + 9

-- AND     TO_CHAR (R_TARIHI, 'DY')  NOT IN ('CMT','PAZ')

)

SELECT    *

FROM      got_r_num

PIVOT     ( MIN (SAAT)    AS s

          , MIN (HASTA)   AS h

          FOR  gun IN ( 'FRI'  AS fri

--                    , 'SAT'  AS sat

                      , 'MON'  AS mon

                      , 'THU'  AS thu

                      )

          )

ORDER BY  r_num

;

I used the English names for the days, because this site thinks that the abbreviation for "Cuma" is a naughty word, and won't allow me to post it.

Marked as Answer by Onur Gul · Sep 27 2020
Onur Gul

thank you for your attention. I get ora-00933 in,

WITH    got_r_num  AS

(

    SELECT  TO_CHAR (t.r_tarihi, 'DAY')      AS gun

    ,       TO_CHAR (t.baslama, 'HH24:MI')  AS saat

    ,       h.adi || ' ' || h.soyadi        AS hasta

    ,       ROW_NUMBER () OVER ( PARTITION BY  TO_CHAR (t.r_tarihi, 'DAY')

                                 ORDER BY   t.baslama

                               )            AS r_num

    FROM             randevu_entegre_deneme  t

    LEFT OUTER JOIN  hasta_deneme            h  ON   h.id  = t.hasta_id

    WHERE   r_tarihi   BETWEEN  TO_DATE ('20.5.2011', 'dd.mm.yyyy')

                       AND      TO_DATE ('20.5.2011', 'dd.mm.yyyy') + 9

)

SELECT    *

FROM      got_r_num

PIVOT ((here))    ( MIN (SAAT)    AS s

          , MIN (HASTA)   AS h

          FOR  gun IN ( 'CUMA'  AS fri

                      , 'PAZARTESI'  AS mon

                      , 'PERSEMBE'  AS thu

                      )

          )

ORDER BY  r_num

;

Frank Kulash

Hi,

44e4386e-32a5-44eb-a5f5-d8a088ed0bd9 wrote:

Hi,

I want to exact results form:

CUMA PAZARTESI SALI PERSEMBE
13:30 ORHAN SAVAS 13:00 SEYHAN UNVER 09:45 SEYMA DURLANIK 08:30 ZUHRE YEL
14:00 FATMA ETA 13:30 SELMA CALISKAN 10:00 HASAN GOC 08:48 AYSEL POLAT
14:30 ISMAHAN YALDIZ 17:45 ESMA COMERT 13:00 TURKAN BICAK 09:00 AHMET OZGUNGOR
14:30 ISMAHAN YALDIZ 09:12 TELEFON RANDEVUSU

thanks for your attention.

I don't see how you get those results from the given sample data.  For example, I don't see 'ISMAHAN' anywhere in the data; how do you get 'ISMAHAN' in the results?

Onur Gul

no I haven't got this result.

I prepare in excel this results. this records from real table.

I had prepared before writing statements.

unknown-879931

44e4386e-32a5-44eb-a5f5-d8a088ed0bd9 wrote:

no I haven't got this result.

I prepare in excel this results. this records from real table.

I had prepared before writing statements.

Kardeşim sen ne yapmak istiyosun söyle yardımcı olayım? Hemşerimi görmüşüm bırakmam

Translate: I am just asking him what he exactly wants to do?

Onur Gul

Thank you very much Frank.

I try on my friends computer when it's done. He using oracle 12 c version.

108:30AYSUN GOKO08:30HASAN KOCA08:30BEKTAS KIZAR08:30SULTAN SALMAN08:30ZULUF OZGE

How can I merge this column

108:30 AYSUN GOKO08:30 HASAN KOCA08:30 BEKTAS KIZAR08:30 SULTAN SALMAN08:30 ZULUF OZGE
unknown-879931

İstediğin kod aşağıdaki gibidir. (Here is the code that you want)

SELECT * FROM (

select TRIM(TO_CHAR(R_TARIHI, 'DAY')) as gun, TO_CHAR(T.BASLAMA,'HH24:MI') AS SAAT,(h.adi || ' ' ||h.soyadi) as hasta

from RANDEVU_ENTEGRE_DENEME t left outer join hasta_DENEME h on h.id=t.hasta_id

where R_TARIHI between to_Date('20.5.2011','dd.MM.yyyy') and (to_Date('20.5.2011','dd.MM.yyyy')+9)

) PIVOT (

listagg(hasta) within group(order by hasta) FOR (gun) in ('PAZARTESI', 'SALI', 'CARSAMBA', 'PERSEMBE', 'CUMA')

);

Eğer günler ingilizce ise aşağıdaki gibi olması lazım.

SELECT * FROM (

select TRIM(TO_CHAR(R_TARIHI, 'DAY')) as gun, TO_CHAR(T.BASLAMA,'HH24:MI') AS SAAT,(h.adi || ' ' ||h.soyadi) as hasta

from RANDEVU_ENTEGRE_DENEME t left outer join hasta_DENEME h on h.id=t.hasta_id

where R_TARIHI between to_Date('20.5.2011','dd.MM.yyyy') and (to_Date('20.5.2011','dd.MM.yyyy')+9)

) PIVOT (

listagg(hasta) within group(order by hasta) FOR (gun) in ('MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY')

);

Kolay gelsin

Regards

unknown-879931

44e4386e-32a5-44eb-a5f5-d8a088ed0bd9 wrote:

kardeşim valla ne deyim allah razı olsun

elimde yapmam gereken bir randevu tablosu var. bunları transpoze etmem gerekiyor.

sorguyu bir türlü beceremedim. sağolsun Frank çok yardımcı olmaya çalıştı. fakat ora-00933 hatası alıyorum onun yazdığı sorguyla.

nasıl çözebilirim ?

Bilader, Frank'in yazdığı kod doğru çalışıyor, eksik olan şey sende günler türkçe tanımlı onda ingilizce dolayısıyla hata alıyorsun. Kırmızıyla boyadığım kısımları düzelteceksin, sorun bu.

Translate: I have just said that Frank's code is working correct. The problem is if you use SQL Developer the days are describe in Turkish. Therefore you have to change the red lines.

WITH    got_r_num  AS

(

    SELECT  TRIM(TO_CHAR (t.r_tarihi, 'DAY'))      AS gun

    ,       TO_CHAR (t.baslama, 'HH24:MI')  AS saat

    ,       h.adi || ' ' || h.soyadi        AS hasta

    ,       ROW_NUMBER () OVER ( PARTITION BY  TO_CHAR (t.r_tarihi, 'DY')

                                 ORDER BY   t.baslama

                               )            AS r_num

    FROM             randevu_entegre_deneme  t

    LEFT OUTER JOIN  hasta_deneme            h  ON   h.id  = t.hasta_id

    WHERE   r_tarihi   BETWEEN  TO_DATE ('20.5.2011', 'dd.mm.yyyy')

                       AND      TO_DATE ('20.5.2011', 'dd.mm.yyyy') + 9

-- AND     TO_CHAR (R_TARIHI, 'DY')  NOT IN ('CMT','PAZ')

)

SELECT    *

FROM      got_r_num

PIVOT     ( MIN (SAAT)    AS s

          , MIN (HASTA)   AS h

          FOR  gun IN ( 'PAZARTESI'

--                    , 'SALI'

                      , 'CARSAMBA' 

                      , 'PERSEMBE' 

                      , 'CUMA')

          )

ORDER BY  r_num

;



1 - 8

Post Details

Added on Jul 31 2019
1 comment
671 views