locked
DataAnnotation or ModelBuilder Fluent API for Indexing Fields RRS feed

  • Question

  • We cannot seem to find a method using either approach (DataAnnotation or ModelBuilder) to make a non-primary key/non-foreign key indexed.  We will have millions of records in a table and require searching/sorting/filtering.  How do we specify a field to be indexed in SQL Server without running a script or manually touching SQL Server?  Additionally, let's assume another million records are added to the table--how do we re-index? 

    We'd like to see part of the Auto-Magic Migration feature (or the more sophisticated Migration feature) include the ability to tell SQL Server to rebuild any indexes.  Maybe indexing and re-indexing is already "magical" in SQL Server and we are not aware of it.

    This discussion is probably only applicable for CodeFirst and perhaps ModelFirst.

    Looking forward to a wonderful (it already works) answer!


    Thursday, September 1, 2011 8:49 PM

Answers

All replies

  • You have to build an initializer in DBContext and then set it in your code. Database.SetInitializer<Db>(new MyDb.Initializer()); Unfortunately, my code examples format wrong when I paste them in the forum. spawelk@live.com
    Friday, September 2, 2011 7:35 PM
  • @spawelk

    Do you know the DataAnnotation or ModelBuilder keyword?


    Sean Stenlund
    Friday, September 2, 2011 11:10 PM
  • Hello,

    Did you check Database.ExecuteSqlCommand method to pass a sql statement into it? Please check it here. http://msdn.microsoft.com/en-us/library/system.data.entity.database.executesqlcommand%28v=VS.103%29.aspx

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, September 5, 2011 3:04 AM
  • @Larcolais or @AnyOneWhoCanHelp

    We looked at the link you provided and found it non-helpful.

    We are using a CodeFirst approach with MVC3. Meaning, the database is built for us and described with either DataAnnotations or via the ModelBuilder Fluent API, or a combination of both. So, for example, assume we have:

    public class Person

    {

    public int ID { get; set; }

    public string Name { get; set; }

    }

    With EF and CodeFirst we will have a table in SQL Server called "Person" and it will have the two fields "ID" and "Name".

    Now, we can define some properties in SQL Server using DataAnnotations like this:

    public class Person

    {

    public int ID { get; set; }

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

    }

    Or, instead of using DataAnnotation we could have used the ModelBuilder Fluent API like this:

    modelBuilder.Entity<Person>().Property(p => p.Name).MaxLength = 10;

    There are many different things we can do with DataAnnotation and/or the ModelBuilder Fluent API.

    Our question is simply; we want the "Name" field to be indexed in SQL Server, how do we do it using DataAnnotation and/or the ModelBuilder Fluent API?


    Sean Stenlund
    Tuesday, September 6, 2011 6:47 PM
  • Hello again,

    AFAIK, Index must be added by SQL. there's no special construction in EF to create index. Like my previous reply, you can also check this thread which was similar with yours. http://stackoverflow.com/questions/5040795/adding-index-to-a-table/5041221#5041221

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Sean Stenlund Wednesday, September 7, 2011 8:05 PM
    Wednesday, September 7, 2011 3:04 AM
  • @Larcolais

    Thank you for your reply.  It appears this will work.  It also appears that the EF team may be working on providing indexing functionality for CodeFirst design using DataAnnotation.  http://data.uservoice.com/forums/72025-ado-net-entity-framework-ef-feature-suggestions/suggestions/2231176-indexattribute?ref=title.  We hope they do anyways.  Thank you again for your response.


    Sean Stenlund
    Wednesday, September 7, 2011 8:05 PM
  • Excellent! It looks like they are handling this issue with "Migration". Go EF team! http://blogs.msdn.com/b/adonet/archive/2011/09/06/code-first-migrations-alpha-2-walkthrough.aspx
    Sean Stenlund
    Wednesday, September 7, 2011 8:38 PM