EntityFramework - create clustered index on non-primary key

Hi:

I', trying to create a table using EF in which the primary key is a GUID and the clustered index is an auto-incrementing int column.  

The code-first class is as follows:
public class LearningObject
    {
        [Index(IsClustered = false)]
        public Guid Id { get; set; }

        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Index("ClusteredId", IsClustered = true)]
        public int ClusteredId { get; set; }

        [MaxLength(50)]
        public string Name { get; set; }

        public int ScoId { get; set; }
    }

Open in new window


Problem is I keep getting the following message:  Cannot create more than one clustered index on table 'dbo.LearningObjects'. Drop the existing clustered index 'PK_dbo.LearningObjects' before creating another.

Any help resolving thisw greatly appreciated.

RBS
RBSAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
The primary key will be created as a clustered index, and only one clustered index per table is permitted.  Lots of legerdemain will be required, if it is even possible at all.  You may succeed if you remove the indexes from both fields ans remove the primary key.  Index the identity field first with a clustered index.  Index the GUID field Next.  Then make it the PK.  Only one clustered index is permitted.  If the PK is created and a clustered index does not already exist, the PK index will be created as a clustered index by default.  Careful attention to the order in which things get done may allow you to create the odd configuration you desire.
0
ste5anSenior DeveloperCommented:
This is not easily possible, cause the Code-First approach means, that you don't bother the database implementation details. Here's the connect item to vote for.

Another option would be a custom database initializer, but this is imho pretty overkill. I would either go for an database-first approach here, cause you care for implementation or go the easy way like Nick said. Create a T-SQL script to be run after database initialization, which recreates the primary and clustered keys as you want.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Like it said above there can be only clustered index by table, since the clustered index is the way the records will be physically ordered.
I'm just wondering why do you need a GUID and an Identity ID? Why can't you work with an ID column only?
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

RBSAuthor Commented:
Hi:

Thanks for your comments.  I understand that only one clustered index is permitted per table.  It is my understanding that  that indexes based on integers are more efficient than on Guids and that clustered indexes do not have to be on the primary key and I need the Guid as my primary key.  I would be fine just removing the index from the Guid.

Where I am stuck right now is using CodeFirst to remove the clustered index from the primary key.  Any help in doing that would be greatly appreciated.

RBS
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Still can't understand why you just don't drop the GUID column.
About the CodeFirst, I'm sorry but I can't help. Never worked with that. I'm just a SQL Server guy.
0
RBSAuthor Commented:
I have a specification that the primary key is a Guid.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok but don't forget that the foreign keys should also be GUIDs.
So, when are you expecting to use the identity column ID?
0
Nick67Commented:
Ok,
Looking around regarding CodeFirst, I've read
We can create the database one of two ways:
1.Manually create and define the schema ourselves using a database tool (e.g. SQL Management Studio or Visual Studio)
2.Automatically create and generate the schema directly from our model classes using the EF Code-First library
http://weblogs.asp.net/scottgu/code-first-development-with-entity-framework-4

You are doing #2, but not necessarily getting the design you want.

But it looks very much like a C# environment.
So, I don't see any technical reason that you couldn't create a C# class to manipulate this table after CodeFirst creates it.
Or, for that matter, to use c# to create it the way you want it in the first place.
Is there some administrative or technical hurdle that prevents you from doing so?
0
RBSAuthor Commented:
No - just that I don't know how I to use C# to drop the index after codefirst creates it.
0
RBSAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for RBS's comment #a40640045

for the following reason:

my original question was never answered
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
What you mean wasn't answered?
 All Experts told you that isn't possible to have more than one cluster index per table. It's how things works with a database and is a rule that you can't avoid.
0
Nick67Commented:
The second entry in the google search
'c# drop index from table'
has a nice example of using C# to drop and recreate a primary key.

As we have said, you are only permitted one clustered index per table, so your requirement is going to mean either working with SQL Server Management Studio or C# to manipulate the table after CodeFirst creates it.
Or to create it yourself first, since CodeFirst isn't going to meet your requirements.
There's a nice example here
http://www.homeandlearn.co.uk/csharp/csharp_s12p3.html

So
I'm trying to create a table using EF in which the primary key is a GUID and the clustered index is an auto-incrementing int column.  
Your answer is, you cannot get CodeFirst to do that.
Depending upon what your environment and requirements are you next question will be
I have operational requirements for a table created by EF to have its indexes altered as follows: The primary key must be a GUID, but the clustered index must be an int identity column.  How can I use C# to accomplish this?
or
I have operational requirements for a table created by EF to have its indexes altered as follows: The primary key must be a GUID, but the clustered index must be an int identity column.  How can I use SSMS to accomplish this?
Or
I have operational requirements for a table as follows: The primary key must be a GUID, but the clustered index must be an int identity column.  How can I use C# to create this table and then have CodeFirst use it afterward?

You have been presented with ways forward.  You just have to choose what is right for you.
0
RBSAuthor Commented:
See my comment:

Thanks for your comments.  I understand that only one clustered index is permitted per table.  It is my understanding that  that indexes based on integers are more efficient than on Guids and that clustered indexes do not have to be on the primary key and I need the Guid as my primary key.  I would be fine just removing the index from the Guid.

Where I am stuck right now is using CodeFirst to remove the clustered index from the primary key.  Any help in doing that would be greatly appreciated.

to which vitor replied

Still can't understand why you just don't drop the GUID column.
About the CodeFirst, I'm sorry but I can't help. Never worked with that. I'm just a SQL Server guy.

I'm happy to give/share  Vitor credit - if that's what he want.  However, he did misunderstand my question - which was about CodeFirst to which he replied:  "About the CodeFirst, I'm sorry but I can't help. Never worked with that. I'm just a SQL Server guy."
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm happy to give/share  Vitor credit - if that's what he want
No. I just want that you close this question properly. Please check others Experts comments because I think they pointed you correctly.
0
Nick67Commented:
OK,

If you alter the order in your class, and indicate the key, does this get it done?
http://www.codeproject.com/Articles/813912/Create-Primary-Key-using-Entity-Framework-Code-Fir
public class LearningObject
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Index("ClusteredId", IsClustered = true)]
        public int ClusteredId { get; set; }

       
        [Key]
        public Guid Id { get; set; }

        [MaxLength(50)]
        public string Name { get; set; }

        public int ScoId { get; set; }
    }

Open in new window

0
RBSAuthor Commented:
My apologies to those who responded - Looking at my original question - it wasn't clear - I was trying to take an existing table that had a Guid as a clustered index and substitute an integer as the clustered index/unique key and I was trying to do it this with code first.  Every way that I tried doing it I would get an error.

The solution from Nick67 gave me the same error that I kept getting with different but similar versions:

Cannot create more than one clustered index on table 'dbo.LearningObjects'. Drop the existing clustered index 'PK_dbo.LearningObjects' before creating another.
0
Nick67Commented:
Ok.
You have an existing table, not a table created by CodeFirst, but not to your liking.
That makes things different.
I think you need to be looking at CodeFirst migrations.
https://msdn.microsoft.com/en-us/data/dn579398.aspx
And that's way beyond my scope, or the scope of the question the way you initially phrased  it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RBSAuthor Commented:
Actually, I don't have an existing table - I got the error when I tried to create a new table with the code you sent me.  That being said, I would like to thank you for your help - I moved on to use another approach.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.