Unidirectional Many to Many Relationship With Entity Framework Code First on Windows Azure SQL Database

There are times when entities don’t need navigation properties on both sides of the relationship. This is an example you can use to map to an existing database or generate a brand new database on Windows Azure SQL Database.

The ERD shows a Many to Many relationship between the FlightLog and the Airport entities. The requirements for the current model has no need for Airport to have a Navigation Property of FlightLogs.

Using Entity Frame Work Code First, the following statement can be used in the EntityTypeConfiguration to configure the relationship.

HasMany(r => r.Airports)
    .WithMany() // No navigation property here
    .Map(m =>
        {
            m.MapLeftKey("FlightLogId");
            m.MapRightKey("AirportId");
            m.ToTable("BridgeTableForAirportsAndFlightlogs");
        });

FlightLog has many Airports, the relationship is held in an intermediate table called BridgeTableForAirportsAndFlightlogs. By not specifying a property for the inverse Navigation Property, a unidirectional Many to Many relationship is created.

Using Entity Framework Code First to generate a database instance on Windows Azure SQL Database

Before you start, you need to have a Windows Azure Account and a logical server configured in Windows Azure SQL Database.

 

To create a logical server, navigate to the SQL DATABASES section, then click on SERVERS. Click on the ADD button located on the bottom of the page.

Create a Login and be sure that you select the same Region that will be hosting your application.

Once the Server is created, Click on the new server name and navigate to the CONFIGURE section.

Then click on the arrow next to “ADD TO ALLOWED IP ADDRESSES” this will effectively allow requests from your current IP Address to reach the SQL Database Service.

Configure your application to use a connection string that points to your newly created logical server on

Windows Azure SQL Database.

Data Source=SERVER-NAME.database.windows.net;
Initial Catalog=FlightLogDatabase;
Persist Security Info=True;
User ID=SERVER_LOGIN;
Password=SERVER_PASSWORD;
MultipleActiveResultSets=True;
Encrypt=True

Set the name of the database instance you wish to create as the Initial Catalog value.

Executing the following code will create the database if it does not exist. Then it will use the Database Initializer to seed the database and it will use the ReliableModel to execute the query returning the seeded data. The database creation process is triggered by the first query made to the database.

Database.SetInitializer(new InitializeDatabase());

var cs = CloudConfigurationManager.GetSetting("DatabaesConnectionString");

var logs = ReliableModel.Query(m =>
    {
        var query = m.FlightLogs
                        .Include(f => f.Airplane)
                        .Include(f => f.Airports);

        Console.WriteLine("SQL\n\n"
                        + query.ToString());

        return query.ToList();
    }
    ,
    () => new Model.Model(cs));

Console.WriteLine("\n\nResult\n\n");

logs.ForEach(l =>
    {
        Console.WriteLine("{0} {1}",
            l.Airplane.Name,
            l.Airplane.TailNumber);

        Console.WriteLine("Last updated " + l.LastUpdate);

        l.Airports.ToList().ForEach(p =>
            {
                Console.WriteLine("{0} {1}",
                    p.Code,
                    p.Name);
            });
    });

Calling the ToString method on the IQueryable will return the SQL statement that will be executed on the SQL Database.

The Database should now show up In the Windows Azure portal. The Database created is a 1Gb Web Edition. You can change these settings when ever your needs out grow these configurations.

 

Defining & Configuring The Model And It’s Entities

public class Model : DbContext
{
    public Model(string connectionString)
        : base(connectionString)
    {
        Configuration.LazyLoadingEnabled = false;
    }

    public Model()
    {
        Configuration.LazyLoadingEnabled = false;
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new AirportConfiguration());
        modelBuilder.Configurations.Add(new AirplaneConfiguration());
        modelBuilder.Configurations.Add(new FlightLogConfiguration());
    }

    public DbSet<Airport> Airports { get; set; }
    public DbSet<Airplane> Airplanes { get; set; }
    public DbSet<FlightLog> FlightLogs { get; set; }
}

To build the model, inherit from DbContext and add DbSet properties for each Entity. To override conventions and configure your model, override the OnModelCreating method and add EntityTypeConfiguration objects for each of the entities.

 

Airport Configuration

public class AirportConfiguration
    : EntityTypeConfiguration<Airport>
{
    public AirportConfiguration()
    {
        HasKey(e => e.AirportId);

        Property(e => e.Code)
            .IsRequired()
            .IsVariableLength()
            .IsUnicode(false);

        Property(e => e.Name)
            .IsRequired()
            .IsVariableLength()
            .IsUnicode(false);
    }
}

Use the HasKey method to define your table’s Primary Key. Heap tables aren’t supported in Windows Azure SQL Database, therefore tables MUST have a Clustered Index. In our case Entity Framework will set the Primary Key to be a Clustered Index.

Use the Property method to override default configurations applied to your entities by convention. In this case I have marked Code and Name to be NOT NULL and I have set them to be VARCAHR. Setting IsUnicode to False will ensure that Entity Framework will not try to convert strings to or from NVARCAHR when making comparisons.

 

Airplane Configuration

public class AirplaneConfiguration
    : EntityTypeConfiguration<Airplane>
{
    public AirplaneConfiguration()
    {
        HasKey(e => e.AirplaneId);
            
        Property(e => e.TailNumber)
            .IsRequired()
            .IsVariableLength()
            .IsUnicode(false);
            
        Property(e => e.Name)
            .IsRequired()
            .IsVariableLength()
            .IsUnicode(false);

        HasMany(e=> e.FlightLogs)
            .WithRequired(e=> e.Airplane)
            .WillCascadeOnDelete(true);
    }
}

 

FlightLog Configuration

public class FlightLogConfiguration
    : EntityTypeConfiguration<FlightLog>
{
    public FlightLogConfiguration()
    {
        HasKey(e => e.FlightLogId);

        Property(e => e.LastUpdate);

        HasMany(r => r.Airports)
           .WithMany() // No navigation property here
           .Map(m =>
            {
                m.MapLeftKey("FlightLogId");
                m.MapRightKey("AirportId");
                m.ToTable("BridgeTableForAirportsAndFlightlogs");
            });
    }
}

 

Seeding the Database

Seeding the database is done by inheriting from a Database Initializer and overriding the Seed method.

public class InitializeDatabase : CreateDatabaseIfNotExists<Model>
{
    protected override void Seed(Model context)
    {
        var yul = new Airport
            {
                Code = "YUL",
                Name = "Montréal-Pierre Elliott " +
                        "Trudeau International Airport"
            };

        var cdg = new Airport
            {
                Code = "CDG",
                Name = "Charles de Gaulle " +
                        "International Airport"
            };

        var a = new Airplane
            {
                TailNumber = "DC920",
                Name = "A380"
            };

        var l = new FlightLog
            {
                Airplane = a
            };

        l.Airports.Add(cdg);
        l.Airports.Add(yul);
        l.LastUpdate = DateTime.UtcNow;
        
        context.FlightLogs.Add(l);
        context.SaveChanges();
    }        
}

As you may have noticed, Entity Framework is able traverse graphs of objects and is smart enough to mark the entities as added. Therefore, I can create an object graph, add it’s root to a DbSet and call SaveChanges. If everything is properly configured, Entity Framework will take care of the rest and the data will be properly persisted.

 

Airport Entity

public class Airport
{
    public int AirportId { get; set; }
    public string Code { get; set; }
    public string Name { get; set; }
}

You may have noticed that this Entity is quite ordinary, the term used to describe it, is POCO (Plain Old CLR Object). Since my entities do not have dependencies on Entity Framework, I usually place them in their own assembly. This allows me to use them throughout my application without having any dependencies on my DAL (Data Access Layer) and in most cases they are easy to serialize.

 

Airplane Entity

public class Airplane
{
    public Airplane()
    {
        FlightLogs = new HashSet<FlightLog>();
    }

    public int AirplaneId { get; set; }
    public string TailNumber { get; set; }
    public string Name { get; set; }

    public ICollection<FlightLog> FlightLogs { get; set; }
}

You may have noticed that I deactivated Lazy Loading in the Model. I usually do this to eliminate problems like N+1 situations, in turn limiting many performance issues from happening. Since Lazy Loading is deactivated I do not need to have virtual properties as my Navigation Properties. By personal preference I favor Explicit Loading and Eager Loading over Lazy Loading.

 

FlightLog Entity

public class FlightLog
{
    public FlightLog()
    {
        Airports = new HashSet<Airport>();
    }

    public int FlightLogId { get; set; }
    public DateTime LastUpdate { get; set; }

    public Airplane Airplane { get; set; }
    public ICollection<Airport> Airports { get; set; }
}

It is best practice to initialize collections in the constructor, it will greatly facilitate your work and reduce the possibility of trying to add an Entity to a null collection.

 

Conclusion

I found the opportunity presented by detailing how to configure a Unidirectional Many to Many relationship with Entity Framework to be an ideal way for me to detail how to create a Database on Windows Azure SQL Database using Code First.

Entity Framework can be quite complex when you start bending it to your will. This post has the goal of giving pointers and directions in which you can continue your journey. Julie Lerman’s Blog is an amazing resource for anything related to Entity Framework. Furthermore, if you plan on using Entity Framework Code First, I strongly recommend reading the following books:

• Programming Entity Framework: Code First
• Programming Entity Framework: DbContext

Both books are quick reads and hold priceless information that will greatly impact the way you work with

Entity Framework.

Get the code : https://github.com/brisebois/EntityFrameworkManyToManyDemo1