Converting SQL query into LINQ - not working

Question

The SQL query I'm trying to convert is:

select p.PhoneNumber_Id, p.State, p.Created
from PhoneNumberServiceItems p
join PhoneNumbers on p.PhoneNumber_Id = PhoneNumbers.Id
inner 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.MaxDate
where PhoneNumbers.NumberRangeId = {Id}

And the LINQ code I've ended up with is below, however this isn't working:

 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
        };

I'm aware my LINQ is wrong, but I can't put my finger on what it is I'm doing differently. Any help would be appreciated.

Edit: This is the compiled SQL generated from the LINQ

SELECT 
[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]
    FROM ( SELECT 
        [Distinct1].[PhoneNumber_Id] AS [PhoneNumber_Id], 
        (SELECT TOP (1) 
            [Extent4].[PhoneNumber_Id] AS [PhoneNumber_Id]
            FROM [dbo].[PhoneNumberServiceItems] AS [Extent4]
            WHERE [Distinct1].[PhoneNumber_Id] = [Extent4].[PhoneNumber_Id]) AS [C1]
        FROM ( SELECT DISTINCT 
            [Extent3].[PhoneNumber_Id] AS [PhoneNumber_Id]
            FROM [dbo].[PhoneNumberServiceItems] AS [Extent3]
        )  AS [Distinct1]
    )  AS [Project3] ) AS [Project4] ON ([Extent1].[PhoneNumber_Id] = [Project4].[C1]) AND ([Extent1].[Created] = [Project4].[C2])
WHERE [Extent2].[NumberRangeId] = {id}

How is it 'wrong' ? Is there a compile error? Is the wrong data being retrieved? — Neil
– Neil, Commented Mar 13, 2019 at 9:27
Sorry, should have clarified. When I look at the complied SQL from the LINQ query, it's doing a much more intensive query than the one I've written above. I'll edit my post and add it in. — dmbll
– dmbll, Commented Mar 13, 2019 at 9:32
I'd just like to know if there's a better way of writing the LINQ code that replicates what I've got in SQL? — dmbll
– dmbll, Commented Mar 13, 2019 at 9:46

Serkan Arslan · Accepted Answer · 2019-03-13 10:36:53Z

2

This is linq equivalent of your query.

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
                    };

edited Mar 13, 2019 at 10:36

answered Mar 13, 2019 at 10:21

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jdweng · Accepted Answer · 2019-03-13 10:09:07Z

2

Try following which is much simpler :

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

edited Mar 13, 2019 at 10:09

answered Mar 13, 2019 at 9:52

jdweng

34.5k3 gold badges17 silver badges19 bronze badges

2 Comments

dmbll Over a year ago

Thanks for your help, the SQL generated is much simplier, however it doesn't seem to bring back any data now. The LINQ query looks like it's doing the right selection though.

jdweng Over a year ago

I switched the query around a little bit. If it still doesn't work remove the WHERE and see if you get results.

Vetrivel S · Accepted Answer · 2019-03-13 10:42:33Z

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
       } ;

Collectives™ on Stack Overflow

Converting SQL query into LINQ - not working

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related