Seeding a Many to Many With Entity Framework and C# Dynamic

Posted by Peter Kellner on November 11, 2015 · 4 mins read

In the previous post, we created a single table (speakers) with Entity Framework Code First from a JSON file with Visual Studio 2015.  Now we will add a sessions table that has a many to many relationship to the speakers table.  That is, speakers can have multiple sessions and sessions can have multiple speakers.  We do that but simply creating another entity (sessions) and add to it a List of speakers and then adding to the existing speakers table a list of sessions.  The new table (models) look as follows.

GitHub Repo: https://github.com/pkellner/EntityFrameworkSeedFromJSON

public class Session
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string DescriptionShort { get; set; }
    public string Description { get; set; }

    public virtual List Speakers { get; set; }
}

public class Speaker
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string ImageUrl { get; set; }
    public string WebSite { get; set; }
    public string Bio { get; set; }
    public bool AllowHtml { get; set; }
    public int PictureId { get; set; }

    public virtual List Sessions { get; set; }
}

Then,  we need to add sessions data (from the JSON file) but when we add the sessions, we need to add the speakers records associated with each session.  That is, we simply populate the List of sessions in the speaker model to make this happen.

One problem we ran into was that EF Code First really wants to control the Id of each record and since in our original data, the many to many relationship was maintained by original primary keys, we need to bring along the original speaker Id.  We do this by simply adding an extra column to the speaker model called PictureId and set that as an attribute (column) of the speaker table.  Then, we can use that to search the speakers table for the existing speakers.

The code below (and also in the GitHub repo) basically does this for us.  It again uses NewtonSoft.JSON as well as the dynamic type in c# to get the job done.

private void GetSessions(DbLocalContext context)
{
    var sessionJsonAll = 
        GetEmbeddedResourceAsString("EntityFrameworkSeedFromJSON.session.json");

    JArray jsonValSessions = JArray.Parse(sessionJsonAll) as JArray;
    dynamic sessionsData = jsonValSessions;
    foreach (dynamic session in sessionsData)
    {
        var sessionForAdd = new Session
        {
            Id = session.id,
            Description = session.description,
            DescriptionShort = session.descriptionShort,
            Title = session.title
        };

        var speakerPictureIds = new List();
        foreach (dynamic speaker in session.speakers)
        {
            dynamic pictureId = speaker.id;
            speakerPictureIds.Add((int)pictureId);
        }

        sessionForAdd.Speakers = new List();
        foreach (var speakerPictureId in speakerPictureIds)
        {
            var speakerForAdd = 
                context.Speakers.FirstOrDefault(a => a.PictureId == speakerPictureId);
            sessionForAdd.Speakers.Add(speakerForAdd);
        }

        context.Sessions.Add(sessionForAdd);
    }
}

To test the results, in Program.cs we put some simple loops and Console Write's to show.

static void Main(string[] args)
{
    using (var context = new DbLocalContext())
    {
        Console.WriteLine("---SESSIONS---");
        var sessions = context.Sessions.ToList();
        foreach (var session in sessions)
        {
            Console.WriteLine("Session: " + session.Title);
            foreach (var speaker in session.Speakers)
            {
                Console.WriteLine("---{0} {1} {2}",
                    speaker.FirstName, speaker.LastName, speaker.PictureId);
            }
        }
        Console.WriteLine("");
        Console.WriteLine("---SPEAKERS---");
        var speakers = context.Speakers.ToList();
        foreach (var speaker in speakers)
        {
            Console.WriteLine("{0} {1} {2}",
                speaker.FirstName, speaker.LastName, speaker.PictureId);
            foreach (var session in speaker.Sessions)
            {
                Console.WriteLine("---{0}",
                session.Title);
            }
        }

    }

}

HTH's