我要轉(zhuǎn)換的 SQL 查詢是：select p.PhoneNumber_Id, p.State, p.Createdfrom PhoneNumberServiceItems pjoin PhoneNumbers on p.PhoneNumber_Id = PhoneNumbers.Idinner join ( select PhoneNumber_Id, max(Created) as MaxDate from PhoneNumberServiceItems group by PhoneNumber_Id) tm on p.PhoneNumber_Id = tm.PhoneNumber_Id and p.Created = tm.MaxDatewhere PhoneNumbers.NumberRangeId = {Id}我最終得到的 LINQ 代碼如下，但這不起作用： var res = from serviceItems in _db.PhoneNumberServiceItems join nums in _db.PhoneNumbers on serviceItems.PhoneNumber_Id equals nums.Id where nums.NumberRangeId == id join serviceGroup in (from ps in _db.PhoneNumberServiceItems group ps by ps.PhoneNumber_Id into numGroup //join tm in _db.PhoneNumbers on psg.FirstOrDefault().PhoneNumber_Id equals tm.Id select new { NumId = numGroup.FirstOrDefault().PhoneNumber_Id, MaxDate = numGroup.Max(i => i.Created) }) on new { PNId = serviceItems.PhoneNumber_Id, serviceCreated = serviceItems.Created } equals new { PNId = serviceGroup.NumId, serviceCreated = serviceGroup.MaxDate } select new { State = serviceItems.State, NumId = serviceGroup.NumId, Created = serviceGroup.MaxDate };我知道我的 LINQ 是錯誤的，但我無法指出我在做什么不同。任何幫助，將不勝感激。編輯：這是從 LINQ 生成的已編譯 SQLSELECT [Extent1].[State] AS [State], [Project4].[C1] AS [C1], [Project4].[C2] AS [C2]FROM [dbo].[PhoneNumberServiceItems] AS [Extent1]INNER JOIN [dbo].[PhoneNumbers] AS [Extent2] ON [Extent1].[PhoneNumber_Id] = [Extent2].[Id]INNER JOIN (SELECT [Project3].[C1] AS [C1], (SELECT MAX([Extent5].[Created]) AS [A1] FROM [dbo].[PhoneNumberServiceItems] AS [Extent5] WHERE [Project3].[PhoneNumber_Id] = [Extent5].[PhoneNumber_Id]) AS [C2]
3 回答
RISEBY
TA貢獻(xiàn)1856條經(jīng)驗 獲得超5個贊
這與您的查詢等效。
var res = from s in PhoneNumberServiceItems
join p in PhoneNumbers on s.PhoneNumber_Id equals p.Id
join tm in ( from p1 in PhoneNumberServiceItems
group p1 by p1.PhoneNumber_Id into p_g
select new {PhoneNumber_Id = p_g.Key,MaxDate = p_g.Max(i=> i.Created) } )
on new {Created = s.Created, PhoneNumber_Id = s.PhoneNumber_Id}
equals new { Created = tm.MaxDate, PhoneNumber_Id = tm.PhoneNumber_Id}
where p.NumberRangeId == {Id}
select new
{
s.PhoneNumber_Id,
s.State,
s.Created
};
慕村9548890
TA貢獻(xiàn)1884條經(jīng)驗 獲得超4個贊
嘗試以下更簡單的方法：
var res = (from nums in _db.PhoneNumbers.Where(x => NumberRangeId == id)
join serviceItems in _db.PhoneNumberServiceItems on nums.PhoneNumber_Id equals serviceItems.Id
select new {serviceItems = serviceItems, nums = nums})
.OrderByDescending(x => x.serviceItems.Created)
.GroupBy(x => x.nums.PhoneNumber_Id)
.Select(x => x.First())
.Select(x => new {Id = x.nums.PhoneNumber_Id, state = x.serviceItems.State, maxDate = x.serviceItems.Created})
.ToList();
海綿寶寶撒
TA貢獻(xiàn)1809條經(jīng)驗 獲得超8個贊
var recentPhoneNos= from psi in _db.PhoneNumberServiceItems
group psi by psi .PhoneNumber_Id into psiTemp
select new {
PhoneNumber_Id = psiTemp.Key,
MaxDate = psiTemp.Max(i=> i.Created)
};
var res=from serviceItems in _db.PhoneNumberServiceItems
join nums in _db.PhoneNumbers on serviceItems.PhoneNumber_Id equals nums.Id
join serviceGroup in recentPhoneNos on nums.Id equals serviceGroup .PhoneNumber_Id
where nums.NumberRangeId == id && serviceGroup.MaxDate
select new {
State = serviceItems.State,
NumId = serviceGroup.NumId,
Created = serviceGroup.MaxDate
} ;
- 3 回答
- 0 關(guān)注
- 139 瀏覽
添加回答
舉報
0/150
提交
取消