How to convert SQL Join query into LINQ?

Question

I need to convert this code into linq query.

The query below gives me to correct total in MS SQL.

SELECT SUM(t1.itemPrice + t2.itemPrice) as TOTAL FROM table1 t1, table2 t2 WHERE r.userID = u.userID

I am trying to get this same code to work in linq query so I can use it in my project MVC4.

My attempt is failing mainly b/c I am not very familiar with Linq just yet. here it is:

--Linq--

var query = (from t1 in db.table1 
             join table2 in db.table2 
             on t1.userID equals t2.userID
             select new { SUM (t2.itemPrice + t1.itemPrice) });

Obviously the above don't work. Can anyone help?

cdhowie · Accepted Answer · 2014-09-25 15:44:12Z

2

You are very close. Sum() has to be applied with non-Linq syntax:

var query = (from t1 in db.table1 
             join table2 in db.table2 
             on t1.userID equals t2.userID
             select t2.itemPrice + t1.itemPrice).Sum();

The sum operation will still be converted to SQL if possible, as you will be calling IQueryable.Sum().

answered Sep 25, 2014 at 15:44

cdhowie

172k25 gold badges303 silver badges324 bronze badges

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

Comments

vikas · Accepted Answer · 2014-09-25 16:04:32Z

0

The other simple looking solution is:

var query = table1.Join(table2, x=>x.userID, y=>y.userID, (x,y) => (x.itemPrice + y.itemPrice)).Sum();

answered Sep 25, 2014 at 16:04

vikas

9396 silver badges11 bronze badges

2 Comments

ceci Over a year ago

OOPS...I just realized that its giving me the wrong total. Because the query uses JOIN the SUM() is wrong. Example, say table1 1 row with the value of $60 and table2 has 2 rows one each with value of 5. The above query returns a SUM of 130 (60 + 60 + 5 + 5). The correct answer should be 70 (60 + 5 + 5).

ceci Over a year ago

How can we use a UNION ALL? It seem to be returning the correct sum. select distinct t1.itemPrice from table1 t1 union all select distinct t2.itemPrice from table2 t2

Collectives™ on Stack Overflow

How to convert SQL Join query into LINQ?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related