I have wrote a query in sql but i need to convert it into linq to sql.

See more:

SQL-Server-2005

SQL-Server

LINQ

I wrote a query in sql. but I want that query in linq to sql in C#. I tried to convert that but its not working.

select x.package_id,x.hnumber,(sum( x.par)) as par,(sum( x.npar)) as npar,x.complete_date, x.transaction_type
     from
(SELECT e.batch_id
       ,a.[Transaction_ID]
       ,b.[Hnumber]
      ,[Import_Workbook_ID]
      ,c.[Dispo_Category]
      ,(case when c.dispo_category ='par'then 1 else 0 end) PAR
      ,(case when c.dispo_category ='npar'then 1 else 0 end) NPAR
      ,d.[Package_ID]
      ,d.transaction_type
      ,[Complete_Date]
  FROM [dbo].[Transactions]a
  left join [dbo].[Plans]b on a.Plans_ID = b.Plans_id
  left join [dbo].[Disposition_Code]c on a.Disposition_Code_ID = c.Disposition_Code_ID
  left join [dbo].[fdr_batch_details]d on a.Transaction_ID = d.transaction_id
  left join [dbo].[fdr_batch_master]e on d.batch_id = e.batch_id
  where e.batch_id = (select top 1 batch_id
  from [dbo].[fdr_batch_master]
  --where date_created >= (GETDATE()-1)
 WHERE batch_id = 119815058 and d.transaction_id=4828224
  order by date_created desc)
 -- and Package_ID is not null
 -- and SUBSTRING(Package_id,1,3) = 'SUB'
  group by a.complete_date,a.[Transaction_ID],b.[Hnumber],c.[Dispo_Category],[Processed_By],d.transaction_type
  ,a.[Last_Modified_By],[Import_Workbook_ID],e.batch_id,d.package_id)x
 Group by x.package_id,x.hnumber,x.complete_date,x.transaction_type
  Order by x.package_id

is a sql query.

I convertd it in to linq to sql as
(from a in rptData.Transactions
join b in rptData.Plans on a.Plans_ID equals b.Plans_id
join c in rptData.Disposition_Codes on a.Disposition_Code_ID equals c.Disposition_Code_ID
join d in rptData.fdr_batch_details on a.Transaction_ID equals d.transaction_id
join e in rptData.fdr_batch_masters on d.batch_id equals e.batch_id
where e.batch_id==batch_ID && d.transaction_id==transid select new { e.batch_id,a.Transaction_ID,b.Hnumber,a.Import_Workbook_ID,c.Dispo_Category,
Par=(c.Dispo_Category=="par" ? "1":"0"),
NPar=(c.Dispo_Category=="npar"?"1":"0"),d.Package_ID,d.transaction_type,a.Complete_Date

}

But I'm stopped at grouping. I'm not able to group it. could some on help me how to group it please.