日期:2014-05-20  浏览次数:20898 次

一条SQL语句转换成LINQ
select b.ProductID,falsenum from
(Select COUNT(*) as falsenum,ItemID from ItemVerifyHistory a 
 where exists(Select * from ItemVerifyHistory b where b.Sequence=1 and a.Sequence>1 and b.VerifyAddress=8 and
  (a. VerifyAddress != b.VerifyAddress or DATEDIFF(day,b. VerifyDate, a. VerifyDate)>=1)) 
  group by ItemID) as a,ProductItem as b
  where a.ItemID=b.ItemID

把以上SQL语句转换成LINQ TO SQL 语法,大家看下。

------解决方案--------------------
C# code

var query= from x in (
from a in ItemVerifyHistory
where ItemVerifyHistory.Where(b=>b.Sequence==1 && a.Sequence>1 && b.VerifyAddress==8
                      && (a. VerifyAddress != b.VerifyAddress || System.Data.Linq.SqlClient.SqlMethods.DateDiffDay(b. VerifyDate,a. VerifyDate)>=1)).Any()
group a by a.ItemID into g
select new {falsenum=g.Count(),g.Key})
           join b in ProductItem 
           on x.ItemID equals b.ItemID
           select new 
           {b.ProductID,x.falsenum};