How to create a many-to-many relationship between users in a table?
-
I'm working on an ASP.NET Core web API and SQLite database. I have created a table for users of my application. The rule is that each user can send a message to some other users (creating White-List). I use the following model for Users:
public class UserModel
{
[Key]
public int UserID { get; set; }
[Required]
public string Username { get; set; }
[Required]
public string Password { get; set; }
[Required]
public string FirstName { get; set; }
[Required]
public string LastName { get; set; }
[Required]
public string Role { get; set; }//Navigation property public Department Department { get; set; }}
My desired table is:
User-SubUser table
UserID ReceiverUserID
23 11
42 11
19 -
34 23Note that the IDs mentioned above are the UserIDs from the User table. This table says that users 23 and 42 can send a message to user 11, user 34 can send a message to user 23, and user 19 cannot send message to anyone. How can I make a model for creating such a table?
-
I'm working on an ASP.NET Core web API and SQLite database. I have created a table for users of my application. The rule is that each user can send a message to some other users (creating White-List). I use the following model for Users:
public class UserModel
{
[Key]
public int UserID { get; set; }
[Required]
public string Username { get; set; }
[Required]
public string Password { get; set; }
[Required]
public string FirstName { get; set; }
[Required]
public string LastName { get; set; }
[Required]
public string Role { get; set; }//Navigation property public Department Department { get; set; }}
My desired table is:
User-SubUser table
UserID ReceiverUserID
23 11
42 11
19 -
34 23Note that the IDs mentioned above are the UserIDs from the User table. This table says that users 23 and 42 can send a message to user 11, user 34 can send a message to user 23, and user 19 cannot send message to anyone. How can I make a model for creating such a table?
Having "19" in the table is pointless and just complicates the situation. The simplest "model" is to simply join "Users" and "User-SubUser" when you need to; e.g. who can x send to (join on UserID), or who might x receive from (join on ReceiverUserID). If you want the (database) server to maintain "referential integrity", that's another story.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
-
Having "19" in the table is pointless and just complicates the situation. The simplest "model" is to simply join "Users" and "User-SubUser" when you need to; e.g. who can x send to (join on UserID), or who might x receive from (join on ReceiverUserID). If you want the (database) server to maintain "referential integrity", that's another story.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
Ok, Considering the final table only includes pairs (removing 19), how can I do joining using C# and EF Core?
-
Ok, Considering the final table only includes pairs (removing 19), how can I do joining using C# and EF Core?
Your "vehicle" in this case is LINQ. [Complex Query Operators - EF Core | Microsoft Docs](https://docs.microsoft.com/en-us/ef/core/querying/complex-query-operators)
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
-
Your "vehicle" in this case is LINQ. [Complex Query Operators - EF Core | Microsoft Docs](https://docs.microsoft.com/en-us/ef/core/querying/complex-query-operators)
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
I meant how I can create a UserSubUser table. I need a C# model to create this table (Code-First approach). UserIDs in the UserSubUser table should be linked to the UserModel table.
-
I meant how I can create a UserSubUser table. I need a C# model to create this table (Code-First approach). UserIDs in the UserSubUser table should be linked to the UserModel table.
No, they don't. You need to look up normalization, and apply dem rules to your database. If all that is required is whitelisting, then just have a table with users who are allowed to message each other. You think from C#; but the layer your working in is SQL, where you need to think SQL. Normalize them tables, work from there.
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
I'm working on an ASP.NET Core web API and SQLite database. I have created a table for users of my application. The rule is that each user can send a message to some other users (creating White-List). I use the following model for Users:
public class UserModel
{
[Key]
public int UserID { get; set; }
[Required]
public string Username { get; set; }
[Required]
public string Password { get; set; }
[Required]
public string FirstName { get; set; }
[Required]
public string LastName { get; set; }
[Required]
public string Role { get; set; }//Navigation property public Department Department { get; set; }}
My desired table is:
User-SubUser table
UserID ReceiverUserID
23 11
42 11
19 -
34 23Note that the IDs mentioned above are the UserIDs from the User table. This table says that users 23 and 42 can send a message to user 11, user 34 can send a message to user 23, and user 19 cannot send message to anyone. How can I make a model for creating such a table?
Alex Wright 2022 wrote:
public string Password { get; set; }
You appear to be storing your users' passwords in plain text. Don't do that! Secure Password Authentication Explained Simply[^] Salted Password Hashing - Doing it Right[^] ASP.NET already provides a built-in authentication system which does the right thing for you: Introduction to Identity on ASP.NET Core | Microsoft Learn[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer