join three tables with LINQ on different columns and print sum at the end

Question

I have 3 tables as shown.

var table1 = new[]
{
    new { Col1 = 1, Col2 = 'A', },
};

var table2 = new[]
{
    new { Col1 = 'A', Col2 = 'X', },
    new { Col1 = 'B', Col2 = 'Y', },
};

var table3 = new[]
{
    new { Col1 = 1, Col3 = 'X', Col4 = 100, },
    new { Col1 = 1, Col3 = 'Y', Col4 = 200, },
    new { Col1 = 2, Col3 = 'X', Col4 = 400, }
};

Row needed -> 1, A, X, 100

I tried How to join 3 tables with linq which is not working.

Please help with the exact code. I am new to SQL.

Put your attempt in the question, explain why what you have isn't working. — Thom A, May 28 '21 at 10:35
Do you want an example in LINQ or SQL? It's not clear from the question. — Andrei15193, May 28 '21 at 10:38
@Larnu I attempted https://stackoverflow.com/questions/41933985/how-to-join-3-tables-with-linq — devMe, May 28 '21 at 10:41
@RPD well, that should work. What's giving you trouble specifically? — Andrei15193, May 28 '21 at 10:44
@RPD - The linked answer is exactly what you need to do. What wasn't working? — Enigmativity, May 28 '21 at 10:46
Put ***your*** attempt in the question, @RPD ... Clearly you've not altered the solution correctly when you implemented it, so if you show us your *actual* attempt we can tell you where you went wrong. — Thom A, May 28 '21 at 10:47
@Larnu, sorry for confusion select new MyProjectViewModel { myClassProperty = IShouldGetValue100 } Please help me to define IShouldGetValue100 I want 100 because of table value. — devMe, May 28 '21 at 11:18
@Enigmativity select new MyProjectViewModel { myClassProperty = IShouldGetValue100 } Please help me to define IShouldGetValue100. I want 100 because of table value. — devMe, May 28 '21 at 12:39
@Enigmativity select new MyProjectViewModel { myClassProperty = IShouldGetValue100 } Please help me to define IShouldGetValue100. I want 100 because of table value. Currently, I am getting 300 (because 100+200). — devMe, May 28 '21 at 12:45
@Enigmativity I had to add where t3.Col1 == t1.Col1 before the select part in your query. That worked! Thank you! If where is not added then rows like 2 'X' 300 in table3 poses problem. I hope I am making sense. — devMe, Jun 02 '21 at 07:52
@RPD - My query worked given your data. What made you think it needed an extra `where` to make it work? — Enigmativity, Jun 02 '21 at 08:31

score 0 · Accepted Answer · edited Oct 18 '21 at 19:30

0

Give this a go:

var table1 = new[]
{
    new { Col1 = 1, Col2 = 'A', },
};

var table2 = new[]
{
    new { Col1 = 'A', Col2 = 'X', },
    new { Col1 = 'B', Col2 = 'Y', },
};

var table3 = new[]
{
    new { Col1 = 1, Col3 = 'X', Col4 = 100, },
    new { Col1 = 1, Col3 = 'Y', Col4 = 200, },
};

var query =
    from t1 in table1
    join t2 in table2 on t1.Col2 equals t2.Col1
    join t3 in table3 on t2.Col2 equals t3.Col3
    where t3.Col1 == t1.Col1
    select new
    {
        t1.Col1, Col22 = t1.Col2, Col23 = t2.Col2, t3.Col4,
    };

I get:

1 A X 100

However, with your naming convention of ColX and repeating values it has made a few things ambiguous. It would have been nice to have properly named fields with clearly distinct values.

edited Oct 18 '21 at 19:30

devMe

29
5

answered May 28 '21 at 10:44

Enigmativity

113,464
11
89
172

select new MyProjectViewModel { myClassProperty = IShouldGetValue100 } Please help me to define IShouldGetValue100 – devMe May 28 '21 at 11:10
@RPD - What do you mean by "define IShouldGetValue100"? – Enigmativity May 29 '21 at 02:09
1

I have edited your kind suggestion to answer my question. I have also edited the question where I have added a third row in the data. For such data row "where condition" is needed. – devMe Oct 18 '21 at 15:12

join three tables with LINQ on different columns and print sum at the end

1 Answers1