将 SQL 查询转换为 LINQ - 不起作用

Question

我要转换的 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]

Answer 1

这与您的查询等效。

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

                    };

Answer 2

尝试以下更简单的方法：

 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();

Answer 3

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

       } ;