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 batch_id = 119815058 and d.transaction_id=4828224
order by date_created desc)
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.