0

I need to store data in the DB that is related to two models that have a many-to-many relationship.

Each Client can have many Contacts and each contact can have many Clients. Each contact has a priority at each client. I made a model with a composite key composed of a Client ID and a Contact ID that has a Priority property, but it's a pain to use. For instance, I don't know a good way to order a client's contacts by priority.

So using it looks like this: 

MyDbContext db = new MyDbContext();
ClientModel client = db.Clients.Find(clientId);
ContactModel contact = db.Contacts.Find(contactId);
ContactPriorityModel priorityModel = db.ContactPriorities.Find(clientId, contactId);
int priority = priorityModel.Priority;

When ContactModel only had one related ClientModel, ordering was as simple as client.Contacts.OrderBy(contact => contact.Priority), but changing this to a many-to-many relationship causes complications.

(Likely unnecessary) code: 

public class MyDbContext
{
    public DbSet<ClientModel> Clients { get; set; }
    public DbSet<ContactModel> Contacts { get; set; }
    public DbSet<ContactPriorityModel> ContactPriorities { get; set; }
}

public class ClientModel
{
    public int Id { get; set; }
    public virtual ICollection<ContactModel> Contacts { get; set; }
    // Other properties
}

public class ContactModel
{
    public string Id { get; set; }
    public virtual ICollection<ClientModel> Clients { get; set; }
    // Other properties
}

public class ContactPriorityModel
{
    [Key, Column(Order = 0)]
    public int ClientId { get; set; }

    [Key, Column(Order = 1)]
    public string ContactId { get; set; }

    public int Priority { get; set; }
}
Sinjai
  • 1,085
  • 1
  • 15
  • 34
  • Slightly off topic... are Contacts people (phone numbers, etc), and Clients other people/businesses that want to contact those people? Because it looks like you’re sharing contacts between clients, and I’m hoping that’s not the case because then it’d be possible for each client to have their contact’s data update by another client (whom they presumably don’t know). – cwharris Jun 08 '18 at 17:27
  • You want to arrange contacts for a particular client based on priority. Try this: `db.ContactPriorities.Where(x => x.ClientId = clientId).OrderBy(x => x.Priority) ` – Ajay Gupta Jun 08 '18 at 17:29
  • @cwharris Contacts are people who work at/for a Client. The background on this is somewhat complicated, but yes, clients do share contacts sometimes. In this case, the contact is the same person (identified by email, typically), so their name, email, etc. is the same at each place. In the event that, say, their phone number ends up differing from client to client, I would probably move that column to the intermediate table along with `Priority`. If you think this sounds janky, I agree: that's why I'm here. – Sinjai Jun 08 '18 at 17:43
  • @AjayGupta Right, but then that leaves me with a bunch of contact IDs. So then I have to loop through that enumerable, call `db.Contacts.Find(id)` on each of them before I *finally* end up with an ordered list of `ContactModel`s. – Sinjai Jun 08 '18 at 17:46
  • I would assert that, unless there is a very good reason to do otherwise, that data-wise, clients *do not share contact data*, even if they represent the same real-world person. “Mike” may have two different phone numbers he uses, one for Client A, and one for Client B, even though his name and email address may be different. It smells like a GDPR violation in the making. – cwharris Jun 08 '18 at 17:47
  • @cwharris The reason is that contacts are really user accounts, and accounts are identified by email. If a contact logs in and is assigned to more than one client, he will be prompted to choose the client he's concerned with at the time. – Sinjai Jun 08 '18 at 17:50
  • Got it. I’ve been working in a different sort of environment involving a lot of the same terminology, but it’s a pseudo-multi-tenancy environment, so, for me, keeping contact information separate for different clients is very important. It sounds like in your case the contact is internal to your company, and clients represent your companies clients. Therefore there is only one legal entity using the information: your company. – cwharris Jun 08 '18 at 17:56
  • Not sure I understand the issue. Add inverse navigation properties (e.g. `public virtual ClientModel Client { get; set; }` and `public virtual ContactModel Contact { get; set; }`) to `ContactPriorityModel` entity and you'll have access to client contacts. – Ivan Stoev Jun 08 '18 at 17:58
  • @IvanStoev I'd have to add `ICollection` properties to `ClientModel` and `ContactModel` too though, right? My issue is foremost that I'm not sure this is even the correct approach, and second that I'm having trouble visualizing getting a priority-sorted list of `ContactModel`s from a `ClientModel` using navigation properties. – Sinjai Jun 08 '18 at 18:05
  • @cwharris Contacts are not internal to the company, but I'm not seeing the apparent information leak you see. Help me see the light? – Sinjai Jun 08 '18 at 18:06
  • Indeed. To be precise, not add, but actually change your current collection properties. That's the only way to implement many-to-many with additional column in the link table. – Ivan Stoev Jun 08 '18 at 18:09
  • @IvanStoev Ohhh, that's the *intermediate* in *intermediate table* (I think) -- my order was wrong. So if I renamed the intermediate model to, say, `ContactInfo`, I'd get an ordered "list" of `ContactModel`s like this? `client.ContactInfos.OrderBy(ci => ci.Priority).Select(ci => ci.Contact)` <> Bonus points if you can think of a better name for the data model, given that it may well eventually contain more than just `Priority`... – Sinjai Jun 08 '18 at 18:26
  • @IvanStoev Also, do I keep the composite key? – Sinjai Jun 08 '18 at 18:34
  • Not strongly necessary, but why not? – Ivan Stoev Jun 08 '18 at 18:46
  • @IvanStoev I'm not sure, honestly. You just clearly know more than I do. A composite key seems to be the best way to identify the model, but I'm flying by the seat of my pants here. The code in my first comment looks good? Feel free to add this stuff as an answer. – Sinjai Jun 08 '18 at 18:57
  • Composite key is ok for me. As well as the code from your comment :) Glad to being helpful, but I'm almost sure it's a duplicate. You can post self answer if you wish. Happy coding! – Ivan Stoev Jun 08 '18 at 19:12
  • 2
    Yep, [duplicate](https://stackoverflow.com/a/7053393/861716). – Gert Arnold Jun 08 '18 at 20:29
  • @IvanStoev The code above, `client.ContactInfos.OrderBy(ci => ci.Priority).Select(ci => ci.Contact)`, does not work. It goes through each `ContactInfo`, but each is projected to `null`. So if the client has 2 contacts, the above code yields an enumerable with 2 null references. Thoughts? The database has all the correct information and the navigation properties are set up [as usual](https://hastebin.com/oriyogaheq.cs). – Sinjai Jun 25 '18 at 03:55
  • When I said correct, I meant it to be a part of a query, e.g. `db.Clients.Where(…).Select(client => …)`. If the `client` is already materialized object, make sure to eager load the related data when retrieving it, e.g. `db.Clients.Include(c => c.ContactInfos).ThenIncude(ci => ci.Contact).FirstOrDefault(c => c.Id == clientId)`. – Ivan Stoev Jun 25 '18 at 05:55
  • @IvanStoev Yeah, I'm passing a `Client` to a viewmodel's constructor and using the aforementioned object's navigation properties, as above. Works great, usually. This is the first time I've had a problem. Do I need to eagerly load the first collection, something like `client.OrderBy(ci => ci.Priority).ToList().Select...`? – Sinjai Jun 25 '18 at 06:03
  • Possible duplicate of [Create code first, many to many, with additional fields in association table](https://stackoverflow.com/questions/7050404/create-code-first-many-to-many-with-additional-fields-in-association-table) – Sinjai Jan 23 '19 at 18:17

0 Answers0