I made some time in the last week to get started on an app I’ve been planning to make for a while, and the first stage of creating it involved following an EF tutorial that didn’t go quite as planned.

The App: ClientScheduler

The app I’m starting to make would let my wife keep track of her clients and work schedule. She works for several different agencies, with several clients in each agency, so it presented an opportunity to learn. My very first stage of this involves an “app” that only does a few things:

  1. Create a list of Agencies, along with their location
  2. Create a list of Clients
  3. Assign Clients to an Agency

Everything else I want comes from having this functionality first, so this is a good starting point.

The EF Tutorial I Followed

Getting Started with Entity Framework 6 Code First using MVC 5 (ASP.NET, by Tom Dykstra)

This is the “ContosoUniversity” web app tutorial, which creates a sort of school course, student and instructor registration site. I decided to follow along with the tutorial and apply their steps to my app in their order.

A Summary of How to Create a Database in EF Code First

From the tutorials I’ve read, here’s how I’ve been creating databases using EF Code First:

Create a DB in EF

 Defining The Models and Their Properties

Once I’ve drawn out on paper what the app needs to do, I draw out the models and the properties I need. As stated above, here’s what my app will do:

keep track of her clients and work schedule. She works for several different agencies, with several clients in each agency

I can’t create a schedule without a list of clients, and I want clients tied to agencies, so I’ll start with two model class files: Agency.cs and Client.cs.

Here’s the Agency Model:

public class Agency
    {
        public int ID { get; set; }
        public string AgencyName { get; set; }
        public string City { get; set; }
        public string State { get; set; }


        public virtual ICollection<Client> Clients { get; set; } 
    }

Here’s the Client model:

public class Client
    {
        public int ID { get; set; }

        public string LastName { get; set; }
        public string FirstName { get; set; }

        // Can specify how the name should be shown on-screen
        public string Alias { get; set; }
        public string AgencyID { get; set; }

        // Holds the info relating to the agency this client is part of
        public virtual ICollection<Agency> Agencies { get; set; } 
    }
The Problem With These Classes

There were two issues I had with these classes. The first was more obscure to me.

I defined these models as a many-to-many relationship: Agencies can have multiple Clients, and Clients can belong to multiple Agencies. I figured it would work just fine, and to a point it did: I created the Agency and Client controllers with their associated views, and EF didn’t give me any errors.

Problem was, after creating a few Agencies, I couldn’t get the Index view for the Clients table to access the list of Agencies.

It hit me that the tutorial held three tables, not two:

15-Original-Entities_thumb.png

I missed a fundamental part of my database learning: Many-to-Many relationships aren’t really defined directly; a third table references both of them. So what I seemed to have done was define two tables without a proper way for them to interact.

I was wrong there, too: Turns out that EF creates a third table behind-the-scenes that holds the primary keys to both tables.

I was unable to figure out how to use this to properly reference the list of Agency Names from within the Client list. Then I realized that I really don’t need to have clients assigned to more than one agency, so let’s just rewrite the Client.cs model to be a one-to-many relationship instead of many-to-many.

Revised Client.cs Class:

public class Client
    {
        public int ID { get; set; }

        public string LastName { get; set; }
        public string FirstName { get; set; }

        // Can specify how the name should be shown on-screen
        public string Alias { get; set; }
        public string AgencyID { get; set; }

        // Holds the info relating to the agency this client is part of
        public virtual Agency Agency { get; set; }
    }
Entity Framework Migrations, And Realizing The Dumb Mistake

I used the following tutorial to perform a code-first migration on the database to update it, which is necessary any time the models used to work with the database change:

Code First Migrations and Deployment with the Entity Framework in an ASP.NET MVC Application

After further testing I found that I could populate a dropdown box in the Client “Create” view with the list of Agencies, but when I used it to assign a new Client to an Agency, then went back to the Index View, the Clients were listed with blank entries for Agency.

I connected to the database from within Visual Studio to check the data, and it did assign the client the correct AgencyID, but I found something odd:

1 - Agency ID really did write correctly but its not showing

The “AgencyID” field is the dropdown; that matches the selected Agency when I created the client. But why is there a separate Agency_ID field showing with a totally different number? No wonder nothing’s showing up.

Then I realized the dumb mistake in Client.cs:

public string AgencyID { get; set; }

ID fields need to be defined as INT, not STRING. Could that be all I had to do? Sure enough, I change it to an INT, execute another code migration to update the database, re-insert data and viola! It works.

2 - Correct database setup

No more separate “Agency_ID” field — the AgencyID field in Client.cs now works like the Foreign Key it is.

Webpage showing correct function

 

Takeaways
  • I still need to research a simple way to access properties of one class from another in a many-to-many relationship.
  • Double-check how my properties are defined; ID and foreign key fields should be defined as an INT.
  • I should consider moving the info on how I use EF to another post. Maybe that will be my next post.