CODEWITHSUNDEEP
linqjoin
ceci
ceci

Reputation: 429

How to convert SQL Join query into LINQ?

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?

Upvotes: 1

Views: 108

Answers (2)

vikas
vikas

Reputation: 939

The other simple looking solution is:

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

Upvotes: 0

cdhowie
cdhowie

Reputation: 169403

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().

Upvotes: 2

Related Questions