The right way to manage database indexes with Entity Framework

Question

I'm building my application with Entity Framework (model first principle). I'm also using MS SQL Server 2008 to store all the data of my application.

After some time of developing I have the following code:

public partial class EventInfo
{
    #region Primitive Properties

    public virtual int Id
    {
        get;
        set;
    }

    public virtual string EventName
    {
        get;
        set;
    }

    public virtual string EventKey
    {
        get;
        set;
    }

    public virtual System.DateTime DateStamp
    {
        get;
        set;
    }

    #endregion
}

And Visual Studio database designer have created special chunk of sql code to map this entity to the database:

-- Creating table 'EventInfoSet'
CREATE TABLE [dbo].[EventInfoSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [EventName] nvarchar(max)  NOT NULL,
    [EventKey] nchar(32)  NOT NULL,
    [DateStamp] datetime  NOT NULL
);

And of course, an index for Id property

-- Creating primary key on [Id] in table 'EventInfoSet'
ALTER TABLE [dbo].[EventInfoSet]
ADD CONSTRAINT [PK_EventInfoSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);

The EventKey is string, and actually I use it to store md5 hash (in string representation). But the thing is that my main code looks like:

    int cnt = context.EventInfoSet.Where(e => e.EventKey == eventKey).Count();

and

    int cnt = context.EventInfoSet.Where(e => e.EventKey == eventKey && e.DateStamp >= dateFrom && e.DateStamp < dateTo).Count();

eventKey here is a string variable. As you can see, I often deal with EventKey property. But my table could contain huge amount of records (up to 5M). And I need this code to work as fast as possible. I didn't find any option to mark EventKey as index property in designer. And I want to know:

1. how can I make things faster? do I have to worry about it at all with my code?
2. is there any gentle way to force .NET development environment generate index field automatically?
3. if there is no gentle way, and I have to update it manually, how I can organize things better to do it automatically?
4. may be you give me a link to the article explaining all these things with indexes, cos my knowledge lacks here.

Thanks!

Answer 1

And of course, an index for Id property

Why 'of course'? If your main access, as you yourself admit, is to count by EventKey, or EventKey and DateStamp then your best clustered key is (EventKey, DateStamp), not ID:

CREATE CLUSTERED INDEX cdx_EventInfoSet 
    ON [dbo].[EventInfoSet] ([EventKey], [DateStamp]);
ALTER TABLE [dbo].[EventInfoSet] 
   ADD CONSTRAINT [PK_EventInfoSet]     
   PRIMARY KEY NONCLUSTERED ([Id] ASC);

Remember, clustered key and primary key are two distinct, unrelated, concepts. You should read Designing Indexes first (including all the subtopics in the link).

Answer 2

Entity framework will not create index for you. Entity framework only creates skeleton of the database. If you want to tweak your database for performance (like adding indexes) you must do it at your own. After that you can switch either to database first or you can use Entity Designer Database Generation Power Pack to update your database instead of recreating it (you will need at least VS 2010 Premium).