CODEWITHSUNDEEP
c#mysqlasp.net-web-apiwebapi
Filip Z
Filip Z

Reputation: 79

C# - Web API (Foreign Key)

I have a database with two tables - users, chatrooms. My chatrooms table looks like this: enter image description here

The created_by column has a foreign key that connects it to the Users table primary key (id int(11))

User table: enter image description here (Don't mind the password, it is just a prototype)

Now I want to do something like this:

    public class Chatroom
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }

        public User Created_by { get; set; }

        public Chatroom(int id, string name, string description, User created_by)
        {
            Id = id;
            Name = name;
            Description = description;
            Created_by = created_by;
        }
    }

And the ApiController:

    MySqlCommand command = new("SELECT id, name, description, created_by FROM chatrooms", connection);
    MySqlDataReader reader = command.ExecuteReader();

    while (reader.Read())
    {
        results.Add(new Chatroom(
            (int)reader["Id"],
            reader["Name"].ToString(),
            reader["Description"].ToString(),
            (User)reader["created_by"]
        ));
    }

Now this obviously does not work, as created_by is of type Int32, not User, and the user's id is stored there, not the User himself. So my question is, how would I go about accessing the user itself. Thank you.

Upvotes: 0

Views: 277

Answers (3)

Yuri Morales
Yuri Morales

Reputation: 2498

Why not using EF?

var chatroom = myDataContext.Chatrooms.Include(i => i.Created_by).ToList();

That's it.

Upvotes: 1

David
David

Reputation: 218827

I can think of three options...

  1. Use an ORM. I don't have specific recommendations for one that works with MySQL, but something is likely to exist somewhere. Maybe something like Dapper? But the general idea is that you use a library which internally maps between your objects and your queries.

  2. If you stick with simple ADO-style queries like you have, then you can simply issue a second query which uses your created_by value to get the relevant User instance from data.

  3. You could potentially combine these queries into a single query by including a JOIN in the query you have. (You may want to include an ORDER BY so records are returned in the same order of users, as that will make the rest of the logic simpler.) Then in your loop you would write some more complex logic to construct your objects. Perhaps also having a collection of User objects to which you conditionally add and then each Chatroom object in the final results can reference an object in that collection based on its created_by value.

Upvotes: 1

Jahan Afshari
Jahan Afshari

Reputation: 109

You need to change your select statement to get the data for the user who the created the chat room from the Users table:

SELECT id, name, description, created_by, users.Name, users.Surname, users.Email, users.Password FROM 
            chatrooms, users
            WHERE users.id = created_by

Then you can get the columns that have the user data using the reader variable like you do with the chatroom data.

Upvotes: 1

Related Questions