CODEWITHSUNDEEP
c#entity-frameworklinq
TyForHelpDude
TyForHelpDude

Reputation: 5002

Building linq query that collect records by group

scenario is simple actually but handling it in linq require more exp than I have.. There is 3 table

Table1
ID Column
1   val1
2   val2
Table2
ID Column
1   val3
2   val4
Table3
ID Column
1   val5
2   val6

I need such a query that returns;

TableResult:

Row ID Column Type
1   1   val1  table1
2   2   val2  table1
3   1   val3  table2
4   2   val4  table2
5   1   val5  table3
6   2   val6  table3

Searched on net and started like below but cant figure how handle the tricks create "type", merge records etc..

  from t1 in table1 
    join t2 in table2 on t1.id equals t2.id
    join t3 in table3 on t1.id equals t3.id
    select new {...}

Upvotes: 0

Views: 47

Answers (2)

Brett
Brett

Reputation: 1550

You've already accepted an answer, so I don't know if this is what you WANT, but it generates the output you specified in your post.

Because you have only used Id values of 1 and 2, it's unclear whether you actually want to perform a Join or just get the set of all rows into a single result.

Anyway:

struct TableStructure
{
    public int Id { get; set; }
    public string Column { get; set; }
}

var t1 = new List<TableStructure>() { new TableStructure { Id = 1, Column = "val1" }, new TableStructure { Id = 2, Column = "val2" } };
var t2 = new List<TableStructure>() { new TableStructure { Id = 1, Column = "val3" }, new TableStructure { Id = 2, Column = "val4" } };
var t3 = new List<TableStructure>() { new TableStructure { Id = 1, Column = "val5" }, new TableStructure { Id = 2, Column = "val6" } };

var result = ((from row1 in t1 select new { row1.Id, row1.Column, SourceTable = "table1" })
        .Union(from row2 in t2 select new { row2.Id, row2.Column, SourceTable = "table2" })
        .Union(from row3 in t3 select new { row3.Id, row3.Column, SourceTable = "table3" }))
        .AsEnumerable().Select((row, index) => new { RowNum = index + 1, row.Id, row.Column, row.SourceTable });

result.ToList().ForEach(row => Console.WriteLine($"{row.RowNum}, {row.Id}, {row.Column}, {row.SourceTable}"));

output:

1, 1, val1, table1
2, 2, val2, table1
3, 1, val3, table2
4, 2, val4, table2
5, 1, val5, table3
6, 2, val6, table3

Upvotes: 2

Masoud Andalibi
Masoud Andalibi

Reputation: 3228

Same as what you did try Distinct at the end. query syntax would be : 

var List = (from t1 in dbContext.table1
                  join t2 in dbContext.table2 on t1.ID equals t2.ID
                  join t3 in dbContext.table3 on t1.ID equals t3.ID
                  select new
                  {
                   //t1.DesiredColumnName,
                   //t2.DesiredColumnName,
                   //t3.DesiredColumnName,
                   //so on
                  }).Distinct().ToList();

Upvotes: 1

Related Questions