If you’re interested in the Include method in a LINQ to Entities queries to be type-safe, then Joe Ferner has the answer for you in this post: Type-Safe Entity Framework Include: http://www.nearinfinity.com/blogs/joe_ferner/type-safe_entity_framework_inc.html

If you’re using Entity Framework Code First, then you may want to create an index on some of your table. The way to do this is to call “Database.ExecuteSqlCommand” in the “Seed” method of your database initializer. The sample below shows how it is done (thanks to Rolf for pointing me in the right direction):

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.Entity;

using System.ComponentModel.DataAnnotations;

namespace CodeFirstPlayground

{

  class Program

  {

    static void Main( string[] args )

    {

      Database.SetInitializer<CodeFirstSampleModel>( new CodeFirstSampleDbInitializer() );

      using ( var model = new CodeFirstSampleModel() )

      {

        var query = from c in model.Customers

                    where c.Name != null

                    select c;

        foreach ( var item in query )

        {

          Console.WriteLine( item.Name );

        }

      }

      Console.ReadLine();

    }

  }

  public class CodeFirstSampleModel : DbContext

  {

    public CodeFirstSampleModel()

      : base( "CodeFirstSampleDB" )

    {

      Customers = this.Set<Customer>();

    }

    public DbSet<Customer> Customers { get; set; }

  }

  public class CodeFirstSampleDbInitializer : DropCreateDatabaseAlways<CodeFirstSampleModel>

  {

    protected override void Seed( CodeFirstSampleModel context )

    {

      context.Database.ExecuteSqlCommand( "CREATE INDEX IX_Customer_Name ON Customers (Name) " );

      Customer c = new Customer()

      {

        Name = "Mark",

        LastOrder = DateTime.Now

      };

      context.Customers.Add( c );

      base.Seed( context );

    }

  }

  public class Customer

  {

    [Key]

    public int Id { get; set; }

    [Required]

    [MaxLength( 50, ErrorMessage = "Customer name must be 50 characters or less." )]

    public string Name { get; set; }

    public DateTime? LastOrder { get; set; }

  }

}

In the model below the phone number for an artist is actually a complex type (a little over engineered, I know, but I was just exploring how well this works).

The complex type consists of 4 ‘fields’: CountryCode, AreaCode, Number and Extension:

Each ‘field’ has properties set:

The great part is that this is fully supported by the DDL generator, so right click on the Entity Framework Designer in Visual Studio 2010 and choose ‘Generate Database From Model…’ and the ‘Artist’ table will be generated as:

-- --------------------------------------------------

-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure

-- --------------------------------------------------

-- --------------------------------------------------

-- Creating all tables

-- --------------------------------------------------

-- Creating table 'Artists'

CREATE TABLE [dbo].[Artists] (

    [Id] int IDENTITY(1,1) NOT NULL,

    [Name] nvarchar(100)  NOT NULL,

    [IsIndividual] bit  NOT NULL,

    [Phone_CountryCode] nvarchar(4)  NOT NULL,

    [Phone_AreaCode] nvarchar(5)  NOT NULL,

    [Phone_Number] nvarchar(10)  NOT NULL,

    [Phone_Extension] nvarchar(10)  NULL

);

GO

-- <snip other tables>

-- --------------------------------------------------

-- Creating all PRIMARY KEY constraints

-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Artists'

ALTER TABLE [dbo].[Artists]

ADD CONSTRAINT [PK_Artists]

    PRIMARY KEY CLUSTERED ([Id] ASC);

GO

-- <snip other primary and foreign key>

-- --------------------------------------------------

-- Script has ended

-- --------------------------------------------------

If you’re using the Entity Framework and don’t want to implement ToTraceString() at strategic points in your code in order to see what kind of SQL statements are getting generated then check out one of the following:

• Community Entity Framework Provider Wrappers (free)
  • http://efwrappers.codeplex.com/
  • Or use NuGet:
    • Install-Package CommunityEFProviderWrappers.EFTracingProvider
• Entity Framework Profiler
• http://efprof.com/
• SQL Server Profiler
• More info on setting up SQL Server Profiler: http://blog.tonysneed.com/2010/08/05/setting-up-sql-server-2008-express-with-profiler

Update 07-02-2011: Added SQL Server Profiler after comment by Tony Sneed.

If you’re doing Model First with Entity Framework you may run into a scenario where you want to design a 1-to-1 relationship, but also have a foreign key be available on your entity. The default behavior in Entity Framework when doing a 1-to-1 relationship (with Model First), or 1-to-0..1 relationship, is to create a foreign key on the 0..1 side of the association but hide the foreign key. A better approach is this:

• Start a new model
• Add the entity that goes on the 1 side of the association, give it a primary key of type integer. Make sure the StoreGeneratedPattern property is set to Identity.
• Add the entity that goes on the 0..1 side of the assocation, name the primary key as you would a foreign key, make it of type integer. Make sure the StoreGeneratedPattern property is set to None.
  
• Click on the 1 entity and add an association, make it a 1-to-0..1, example:
  
• Double click the association and set a referential constraint.
  
• Now you’re ready to generate your database schema.