none
Code First Concatenating Schema into Table Name RRS feed

  • Question

  • I'm trying to leverage EF to read from the Windows Workflow Foundation persistence store database. The table I'm trying to read from is called InstancesTable, and it is in the schema System.Activities.DurableInstancing. I believe that having periods in the name of the schema is causing issues. Here is the definition of my type and where I map by DbInstance type to the correct table and schema. 

    modelBuilder.Entity<DbInstance>().ToTable("InstancesTable", "System.Activities.DurableInstancing");

     

    I can query data from this table without issue.

    I have a separate Task table, with a one-many relationship from InstanceTable to Task. But when I try to add a row to the Task table, EF actually creates a new table in the "System" schema with the name Activities.DurableInstancing.InstancesTable. (Note the mix-up of the schema and table name elements). Since it is creating a foreign key constraint to a new empty table I get a foreign key constraint error. In summary, EF is creating the table:

    [System].[Activities.DurableInstancing.InstancesTable]

    instead of using the existing:

    [System.Activities.DurableInstancing].[InstancesTable]

     

    Thinking its just a simple case of some missing brackets in the generated SQL from EF, I changed my ToTable() call to include brackets around the schema:

    modelBuilder.Entity<DbInstance>().ToTable("InstancesTable", "[System.Activities.DurableInstancing]");

    This however led to yet a different error. Since the table and schema name is not something I can control, does anyone know what is necessary to force EF to use the existing table with the correct schema and table name?

    My fallback is to create my own view using a more standard schema.

     

    Thanks,

    Eric

     

     

    Monday, July 1, 2013 6:50 PM

Answers

  • Hi Eric,

    I have also tested with EF6, and it seems that there are some problems to define schema name with some '.' .

    If I define a schema like "[a.b.c]", the generated SQL is something like:

    IF schema_id('[a') IS NULL
        EXECUTE('CREATE SCHEMA [[a]')

    which is not the correct one.

    If you already have the database, you can try to use Entity Framework Power Tools and Reverse Engineer Code First. The entity classes and mapping classes will be automatically generated.

    Best regards,


    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Thursday, July 4, 2013 10:09 AM
    Moderator