The right way to manage database indexes using Entity Framework

Question

I create my application with the Entity Framework (the first principle of the model). I also use MS SQL Server 2008 to store all the data in my application.

After some development time, 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 the Visual Studio database developer created a special sql code snippet to map this object 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, the index for the Id property

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

EventKey is a string, and in fact I use it to store the md5 hash (in string representation). But the fact is that my main code is as follows:

    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 - . , EventKey. ( 5M). , . EventKey . :

• ? ?
• - .NET ?
• , , ?
• , , , .

!

Answer 1

, , Id

""? , , EventKey EventKey DateStamp, (EventKey, DateStamp), ID:

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

, , . ( ).

Answer 2

Entity . Entity . (, ), . , Entity Designer ( , VS 2010 Premium).