locked
Clustered Index Entity Framework RRS feed

  • Question

  • When I create the azure database with Entity Framework model first pattern, then the creation works. But when I want to run SQL commands, then I get the following error: "Tables without a clustered index are not supported". Is this a bug of entity Framework? I use Version 6.1.2. Any Workarounds?
    Tuesday, March 3, 2015 12:29 PM

Answers

  • Hi engenious,
    I cannot comment on whether this is a bug in EF or not.
    Only the latest update of SQL Database (V12) supports tables without indexes. In all non V12 servers in SQL database a clustered index is requiered. The existance of the index is checked when you try to insert data.
    If you define attributes in your objects as primary key, an index should be created.
    Jan
    Tuesday, March 3, 2015 4:24 PM

All replies

  • I found that this seems to be a bug. Is this correct?

    

    Tuesday, March 3, 2015 1:12 PM
  • Hi engenious,
    I cannot comment on whether this is a bug in EF or not.
    Only the latest update of SQL Database (V12) supports tables without indexes. In all non V12 servers in SQL database a clustered index is requiered. The existance of the index is checked when you try to insert data.
    If you define attributes in your objects as primary key, an index should be created.
    Jan
    Tuesday, March 3, 2015 4:24 PM
  • Hi Jan,

    Thanks for your answer. I checked all tables and all tables has a Primary key. This is automatically generate by the EF. It creates an Id Primary key.

    I don't know what is the differnce to a clustered Primary key?

    Tuesday, March 3, 2015 6:04 PM
  • A primary key often gives you a clustered index, but it is not a requirement.  Primary keys are a close relative of a unique constraint.  Check for a clustered index explicitly.  If there isn't one, then you can add one and using the primary key is probably a good place to start looking for what to create it on.
    Tuesday, March 3, 2015 9:19 PM
  • Do you know how to create a clustered index with entity Framework model first pattern? I didn't found an Attribute for that.
    Wednesday, March 4, 2015 6:30 AM
  • Wednesday, March 4, 2015 6:32 AM
  • Hi Girish,

    I think the first link only works with code first pattern, but not with model first pattern, correct?

    Do you have an workaround for that problem?

    Regards

    Andreas

    Friday, June 5, 2015 11:30 AM
  • Hey,

    Would you be able to share the model that you are creating the database from? Can you also include the database creation script that is generated from your model.

    In general, the tables should all have clustered indexes on them. Here is an example of the database creation script that gets generated for a very simple model with a single entity.

    -- --------------------------------------------------
    -- Creating all tables
    -- --------------------------------------------------
    
    -- Creating table 'Blogs'
    CREATE TABLE [dbo].[Blogs] (
        [Id] int IDENTITY(1,1) NOT NULL,
        [Url] nvarchar(max)  NOT NULL
    );
    GO
    
    -- --------------------------------------------------
    -- Creating all PRIMARY KEY constraints
    -- --------------------------------------------------
    
    -- Creating primary key on [Id] in table 'Blogs'
    ALTER TABLE [dbo].[Blogs]
    ADD CONSTRAINT [PK_Blogs]
        PRIMARY KEY CLUSTERED ([Id] ASC);
    GO

    As you can see the primary key is clustered, so there must be something specific about your model that is not creating a clustered index.

    ~Rowan


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.


    Friday, June 12, 2015 11:18 PM