Category Archives: Entity Framework

Entity Framework – Lazy Loading

Posted on by
Reply

I want to analyse in this post how Entity Framework really interact with the database. i.e. The queries that are really executed.

In previous post I created a database using Code First. These are two of my entities:

A food representation:

public class Food
{
    /// <summary>
    /// Gets or sets the food identifier.
    /// </summary>
    [Key]
    // NOTE: As the name is className + id, this property is not needed
    public int Id { get; set; }
 
    /// <summary>
    /// Gets or sets the food name.
    /// </summary>
    [MaxLength(200)]
    [Index(IsUnique = true)]
    public string Name { get; set; }
 
    /// <summary>
    /// Gets or sets the recipes needing this Food
    /// </summary>
    // NOTE: EF needs the virtual attribute to enable Lazy loading 
    public virtual List<Recipe> RecpiesNeedingThisFood { get; set; }
 
    /// <summary>
    /// Returns a <see cref="System.String" /> that represents this instance.
    /// </summary>
    public override string ToString()
    {
        return string.Format("{0} ({1})", this.Name, this.Id);
    }
}

An entry on my fridge representation: a food and an expire date of this food.

public class FridgeEntry
{
    /// <summary>
    /// Initializes a new instance of the <see cref="FridgeEntry"/> class.
    /// </summary>
    public FridgeEntry()
    {
        ExpireDate = DateTime.MaxValue;
    }
 
    public int Id { get; set; }
 
    [Required]
    // NOTE virtual is needed for lazy loading
    public virtual Food Food { get; set; }
 
    /// <summary>
    /// Gets or sets the expire date.
    /// </summary>
    public DateTime ExpireDate { get; set; }
 
 
    /// <summary>
    /// Returns a <see cref="System.String" /> that represents this instance.
    /// </summary>
    public override string ToString()
    {
        return string.Format("FoodId ({0}) - expire date: {1}", this.Id, this.ExpireDate);
    }
}

How to debug the queries and connections between EF and my database?

A new feature of EF6 is the possibility to log everything that Entity Framework is doing.

The context.Database has a Log property which is type Action<string> if this action is not null EF will call it always he connect to the database.

Define a logger, to analyse the Lazy loading a simple method writing in console the messages is enough:

private static void ConsoleLogger(string msg)
{
    msg = msg.Trim();
 
    if (!string.IsNullOrEmpty(msg))
    {
        Console.ForegroundColor = ConsoleColor.DarkGray;
        Console.WriteLine(DateTime.Now.TimeOfDay.ToString("g"));
        Console.ForegroundColor = ConsoleColor.DarkGreen;
        Console.WriteLine(msg);
 
        Console.ResetColor();
    }
}

Now we just have to define this  logger as the logger of the context.Database.

EF Lazy loading testing

We have already a way to see the connections between EF and the database, now let’s analyse it:

Continue reading →

Entity Framework – Local and Azure connection

Posted on by
Reply

In previous posts I used EF using the local database and using the Azure SQL Server. This is the configuration I use now to connect to my local database in Debug mode and to my Windows Azure database in Release Mode.

App.config

  <connectionStrings>
    <!--Azure MyFood database-->
    <add 
        name="MyFoodAzure"
        providerName="System.Data.SqlClient"
        connectionString="Data Source=i608rt1tak.database.windows.net;Initial Catalog=MyFood;Integrated Security=False;User ID=ravened;Password=pass¿;Connect Timeout=60;Encrypt=False;TrustServerCertificate=False" />
    
    <!--Local MyFood database-->
    <add 
        name="MyFoodLocal"
        providerName="System.Data.SqlClient"
        connectionString="Server=(localdb)\v11.0;Integrated Security=true;Database=MyFood;"/>
  </connectionStrings>

DbContext

    public class FoodContext : DbContext
    {
 
        // NOTE: Call base constructor to specify the connection string name
#if !DEBUG
        // In Release mode it will look for the Cloud connection string
        public FoodContext() : base("name=MyFoodAzure") { }
#else
        // In Release mode it will look for the Cloud connection string
        public FoodContext() : base("name=MyFoodLocal") { }
#endif
 
        public DbSet<Food> Foods { get; set; }
        public DbSet<Recipe> Recipes { get; set; }
        public DbSet<FridgeEntry> Fridge { get; set; }
 
    }

Azure SQL Server – Entity Framework Connection

Posted on by
Reply

By default when Entity framework is added to a project, a file “App.config” is created if it doesn’t exists yet.
( “Web.config” in web applications).
A default connection factory that will connect to a local database is added in this file:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
 
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
 
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.1" />
  </startup>
 
  <!-- By default, when no connection string is specified use the local database.-->
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
 
</configuration>
  

This factory will be used when no connection string for the specified context is found. But let clarity how EF try to find the connection string to use…

Which connection string will EF use?

To connect to the database we use a context that extend DbContext.

By default if we don’t call a DbContext base constructor, E.F. will look for a connection string name using the name of our defined context.

namespace CodeFirstAzureTest.Data
{
    public class FoodContext : DbContext
    {
        public DbSet<Food> Foods { get; set; }
        public DbSet<Recipe> Recipes { get; set; }
        public DbSet<FridgeEntry> Fridge { get; set; }
 
    }
}

It is possible to specify the name of the connection string or the complete connection string calling the base constructor:

using System.Data.Entity;
using CodeFirstAzureTest.Data.Entities;
 
namespace CodeFirstAzureTest.Data
{
    public class FoodContext : DbContext
    {
        // NOTE: Call base constructor to specify the connection string name
        public FoodContext() : base("name=MyFood") { }
 
        public DbSet<Food> Foods { get; set; }
        public DbSet<Recipe> Recipes { get; set; }
        public DbSet<FridgeEntry> Fridge { get; set; }
 
    }
}

When no connection string is defined, if a defaultConnectionFactory is defined, EF will use it to create a connection string using the name of the connection string he needs.

e.g. If we don’t specify the connection string name in our context and EF cannot find any connection string the factory will create one using exactly this name. This is why by default the database created locally had this name in my previous post.

Connect EF to Windows Azure

To connect to a Windows Azure we need to do the following steps:

Continue reading →

Entity Framework 6 – Migrations, Advanced Topics

Posted on by
Reply

In previous posts I created a database using EF code-first and I enabled migrations.

Now lets try some advanced topics:

  • Downgrades & migrations to specific  versions
  • Customizing Migrations
  • Execute custom SQL with the migration
  • Get migration generated SQL
  • Auto migrate when the application start and additional actions

Downgrades & migrations to specific  versions

To migrate to an specific version run in command in Package Manager Console.

Update-Database –TargetMigration: MigrationName

Customizing Migrations

Let’s add a couple of new properties to our Model and add a migration to generate the default migration. We will add some information to our recipes:

  • A name: the name shall be unique for each recipe.
  • A complexity index: each recipe shall have a complexity index from 1 to 3, the default complexity level shall be 2.

This is our new Recipe class with a name and complexity index:

public class Recipe
{
    // Primary Key
    public int RecipeId { get; set; }
 
    // EF needs the virtual attribute to enable Lazy loading 
    public virtual List<Food> Ingredients { get; set; }
 
    // Recipe description
    public string Description { get; set; }
 
    // Recipe name
    [Index(IsUnique = true)]
    [MaxLength(200)] 
    public string Name { get; set; }
 
    // Recipe complexity
    [Range(1, 3)]
    [DefaultValue(2)]
    public int Complexity { get; set; }
}

Add the default migration:

PM> Add-Migration Recipe_NameAndComplexity
Scaffolding migration ‘Recipe_NameAndComplexity’.
The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running ‘Add-Migration Recipe_NameAndComplexity’ again.

Continue reading →

Entity Framework 6 – Enable Migrations

Posted on by
Reply

In a previous post I tested Entity Framework 6 Code First. In a production environment it is more than probable that we would need to make changes to our Model. E.g. We could need a new property.

Using EF and code first I would expect a way to add this property to my Entity class and somehow apply this change to my database. This is what I am going to test and share in this post.

As a start point of this post I will use the model created here: Entity Framework 6 – Code First.

We have already the entity Recipe in our model, and now we would like to add a simple new property type string, to store the recipe description.

public class Recipe
{
    // Primary Key
    public int RecipeId { get; set; }
 
    // EF needs the virtual attribute to enable Lazy loading 
    public virtual List<Food> Ingredients { get; set; }
 
    // New property with the description of the recipe
    public string Description { get; set; }
}

If we run now the application this exception will appears:

image

Enable EF Code first migrations

  • Run the Enable-Migrations command in Package Manager Console

The output should be something similar to:

PM> Enable-Migrations
Checking if the context targets an existing database…
Detected database created with a database initializer. Scaffolded migration ‘201407242003173_InitialCreate’ corresponding to existing database. To use an automatic migration instead, delete the Migrations folder and re-run Enable-Migrations specifying the -EnableAutomaticMigrations parameter.
Code First Migrations enabled for project CodeFirstAzureTest.

The command created a “Migrations” folder in my project with two classes:

Continue reading →