What sould the identical Linq to sql query for this sql server query ?

Question

I want to convert Following sql server query with Linq-to-sql query . What I have to do. how it will be? I am using c#.

SELECT       Table1.CRNo, Table2.StageId

FROM         Table1 INNER JOIN Table2 
             ON Table1.CRNo = Table2.CRNo

WHERE        (Table2.IsActive = 'true')

Table 1 and Table 2 are two tables. CRNo is identical in both tables. Table 2 is detail table of Table 1.

What should be the query.

Edited:

 from record1 in table1
 join record2 in table2 on record1.CRNo equals record2.CRNo
 where record2.IsActive
 select new { record1.CRNo, record2.StageId }

definitely, it is working fine. but results comes with a record who as IsActive False also if there are multiple entries in the table2. let say table 2 have records as :

CRNo:1 StageId: 1 IsActive:False
CRNo:2 StageId: 1 IsActive:False
CRNo:1 StageId: 2 IsActive:True

Then this is coming with CRNo 1 with Stage 1 , which has IsActive False. Why should this is happening ? Please review this again

Answer 1

from record1 in table1
join record2 in table2 on record1.CRNo equals record2.CRNo
where record2.IsActive
select new { record1.CRNo, record2.StageId }

Answer 2

If the tables have a foreign key relationship present in you database - or modelled in your dbml - you could also do the following:

from t1 in Table1
where t1.Table2.IsActive == "true"   // assuming that IsActive is a string and not a boolean
select new { t1.CRNo, t1.Table2.StageId }

In order to speed up things you maybe should use DataLoadOptions to specify that for each t1 the Table2 entry should be fetched as well:

using (ctx = new MyDataContext()) {
  var options = new DataLoadOptions();
  options.LoadWith<Table1>(x => x.Table2);
  ctx.LoadOptions = options;

  var res = from t1 in Table1
            where t1.Table2.IsActive == "true"
            select new { t1.CRNo, t1.Table2.StageId };

}

Additionally you might want to look here for samples of the linq syntax.

Edit - to consider only active entries:

from t1 in table1
join t2 in table2.Where(x => x.IsActive == "true") on t1.CRNo equals t2.CRNo
select new { t1.CRNo, t2.StageNo };