translate t-sql in linq

How can I write the below t-sql in Linq?

开发者_开发技巧

select (select COUNT(col1) FROM t2 WHERE col2 = t1.col2 and col1 = t1.col1) as total,

 t1.col1,t1.col2...................

from t1 

---

var res = from t1 in context.t1s
    select new
    {
        total = context.t2s.Where(t2=> t2.col1 == t1.col1 && t2.col2 == t1.col2).Count(),
        t1.col1,
        t1.col2,
    };

Edit:
And if you have your FK-relations set up correctly in the database and your DBML you can use the simpler version below

var res = from t1 in context.t1s
    select new
    {
        total = t1.t2s.Count(),
        t1.col1,
        t1.col2,
    };

---

Try using LinqPad. It is great for translating between SQL and LINQ queries.

http://www.linqpad.net/