Automatically Define the State of All Entities in the Graph in EntityFramework

• Download demo - 153.8 KB

The Problem

One of the most popular questions is if there exists a method which will automatically decide whether to insert, update or delete entity in the Entity Framework. It is not possible in EF.

Usually, we find ourselves writing very similar codes for defining the state of entities. Normally, the procedure is as follows:

• Determine which properties are needed for defining the existence of the entity in the database (this could be primary key properties or unique key properties)
• If the result is null, then the entity must be inserted.
• If the result is not null and if a change has occurred in any part of the entity, then the entity must be updated.
• If we have a collection of entities, then we need to compare it with the ones in the database and delete those which do not exist in the collection anymore.

and so on …

Additional explanation about why sometimes we need unique key properties in addition to the primary key properties.

Say we have Phone entity which has some properties: ID, Digits, Prefix …

ID is auto-generated primary key. In the meanwhile, we do not want to insert the same phone number to the table with a different ID. So Digits and Prefix properties are unique together. This situation is forcing us to take into consideration this:

If primary key has default value, and there is no corresponding entity in the database with the specified Digits and prefix, then it must be inserted. Otherwise, if a change has occurred, then it must be updated and so on…

Now, let’s do the same things all over again for a different entity graph. Again, and again…

Background

It is recommended to have knowledge about Code First approach in Entity Framework. But, you must follow these 2 rules:

1. Use Foreign-Key Association while creating models. It means that, you must have a foreign key property in your model in addition to the corresponding Navigational Property. For example:

   C#

   public class Person
   {
       ...
       public int DocumentId {get; set;} //Foreign-Key property
       public virtual Document Document { get; set; } // Navigational Property  
       ...
   }

2. If you must configure Many-to-Many relationship between the Person and Phone entity classes, then you must explicitly create PersonPhone model.

I am going show you these 2 rules in practice in the step-by-step tutorial. So, don't worry.

What to Install

I have already published the repository in the Github. Also, you can use Nuget to easily download the library with its dependencies:

Install-Package EntityGraphOperations

What Does This API Offer

I have already explained the problem in the introduction of this post. The solution is to use EntityGraphOperations for Entity Framework Code First.

Features

• Automatically define state of all entities
• Update only those entities which have changed
• Configuration over properties (for example, you can choose which properties must not be compared for defining whether the entity has been modified or not)
• Supports duplicate entities
• Fluent API style mapping of special entity configurations
• Let the user manually manage graph after automatically determining state of all entities

How Does This API Work?

Let’s say I have a Person object. Person could have many phones, a Document and could have a spouse.

public class Person
{
     public int Id { get; set; }
     public string FirstName { get; set; }
     public string LastName { get; set; }
     public string MiddleName { get; set; }
     public int Age { get; set; }
     public int DocumentId {get; set;}

     public virtual ICollection<PersonPhone> PersonPhones { get; set; }
     public virtual Document Document { get; set; }
     public virtual PersonSpouse PersonSpouse { get; set; }
}

I want to determine the state of all entities which are included in the graph:

context.InsertOrUpdateGraph(person)
       .After(entity =>
       {
            // Delete missing phones.
            entity.HasCollection(p => p.PersonPhones)
               .DeleteMissingEntities();

            // Delete if spouse is not exist anymore.
            entity.HasNavigationalProperty(m => m.PersonSpouse)
                  .DeleteIfNull();
       });

Also, as you remember, unique key properties could play a role while defining the state of Phone entity. For such special purposes, we have ExtendedEntityTypeConfiguration<> class, which inherits from EntityTypeConfiguration<>. If we want to use such special configurations, then we must inherit our mapping classes from ExtendedEntityTypeConfiguration<>, rather than EntityTypeConfiguration<>. For example:

public class PhoneMap: ExtendedEntityTypeConfiguration<Phone>
{
    public PhoneMap()
    {
         // Primary Key
         this.HasKey(m => m.Id);

          …

         // Unique keys
         this.HasUniqueKey(m => new { m.Prefix, m.Digits });
    }
}

That's all! InsertOrUpdateGraph will automatically set the entities state as Added or Modified. And after that, we will define which entities must be deleted if they do not exist anymore.

Step-by-Step Sample Project

Pre-requisite

Before we start creating a demo application, let's ensure that you have all the ingredients to create an application.

• You need Visual Studio 2010+
• If you are doing with Oracle, then the recommendation is to install Oracle.ManagedDataAccess.EntityFramework package from nuget:
  Install-Package Oracle.ManagedDataAccess.EntityFramework
• Copy&Paste and execute these .sql files for creating tables (only if you are using Oracle)

By the way, it is personal preference, but I allow EF Code-First to create tables in the database only if the database is on MS Sql Server. But, if I am using Oracle, then I prefer to create database on my own and set initializer to null in the DbContext class. (I will show what I want to say, so don't worry). That's why I gave you the .sql file for creating tables if you are using Oracle.

Database Structure

I have created 5 tables:

1. Persons - ID, First Name, Last Name, Middle Name, Age, Document ID
2. Documents - ID, Document Number, Document Series
3. Spouses - Person Id, Spouse First Name, Spouse Last Name, Spouse Middle Name
4. Phones - ID, Digits, Prrefix
5. PersonPhones - Phone ID, Person ID

I hope that you have understood the relation between these tables. Anyway, I want to explain the relation in brief.
A person can have multiple phones, a document and a spouse. Digits and Prefix properties are unique together. So, we must be sure to insert the same phone only once. If any other person has the same phone number, then API will get and use its primary key.

Step 1 - Create the Console Application

To begin, create a brand-new Console Application named EntityGraphOperationsDemo.

Let's spend some time in understanding the solution created. The first thing you will notice is that there are some folders and classed created, see the below figure:

As you see, Entities folder contains two subfolders: Models and Mappings.

Models folder contains our entity models. And their mapping details are stored inside the Mappings folder.

Step 2 - Create Our Models

As I explained in the background section of this article, we will use Foreign-Key association technique while creating our entity models.

public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    string LastName { get; set; }
    public string MiddleName { get; set; }
    public int Age { get; set; }
    public int DocumentId { get; set; }

    public virtual ICollection<PersonPhone> PersonPhones { get; set; }
    public virtual Document Document { get; set; }
    public virtual PersonSpouse PersonSpouse { get; set; }
}

public class Document
{
    public int Id { get; set; }
    public string DocumentNumber { get; set; }
    public string DocumentSeries { get; set; }
}

public class PersonSpouse
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string MiddleName { get; set; }

    public virtual Person Person { get; set; }
}

public class Phone
{
    public int Id { get; set; }
    public string Prefix { get; set; }
    public string Digits { get; set; }
}

public class PersonPhone
{
    public int PersonId { get; set; }
    public int PhoneId { get; set; }

    public virtual Person Person { get; set; }
    public virtual Phone Phone { get; set; }
}

And, if we want to take advantages of this API then, our mapping classes must inherit from ExtendedEntityTypeConfiguration<T>:

public class PersonMap : ExtendedEntityTypeConfiguration<Person>
{
    public PersonMap()
    {
        // Primary Key
        this.HasKey(m => m.Id);

        // Table & Column Mappings
        this.ToTable("PERSONS", "EGO_TEST");

        this.Property(m => m.Id)
            .HasColumnName("ID")
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
            .IsRequired();

        this.Property(m => m.FirstName)
            .HasColumnName("FIRST_NAME")
            .HasMaxLength(60)
            .IsRequired();

        this.Property(m => m.LastName)
            .HasColumnName("LAST_NAME")
            .HasMaxLength(60)
            .IsRequired();

        this.Property(m => m.MiddleName)
            .HasColumnName("MIDDLE_NAME")
            .HasMaxLength(60)
            .IsRequired();

        this.Property(m => m.Age)
            .HasColumnName("AGE")
            .IsRequired();

        this.Property(m => m.DocumentId)
            .HasColumnName("DOCUMENT_ID")
            .IsRequired();

        // Relationships
        this.HasRequired(m => m.Document)
            .WithMany()
            .HasForeignKey(m => m.DocumentId);
    }
}

public class DocumentMap : ExtendedEntityTypeConfiguration<Document>
{
    public DocumentMap()
    {
        // Primary key
        this.HasKey(m => m.Id);

        // Table & Column Mappings
        this.ToTable("DOCUMENTS", "EGO_TEST");

        this.Property(m => m.Id)
            .HasColumnName("ID")
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
            .IsRequired();

        this.Property(m => m.DocumentNumber)
            .HasColumnName("DOCUMENT_NUMBER")
            .HasMaxLength(12)
            .IsRequired();

        this.Property(m => m.DocumentSeries)
            .HasColumnName("DOCUMENT_SERIES")
            .HasMaxLength(3)
            .IsRequired();
    }
}

public class PersonSpouseMap : ExtendedEntityTypeConfiguration<PersonSpouse>
{
    public PersonSpouseMap()
    {
        // Primary key
        this.HasKey(m => m.PersonId);

        // Table & Column Mappings
        this.ToTable("SPOUSES", "EGO_TEST");

        this.Property(m => m.PersonId)
            .HasColumnName("PERSON_ID")
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)
            .IsRequired();

        this.Property(m => m.FirstName)
            .HasColumnName("SPOUSE_FIRST_NAME")
            .HasMaxLength(60)
            .IsRequired();

        this.Property(m => m.LastName)
            .HasColumnName("SPOUSE_LAST_NAME")
            .HasMaxLength(60)
            .IsRequired();

        this.Property(m => m.MiddleName)
            .HasColumnName("SPOUSE_MIDDLE_NAME")
            .HasMaxLength(60)
            .IsRequired();

        // Relationships
        this.HasRequired(m => m.Person)
            .WithRequiredDependent(m => m.PersonSpouse);
    }
}

public class PhoneMap : ExtendedEntityTypeConfiguration<Phone>
{
    public PhoneMap()
    {
        // Primary key
        this.HasKey(m => m.Id);

        // Table & Column Mappings
        this.ToTable("PHONES", "EGO_TEST");

        this.Property(m => m.Id)
            .HasColumnName("ID")
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
            .IsRequired();

        this.Property(m => m.Prefix)
            .HasColumnName("PREFIX")
            .HasMaxLength(5)
            .IsRequired();

        this.Property(m => m.Digits)
            .HasColumnName("DIGITS")
            .HasMaxLength(12)
            .IsRequired();

        // Extended Configurations
        // Unique Key
        this.HasUniqueKey(m => new {m.Digits, m.Prefix });
    }
}

public class PersonPhoneMap : ExtendedEntityTypeConfiguration<PersonPhone>
{
    public PersonPhoneMap()
    {
        // Primary Key
        this.HasKey(m => new { m.PersonId, m.PhoneId });

        // Table & Column Mappings
        this.ToTable("PERSON_PHONES", "EGO_TEST");

        this.Property(m => m.PersonId)
            .HasColumnName("PERSON_ID");

        this.Property(m => m.PhoneId)
            .HasColumnName("PHONE_ID");

        // Relationships
        this.HasRequired(m => m.Phone)
            .WithMany()
            .HasForeignKey(m => m.PhoneId);

        this.HasRequired(m => m.Person)
            .WithMany(m => m.PersonPhones)
            .HasForeignKey(m => m.PersonId);
    }
}

Step 3 - Let's Create Our DbContext Class

As we finished with our models and their mapping, now we can create our DbContext class. I have added two different connection strings in the App.config file. You can use any of them, depending on your RDBMS.

<connectionStrings>
   <add name="EgoDbConnectionOracle" providerName="Oracle.ManagedDataAccess.Client"
     connectionString="data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
     (HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));password=123;
     persist security info=True;user id=EGO_TEST;"/>

   <add name="EgoDbConnectionMsSqlServer" providerName="System.Data.SqlClient"
     connectionString="data source=FJABIYEV-PC\SQLEXPRESS;initial catalog=EGO_TEST;
     integrated security=True;pooling=False;MultipleActiveResultSets=True;App=EntityFramework"/>
 </connectionStrings>

And here is our DbContext class. There is nothing special:

public class EgoDbContext : DbContext
{
    public EgoDbContext()
        : base("name=EgoDbConnectionMsSqlServer") // connection string name, 
                                // change it to EgoDbConnectionOracle if you are using Oracle.
    {
        this.Configuration.ProxyCreationEnabled = true;
        this.Configuration.LazyLoadingEnabled = true;
        this.Configuration.ValidateOnSaveEnabled = true;
        this.Configuration.AutoDetectChangesEnabled = false;
    }

    static EgoDbContext()
    {
       // Uncomment this, if you are using Oracle
       // Database.SetInitializer<EgoDbContext>(null); 
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Dynamically get all mapping classes.
        var typesToRegister = Assembly.GetExecutingAssembly()
            .GetTypes()
            .Where(type => !String.IsNullOrEmpty(type.Namespace))
            .Where(type => type.BaseType != null && type.BaseType.IsGenericType)
            .Where(type => type.BaseType.GetGenericTypeDefinition() == 
                                    typeof(ExtendedEntityTypeConfiguration<>))
            .Where(type => !type.ContainsGenericParameters);

        // Register mapping classes.
        foreach (var type in typesToRegister)
        {
            dynamic configurationInstance = Activator.CreateInstance(type);
            modelBuilder.Configurations.Add(configurationInstance);
        }

        base.OnModelCreating(modelBuilder);
    }   
}

Step 4 - Main Showdown

That's all. Now, we can test if this API works as I promised you. :)

Add this sample code to the Main method in the Program.cs and run the application.

#region Let's create a Person entity and check if all datas are inserted correctly.
using (EgoDbContext context = new EgoDbContext())
{
    // Create person entity and set it's properties.
    Person person = new Person()
    {
        FirstName = "Farhad",
        LastName = "Jabiyev",
        MiddleName = "John",
        Age = 25,
        Document = new Document
        {
            DocumentNumber = "11112222",
            DocumentSeries = "POL"
        },
        PersonSpouse = new PersonSpouse
        {
            FirstName = "Kate",
            MiddleName = "Tina",
            LastName = "Jabiyev"
        }
    };

    person.PersonPhones = new List<PersonPhone>
    {
        new PersonPhone { Phone = new Phone { Digits="57142", Prefix="14"} },
        new PersonPhone { Phone = new Phone { Digits="57784478", Prefix="50"} }
    };

    context.InsertOrUpdateGraph(person);

    context.SaveChanges();
}
#endregion

All entities will be added to the database. Please go and check if everything works as it must.

Now, the states of all entities were added because, our database was empty. So, let's comment this region and make some changes and re-run the application:

#region Now, let's update a Person entity and check if all datas are affected correctly.
using (EgoDbContext context = new EgoDbContext())
{
    Person person = new Person()
    {
        Id = 1,
        FirstName = "New Farhad", // Name has been changed - It must be updated
        LastName = "Jabiyev",
        MiddleName = "John",
        Age = 26, // Age has been changed - It must be updated
        Document = new Document
        {
            DocumentNumber = "11112222",
            DocumentSeries = "USA"
            // Series has been changed, so new Document entity will be inserted and 
            // DocumentId property will me modified in the Person entity
        },
        // Spouse does not exist anymore - It must be deleted
        //PersonSpouse = new PersonSpouse 
        //{
        //    FirstName = "Kate",
        //    MiddleName = "Tina",
        //    LastName = "Jabiyev"
        //}
    };

    person.PersonPhones = new List<PersonPhone>
    {
        // new PersonPhone { Phone = new Phone { Digits="57142", Prefix="14"} }, // It must be deleted
        new PersonPhone { Phone = new Phone 
                   { Id = 2, Digits="68745877", Prefix="50"} }, // It must be updated
        new PersonPhone { Phone = new Phone { Digits="66658", Prefix="14"} }, // It must be inserted
    };

    context.InsertOrUpdateGraph(person)
        .After(entity =>
        {
            // Delete missing phones.
            entity.HasCollection(p => p.PersonPhones)
               .DeleteMissingEntities();

            // Delete if spouse is not exist anymore.
            entity.HasNavigationalProperty(m => m.PersonSpouse)
                  .DeleteIfNull();
        });

    context.SaveChanges();
}
#endregion

Please run the application. The API will execute these queries again to the database:

• Update First Name and Age of the person
• Delete spouse from the table
• Insert new document to the table
• Update Document Id of the Person entity according to the newly inserted Document
• Delete those PersonPhones from the table which do not exist in the collection
• Update those Phones which have primary key and different digits/prefix pair

Isn't that nice?

I am waiting for your feedback.