CODEWITHSUNDEEP
sqllinq-to-sql
user9659
user9659

Reputation: 901

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

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

Thanks

Upvotes: 2

Views: 1945

Answers (3)

Shawn
Shawn

Reputation: 19793

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

Upvotes: 0

Ryan Versaw
Ryan Versaw

Reputation: 6495

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()
              };

Upvotes: 3

Yannick Motton
Yannick Motton

Reputation: 35971

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 };

Upvotes: 0

Related Questions