Combine 5 tables in c# without relationship

Question

Need your help with a quite easy sql question i guess (but cant figure it out!)!

I am working with an MVC Project in asp.net and have 5 tables in my database i want to add upp and present in a single view

Each table is data about employee at a certain section att my job. like this:

HR(tabel1)

id       FirstName     Lastname       phoneNumber
1          Mike          Ashton        xxxxxxxx
2          John          Keegan        xxxxxxxx

Economy(tabel2)

id       FirstName     Lastname       phoneNumber
1          Lisa          Ek            xxxxxxxx
2          John          Alm           xxxxxxxx

.
IT (table3)
Logistic(table4)
Support(table5)

So the table dont have any relationship and i just want to display the data within each table under each other on a single view page(employee) like this:

id       FirstName     Lastname       phoneNumber
1          Mike          Ashton        xxxxxxxx
2          John          Keegan        xxxxxxxx
3          Lisa          Ek            xxxxxxxx
4          John          Alm           xxxxxxxx
.
.

So in my controller i need to join these 5 tables somehow but cant figure it out how to do it, anyone can help me?

/ Daniel

How about you create one view model and add these five tables as five different properties in it. No need to combine them if there is no relation. — Sahil Sharma, Nov 06 '18 at 07:16
So you're saying you want to _union_ a number of tables? Can I ask why you have 5 separate tables, and not an "Employees" table with a "Section" field? With the right indexes, it would likely be more efficient than what you want to do now. — ProgrammingLlama, Nov 06 '18 at 07:17
Sounds like OP uses manual sharding or partitioning. You can create one table and split by partitions based on department field. https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-2017 — Access Denied, Nov 06 '18 at 07:21
Agree with the others. You only need 1 table with the users and there Id and a field for the section.(But I would implement a table for the section, to join with the id and not the label, in order to be able to edit it easily) — Manta, Nov 06 '18 at 08:18
Sure, one table would be better. But maybe this is the model OP *has* to work with. Changing a model is not always an option. UNION would be a good approach. — nilsK, Nov 06 '18 at 08:48

score 0 · Accepted Answer · answered Nov 06 '18 at 10:26

In order to resolve you could create a ViewModel (as suggested by Sahil Sharma).

public class MyViewModel
{
    public int Id {get; set;} 
    public string FirstName{get; set;} 
    public string LastName{get; set;} 
    public string PhoneNumber{get; set;} 
}

In your controller, create a new instance of your ViewModel and use the data from those 5 tables. The below code will fill the combinedTablesList with data from the HR table1.

List<MyViewModel> combinedTablesList = new List<MyViewModel>();
MyViewModel myViewModel = new MyViewModel();
int id = 1;

//select all your HR values as a list from your database repository.
List<HR> table1 = yourRepository.HR().toList();

foreach(var item in table1)
{
    myViewModel.Id = id;
    myViewModel.FirstName = item.FirstName;
    myViewModel.LastName = item.LastName ;
    myViewModel.PhoneNumber= item.PhoneNumber;
    combinedTablesList.add(myViewModel);
    id++;
}

If your 5 tables do extend a base class you could implement the foreach loop in a method with the following signature(avoid code repetition):

public void CombineTables(List<baseClass> myTable, List<MyViewModel> tableList);

And call it as following:

List<MyViewModel> combinedTablesList = new List<MyViewModel>();
MyViewModel myViewModel = new MyViewModel();

int id = 1;

//select all your HR values as a list from your database repository.
List<HR> table1 = yourRepository.HR().toList();
//select all your Economy values as a list from your database repository.
List<Economy> table2 = yourRepository.Economy().toList();

CombineTables(table1, combinedTablesList);
CombineTables(table2, combinedTablesList);

Hope it helps.

Thx for all great help, The viewmodel solution made my day :-) — Daniel, Nov 06 '18 at 15:51

Combine 5 tables in c# without relationship

1 Answers1