3 Replies - 354 Views - Last Post: 12 January 2020 - 04:43 AM Rate Topic: -----

#1 O'Niel   User is offline

  • D.I.C Regular

Reputation: 15
  • View blog
  • Posts: 493
  • Joined: 13-September 15

Seeding many-to-many relation EntityFrameworkCore

Posted 02 January 2020 - 03:34 AM

I have to make a small console application to store my contacts. Each contact belongs in a specific category. Some contacts are family, some friends, some colleagues,...
A contact can also have multiple categories (for example being a colleague and family).

I have already successfully implemented this using List-objects to save the data in memory. Now I need to rewrite my application to have the data saved in a Sqlite database using Dbset, FluentAPI, and EntityFrameworkCore. But I encounter some troubles understanding how to make use of database-relations in C# using these frameworks.

Consider the following three objects:

**Contact**
        public class Contact : IValidatableObject {
    
            [Key]
            public int PersonId { get; set; }
    
            [Required]
            [StringLength(30)]
            public string Name { get; set; }
            public Address Address { get; set; }
            public Gender Gender { get; set; }
            public DateTime Birthday { get; set; }
            public string Phone { get; set; }
            public string Mobile { get; set; }
            public bool Blocked { get; set; }
            
            public List<Category> Categories { get; set; }
    
            public virtual IList<ContactCategory> ContactCategories { get; set; }
    
            public Contact() { Categories = new List<Category>(); }
    }


**Category**

      public class Category {
            
            public int CategoryId { get; set; }
            public string Description { get; set; }
            
            //[NotMapped]
            //public virtual List<Contact> Contacts { get; set; }
    
            public virtual IList<ContactCategory> ContactCategories { get; set; }
    
        }



**Address (Not important for this question)**
    public class Address {
        
        [NotMapped]
        public int AddressId { get; set; }
    
        [Required]
        [RegularExpression("^[a-zA-Z- ]+[0-9]+$")]
        public string StreetAndNumber { get; set; }
    
        [Range(1000, 9999)]
        public short Zipcode { get; set; }
        public string City { get; set; }
        public Contact Contact { get; set; }
    
    }



I had to create a Many-to-Many relationship between Contact and Category using EntityFrameworkCore and the Fluent API. So, I created a new class to link these two objects.

**ContactCategory**

    public class ContactCategory {
    
        public int ContactId { get; set; }
        public int CategoryId { get; set; }
    
        public Contact Contact { get; set; }
        public Category Category { get; set; }
    
        public ContactCategory() {}
    
        public ContactCategory(int contactId, int categoryId, Contact contact, Category category) {
            this.ContactId = contactId;
            this.CategoryId = categoryId;
            this.Contact = contact;
            this.Category = category;
        }
    
    }



I modelled this relation using the FluentAPI this way:

        //Many-to-Many relation Contacts <-> Category
        modelBuilder.Entity<ContactCategory>()
            .HasKey(cc => new {cc.ContactId, cc.CategoryId});

        modelBuilder.Entity<ContactCategory>()
            .HasOne(x => x.Contact)
            .WithMany(y => y.ContactCategories)
            .HasForeignKey(y => y.CategoryId);

        modelBuilder.Entity<ContactCategory>()
            .HasOne(x => x.Category)
            .WithMany(y => y.ContactCategories)
            .HasForeignKey(y => y.ContactId);



This code compiles. However, I don't know how to add a category to a contact.

When having the data in memory (no Sqlite database) I seeded my data this way:
        Contact c1 = new Contact();
        c1.PersonId = 1;
        c1.Name = "Verstraeten Micheline";
        Address a1 = new Address();
        a1.AddressId = 1;
        a1.StreetAndNumber = "Antwerpsestraat";
        a1.Zipcode = 2000;
        a1.City = "Antwerpen";
        a1.Contact = c1;
        c1.Address = a1;
        c1.Gender = Gender.male;
        c1.Birthday = new DateTime(1978, 8, 30);
        c1.Phone = "03/123.45.67";
        c1.Mobile = "0495/11.22.33";
        c1.Blocked = false;
        c1.Categories.Add(this.categories.Where(i => i.CategoryId == 1).FirstOrDefault());


(Note the last line). This won't work anymore because now I have to use a database thus this.categories (a List<Category>) doesn't exist anymore.

**Possible solutions I thought of:**
Doing the following:
        Category cat = new Category();
        cat.CategoryId = 1;
        cat.Description = "Family";
    c1.Categories.Add(cat);


Pretty sure this is wrong...

Another solution I thought of
was manually making a ContactCategory object each time a Contact needs to be created. And then linking the Contact to the Category by using their IDs... But also this isn't the correct way I guess...

The last approach seems logical, however even if that would be correct I'd have no idea on how to reference to a Category to fill in the property ContactCategory.Category;

I'm a little bit lost here...


**FINDING CONTACTS BY CATEGORY**

Now I also need to be able to filter my contacts by category (e.g filter on 'friends', 'family,...'). When doing this exercise in memory this was my approach:

        public IEnumerable<Contact> readAllContacts(int? categoryId = null) {
            if (categoryId != null) {
                List<Contact> contacts = new List<Contact>();

                foreach (Contact c in this.contacts) {
                    if (c.Categories.Contains(this.categories.Where(i => i.CategoryId == categoryId).FirstOrDefault())) {
                        contacts.Add(c);
                    }

                }

                return contacts;
            }

            return this.contacts;
        }


(Return all contacts, except if a categoryId is specified. If a id is specified, only return contacts matching that Category). Which worked perfectly in memory. However I have no clue on how to rewrite that if-statement to make it work with EntityFrameworkCore and the FluentAPI using my DbSets.

Is This A Good Question/Topic? 0
  • +

Replies To: Seeding many-to-many relation EntityFrameworkCore

#2 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 7244
  • View blog
  • Posts: 24,556
  • Joined: 05-May 12

Re: Seeding many-to-many relation EntityFrameworkCore

Posted 03 January 2020 - 12:32 AM

I don't use Entity Framework, but the answer is provided by a book author: Updating many to many relationships in Entity Framework Core

Based on my understanding, you don't want to have your Contact.Categories and Category.Contacts properties. Instead you rely on on your Contact.ContactCategories and Category.ContactCategories properties.

The seeding procedure within a single database context would be to instantiate your Contact and Category objects, then you instantiate ContactCategory objects with references to the Contact and Category objects. These ContactCategory objects are then added to your Contact.Categories. And then finally save the changes. I don't know if this will automatically populate the Category.ContactCategories within the same context, but unit test he shows implies that if you query for all the Category objects, it will return objects where the Category.ContactCategories will be populated.

Again, take this all with a huge grain of salt since I don't use EF.
Was This Post Helpful? 1
  • +
  • -

#3 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 7244
  • View blog
  • Posts: 24,556
  • Joined: 05-May 12

Re: Seeding many-to-many relation EntityFrameworkCore

Posted 03 January 2020 - 12:42 AM

As for reading all the contacts, I would assume that it would be something like:
IEnumerable<Contact> ReadAllContact(int? categoryId)
{
    if (categoryId.HasValue)
    {
        using (var db = new MyDbContext())
        {
            return db.ContactCategories
                     .Where(cc => cc.CategoryId == categoryId)
                     .ToList();
        }
    }

    using (var db = new MyDbContext())
        return db.Contacts.ToList();
}



Same disclaimer as above. I don't use EF.
Was This Post Helpful? 1
  • +
  • -

#4 O'Niel   User is offline

  • D.I.C Regular

Reputation: 15
  • View blog
  • Posts: 493
  • Joined: 13-September 15

Re: Seeding many-to-many relation EntityFrameworkCore

Posted 12 January 2020 - 04:43 AM

Thanks man! Your answer seems correct! It worked.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1