as performed query SQL in linq or lambda expression?

Question

Good day to all, I have the following problem I have to convert the following query SQL to LINQ to SLQ or lambda expression:

SELECT  T2.ID, SUM(T1.Importe) AS Importe, T3.ID, T3.Column_1,
        T3.Column_2, T1.Column_3
FROM    Tabla_1 T1
INNER JOIN Tabla_2 T2 on T1.ID = T2.ID
INNER JOIN Tabla_3 T3 on T2.ID = T3.ID
WHERE   T1.ID in (LIST)
GROUP BY T2.ID, T3.ID, T3.Column_1, Column_2, T1.Column_3

where LIST is a List (12,15,18,19,...)

as performed in linq or lamnbda expression?

thks!

Start off small. Make one inner join and get that working for you, make a simple grouping and then put it all together. — Filip Ekberg
– Filip Ekberg, Commented Apr 2, 2012 at 22:58

The Smallest · Accepted Answer · 2012-04-03 11:19:17Z

3

Here is the hint:

var result = ents.T1
    .Where(x => list.Contains(x.Id))
    .GroupBy(x => new 
                 { 
                    Id2 = x.T2.Id, 
                    Id3 = x.T3.Id,
                    ...
                    // etc group fields 
                 })
    .Select(x => new
                 { 
                    Importe = x.Sum(i => i.Importe)
                    x.Key.Id2,
                    // other group fields
                    ...
                 })
    .ToArray();

I strongly suggest not to name columns and tables the way you did. Really)

edited Apr 3, 2012 at 11:19

answered Apr 2, 2012 at 23:07

The Smallest

5,78328 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ch2o Over a year ago

the field "Importe" exist in the GroupBy clause?

The Smallest Over a year ago

Importe exists in each element of Grouping. So you can use Sum over it.

Gabriel GM · Accepted Answer · 2012-04-03 00:02:18Z

1

I did not try, but it could look like that :

var result = (from t1 in dataContext.tabla_1
              join t2 in dataContext.tabla_2 on t1.ID equals t2.ID
              join t2 in dataContext.tabla_2 on t2.ID equals t3.ID
              group t2 by t2.ID into g
              where listIds.contains(t1.ID)
              select new {g.ID, g.Sum(t => t.Importe), ...});

answered Apr 3, 2012 at 0:02

Gabriel GM

6,6392 gold badges34 silver badges36 bronze badges

3 Comments

ch2o Over a year ago

var result = (from t1 in dataContext.tabla_1 join t2 in dataContext.tabla_2 on t1.ID equals t2.ID join t2 in dataContext.tabla_2 on t2.ID equals t3.ID where listIds.contains(t1.ID) group t2 by t2.ID into g select new {g.ID, g.Sum(t => t.Importe), ...}); LINQ to Entities does not recognize the method 'Boolean Contains(Int32)' method, and this method cannot be translated into a store expression.

Gabriel GM Over a year ago

It is the same function as The_Smallest's answer. Is it working in his example?

ch2o Over a year ago

no work, exception message: "LINQ to Entities does not recognize the method 'Boolean Contains(Int32)' method, and this method cannot be translated into a store expression" thks anyway =)

Collectives™ on Stack Overflow

as performed query SQL in linq or lambda expression?

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related