我在sql中写了一个查询，但我需要将它转换为linq到sql。 [英] I have wrote a query in sql but i need to convert it into linq to sql.

问题描述

我在sql中写了一个查询。但我想在linq中查询到C＃中的sql。我尝试转换它但它不起作用。

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

是一个sql查询。



我将它转换为linq到sql为

（来自rptData中的一个）。交易

在a.Plans_ID上的rptData.Plans中加入b等于b.Plans_id

在a.Disposition_Code_ID上的rptData.Disposition_Codes中加入c等于c.Disposition_Code_ID

在rptData.fdr_中加入d a.Transaction_ID上的batch_details等于d.transaction_id

在d.batch_id上的rptData.fdr_batch_masters中加入e等于e.batch_id

其中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



}



但是我已经停止了分组。我不能把它分组。有些人可以帮我分组吧。

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.

推荐答案

尝试使用LINQPad。



< a href =http://www.linqpad.net/> http://www.linqpad.net/ [ ^ ]

Try to use LINQPad.

http://www.linqpad.net/[^]

这篇关于我在sql中写了一个查询，但我需要将它转换为linq到sql。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！