CODEWITHSUNDEEP
c#database
Paul
Paul

Reputation: 281

C# Storing database table rows in Lists

I have a database with various tables and I use various SQL queries to extract the following:-

Table1

ID, Surname, Title, Department

1,   Jones,     Mr,    20

1,   Jones,     Mr,    21

A person can be a member of many departments. So the SQL query I do produces multiple rows of the same person (same ID) but with different department ID's. This information is stored in a list.

I can display this information without problem in a DataGridView object.

I also have another List for Departments, which I populate via a SQL query:-

ID,  Name

20,  Accounts

21,  Admin

What I am trying to achieve is to combine these two Lists so I end up with something like:-

ID, Surname, Title, Department

1,   Jones,     Mr,    Accounts, Admin

So, a new third List contains only 1 row per person ID but that row contains the department name not the department ID and also that row contains all of the departments that person is a member of.

Upvotes: 0

Views: 2031

Answers (1)

MarcinJuraszek
MarcinJuraszek

Reputation: 125620

I would suggest do the join in SQL, because it will be more efficient - that's what DataBase is designed to do!

However, if you really want you can use LINQ to Objects. I assumed you have two lists:

var people = new List<Person>();
var departments = new List<Department>();

both Person and Department has properties you've written about.

Your query:

var query= from p in people
           join d in departments on p.Department equals d.ID
           group d by new { p.ID, p.Surname, p.Title } into g
           select new {
               g.Key.ID,
               g.Key.Surname,
               g.Key.Title,
               Departments = String.Join(", ", g.Select(d => d.Name))
           };

And getting results from query to List of anonymous type:

var result = query.ToList();

Upvotes: 2

Related Questions