[LINQ] Master–Detail Same Record (I)
PROBLEM
Firstly, I am working on a project based on LINQ, EF, and C# with VS2010.
The following Table shows what I have and what I want to show.
| Header | |||
| C1 | C2 | C3 | |
| 1 | P1 | 01/01/2011 | |
| 2 | P2 | 01/02/2011 | |
| Details | |||
| 1 | 1 | D1 | |
| 2 | 1 | D2 | |
| 3 | 1 | D3 | |
| 4 | 2 | D1 | |
| 5 | 2 | D4 | |
| Expected Results | |||
| 1 | P1 | 01/01/2011 | D1, D2, D3 |
| 2 | P2 | 01/02/2011 | D1,D4 |
IDEAS
At the begin I got 3 possible ways:
- Doing inside the DB: It could be achieved from DB with a CURSOR in a Stored Procedure.
- Doing from .NET with LOOPS.
- Doing with LINQ (I love it!!)
FIRST APROX
Example with a simple CLASS with a LIST:
With and Employee Class that acts as Header Table:
1: public class Employee
2: {
3: public Employee () { }
4: public Int32 ID { get; set; }
5: public String FirstName{ get; set; }
6: public String LastName{ get; set; }
7: public List Numbers{ get; set; } // Acts as Details Table
8: }
We can show all numbers contained by Employee:
1: List listado = new List();
2: //Fill Listado
3: var query= from Employee em in listado
4: let Nums= string.Join(";", em.Numbers)
5: select new {
6: em.Name,
7: Nums
8: };
The “LET” operator allows us to host the results of “Join” of the Numbers List of the Employee Class.
A little example.
ASAP I will post the second part to achieve the same with Entity Framework.
Best Regards
posted on Wednesday, February 16, 2011 10:36 PM Print