How to display data from more than one table to my view using MVP pattern in C# winforms?

Question

How to display data from more than one table to my view using MVP pattern in C# winforms?

Say I have the following tables:

1. Fruit

2. Color

   Table definition:  
   +----------+  
   | Fruit    |  
   +----------+  
   | Id       |  
   | Name     |  
   | Color_Id |  
   +----------+  
        |∞
        |
        |1
   +----------+  
   | Color    |  
   +----------+  
   | Id       |  
   | Name     |  
   +----------+  
   
   Table contents:  
   
   Fruits  
   +-----+--------+----------+  
   | Id  | Name   | Color_Id |  
   +-----+--------+----------+  
   | 10  | Apple  | 70       |  
   | 20  | Orange | 80       |  
   | 30  | Grapes | 90       |  
   +-----+--------+----------+  
   
   Colors  
   +-----+--------+  
   | Id  | Name   |  
   +-----+--------+  
   | 70  | Red    |  
   | 80  | Orange |  
   | 90  | Violet |  
   +-----+--------+  
   

Then in my solution I have created classes for each tables namely, Fruit and Color class, having their own getters and setters and other details representing the tables in the database.

Then I also created two models for Fruit and Color.

So if I wanted to create a view that will display a record about Color, I would use the model I created for Color to retrieve a collection of colors (e.g. List<Color>). If I'm using a DataGridView to display that record, I would get something similar like the following:

    +-----+--------+  
    | Id  | Name   |  
    +-----+--------+  
    | 70  | Red    |  
    | 80  | Orange |  
    | 90  | Violet |  
    +-----+--------+  

For the Fruit records, I could use the same method above but I need to display the color name instead of the color id like the following: (Assuming I'm using a DataGridView or a custom control)

    +-----+--------+----------+  
    | Id  | Name   | Color    |  
    +-----+--------+----------+  
    | 10  | Apple  | Red      |  
    | 20  | Orange | Orange   |  
    | 30  | Grapes | Violet   |  
    +-----+--------+----------+  

I figured I could create another class for it and name it 'FruitColor' then give it properties same as the Fruit class above but instead of having a color id property, I'd replace it with a color name instead... But I'm not really sure if it's the right way of doings thing in MVP. I thought I should ask you guys for any tips on how to do it right...

I'm not using ORM's for my project because I want to understand how do it manually...But I would also appreciate any help related to using ORM's. I also just started learning about MVP 3 weeks ago so I'm really hoping for any help.

Answer 1

When retrieving Fruit, you should query the colours with them using a JOIN in SQL:

RETRIEVE f.*, c.name as color_name
FROM Fruits f
INNER JOIN Colors c
ON f.Color_Id = c.Id

Then, transform the results you get into Fruit as you're probably already doing. This would, however, require you to expand your Fruit class so it would either have a string property for the color name variable, or just add a property that contains the color as a whole:

public class Fruit
{
   public int Id { get; set; }
   public string Name { get; set; }
   public Color Color { get; set; }
}