Translate SQL to linq-to-sql (subquery)

How to I convert the following SQL statement into LinqToSQL?

select t1.name, (select COUNT(*) from table2 where t2.f_id = t1.id) as cnt
from table1 t1
开发者_如何学Python

My attempts seem to end up doing an inner join (and therefore giving wildly inaccurate results).

Thanks

---

If relationships are present in your database, you can use a pretty simple query similar to the following:

var results = from t1 in context.Table1
              select new
              {
                  t1.Name,
                  T2Count = t1.Table2s.Count()
              };

If relationships are not present, this should work:

var results = from t1 in context.Table1
              join t2 in context.Table2 on t1.id equals t2.f_id into joined
              select new
              {
                  t1.Name,
                  T2Count = joined.Count()
              };

---

Edit2:

Try this instead, fixed the Key issue in the first query, and the second one now creates a correct empty result.

var subQuery = from t2 in DataContext.Table2
               group t2 by t2.f_Id into myGroup
               select new { Id = myGroup.Key, Cnt = myGroup.Count() };


var result = from t1 in DataContext.Table1
             join t2 in subQuery on t1.Id equals t2.Id into temp
             from t3 in temp.DefaultIfEmpty(new { Id = t1.Id, Cnt = 0 })
             select new { t1.Name, t3.Cnt };

---

var result = db.table1s.select(t => new {.Name = t.Name, .Count = t.table2s.Count()});