How to handle Many-to-Many Relationship in EF Core

There are a few steps that are instrumental in handling a Many-to-Many relationship in EF Core. It must be noted that EF Core 1.1 does not support Many-to-Many relationship right off the bat. However, with a junction table in hand, you can achieve this in just a few easy steps. Here’s how:

Let’s write a couple of POCO classes first. Below are the two main classes, namely Department and Employee respectively. Let’s just assume the relationship between the two is Many-to-Many.

    class Department
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string Location { get; set; }

        public List DepartmentEmployees { get; set; }
    }
    class Employee
    {
        public int ID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int Age { get; set; }

        public List DepartmentEmployees  { get; set; }
    }

As indicated above; in order for it work, we need a join table such that it can contain foreign keys of Department and Employee Classes like so:

class DepartmentEmployee
    {
        public int DepartmentID { get; set; }
        public int EmployeeID { get; set; }

        //Navigation Properties
        public Department Department { get; set; }
        public Employee Employee { get; set; }
    }

Now, add a class which is to be derived from DbContext. For this, ensure that you have the necessary packages installed which are the following:

  1. Microsoft.EntityFrameworkCore.SqlServer
  2. Microsoft.EntityFrameworkCore.SqlServer.Design
  3. Microsoft.EntityFrameworkCore.Tools

Please note, I have installed  SqlServer.Design package only because, I am using SQL Server. I were using DB2, I would need to install the DB2.Design package and so on.

class DepartmentContext : DbContext
    {
        public DbSet Department { get; set; }
        public DbSet Employee { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"server=obi-oberoi; database=demo.ManyToMany; trusted_connection=true;");
        }
    }

Now, let’s try and add our first migration using the Package Manager Console window and see what we get:
PM>add-migration initial

InitialMigrationNotice, the message that says “The entity type “DepartmentEmployee” requires a primary key to be defined”. This is because EF Core could not discover a primary key in the DeparmentEmployee model even though they are defined in the class.

One thing to keep in mind though is that in order for a Join table to work, you need composite primary key(s). However, the creation of a composite key cannot be achieved simply through Data Annotations as it only provides a scaled down version of Fluent API. For that you need a Fluent API.

In order to resolve this, let’s override the OnModelCreating() method in our context and configure the ModelBuilder API that’ll define the shape of our entity, the relationship and how it maps to the database.

Given that it’s a composite key, it’s made up of the DepartmentID and EmployeeID.

    class DepartmentContext : DbContext
    {
        public DbSet Department { get; set; }
        public DbSet Employee { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"server=obi-oberoi; database=demo.ManyToMany; trusted_connection=true;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity()
                .HasKey(d => new { d.DepartmentID, d.EmployeeID });
        }
    }

Now, let’s try adding a migration.CompositeKeyand it worked!!!

Posted in ORM | Comments Off on How to handle Many-to-Many Relationship in EF Core

How to Build Trillion $ Opportunities & Solve the Grand Challenges of Humanity!

This is pretty much eleventh hour; and with a bunch of tickets on hand, I thought of making this final pitch to entice like-minded people to come and attend this once in a life time event.

This short seminar of 60 – 75 minutes could potentially change ones life and pave the road to ultimate Success!

WadhwaBlogUse this link to register for a complimentary seat.

I look forward to seeing  you on May 15th at 5:30PM at Microsoft Canada Headquarters.

Obi

Posted in News | Comments Off on How to Build Trillion $ Opportunities & Solve the Grand Challenges of Humanity!

Why is EF Core so blazing fast than its Predesessor?

It’s worth noting that EF Core is a brand new shiny tool from Microsoft that has been built from the ground up. It is lightweight, extensible and it comes with a plethora of new features when compared with EF 6.x.

Now, when it comes to performance, in just about all possible scenarios where tests have been conducted on CRUD related operations, EF Core has invariably outperformed EF 6.x. This is despite EF 6.x code having been tweaked for maximum optimization against somewhat naïve EF Core code, and yet coming out as a winner. So this begs the question…why is EF Core so much faster?

Well, the answer lies in the following:

  • Caching
  • Batching
  • Faster Initialization to build object models (conceptual model, mappings, storage etc.)

Stay tuned for more!!

Posted in ORM | Comments Off on Why is EF Core so blazing fast than its Predesessor?

EF 6.x or EF Core? I am confused!!!

If you are an enterprise developer or you are building a Line of Business Application and targeting the full .NET Framework and who also cares about visual tooling, EF 6.x is the way to go. It also means you can do WPF, Win Forms, ASP.NET MVC, Console apps etc.

EF 6.x is a mature product that is built over a period of time starting from 2008 till now. During this period, we have seen continuous improvements by way to adding new features, bug fixing etc. It has a rich eco-system of database providers and provides at least four workflows to build a model and database. It includes the following:

  • Code First
  • Code First From Database
  • Database First (EDMX Designer)
  • Entity First (Visual EDMX Designer)

EF Core on the other hand can target a multitude of application models ranging from full .NET Framework, ASP.NET Core as well as Xamarin. So if your dominant concern is to deploy your application on devices such as iOS, Android and such, EF Core is probably the way to go!

Apart from being cross-platform, EF Core is also a lightweight version of Entity Framework and is therefore built on a more leaner and faster development stack. Its because it has been built from ground up and is built over a set of services which make up the core of .NET.

Not only new platforms and new devices, EF Core has been architected to target new data stores. These data stores can be both relational and non-relational. Some of the examples of database providers are the following:

  • Azure Table Storage
  • Reds
  • In Memory (functional testing)
  • Relational providers such as:
    • SQL Server
    • SQLite
    • SQL Compact
    • Postgres
    • Oracle (prototyped)
    • DB2

more to come…

Posted in ORM | Comments Off on EF 6.x or EF Core? I am confused!!!

Can’t get too far without Transactions support. Guess what? EF Core’s got your Back!!

A transaction symbolizes a unit of work performed within a database management system against a database, and treated in a coherent and reliable way independent of other transactions. A transaction generally represents any change in database.

If the transaction is committed, all of the operations are successfully applied to the database. If the transaction is rolled back, none of the operations are applied to the database.

The default behavior of a transaction is if the database provider supports transactions, all changes in a single call to SaveChanges() are materialized in a transaction. However, if any one of the changes fails, then the transaction is rolled back without any change(s) being materialized to the database. What this means is that SaveChanges() is guaranteed to either completely succeed, or leave the database in its original state in the event of a failure.

Let’s see this in action, but first, let’s see what we have in our database table called Departments before we call our method.

BeforeUpdateDelete

See the code snippet below. Notice SaveChanges() operation is being used twice. However, LINQ query is executed only once.

      private static void TransactionUpdateDelete()
        {
            using (var db = new DepartmentContext())
            {
                using (var trx = db.Database.BeginTransaction())
                {
                    int depId = 15;
                    var depts = db.Departments.Find(depId);
                    depts.location = "New Delhi";

                    //Delete a record
                    db.Database.ExecuteSqlCommand("delete from dbo.Departments where depId = 16");
                    db.SaveChanges();

                    var deptList = db.Departments.ToList();

                    foreach (var item in deptList)
                    {
                        Console.WriteLine(item.depName, item.depName, item.location);
                    }
                    Console.ReadLine();
                    trx.Commit();
                }
            }
        }

Snapshot of Data View After the Commit(). Notice DepartmentID 15 is updated from New York to New Delhi and DepartmentID 16 is yanked out as a result of the Deletion

AfterUpdateDelete

And lastly, the output window to demonstrate the successful execution of the two operations, namely, Update and Delete within the context of a Transaction in EF Core.

Output

Posted in ORM | Comments Off on Can’t get too far without Transactions support. Guess what? EF Core’s got your Back!!