Блог

Я где-то делаю что-то не так, просто не знаю где.

Я пытаюсь реализовать RANK() функцию окна в своем запросе LINQ.

Ниже, пожалуйста, найдите SQL-запрос:

                 select 
                 tl.TransferLinkId
                ,tl.TransferLinkTypeId
                ,tl.PrimaryTransferId
                ,tl.SecondaryTransferId
                ,tl.UpdatedDate
                ,COUNT(tl.SecondaryTransferId) OVER (Partition by tl.SecondaryTransferId) AS DuplicateCount
                ,RANK() OVER (PARTITION BY tl.TransferLinkTypeId, tl.SecondaryTransferId ORDER BY tl.UpdatedDate DESC, tl.TransferLinkId) as RankVal
            from TransferLink tl WITH (NOLOCK) 
            join TransferLinkType tlt WITH (NOLOCK)  on (tl.TransferLinkTypeId = tlt.TransferLinkTypeId and tlt.EnumTransferLinkType = 'Deviance')
            --where 
            --  tl.Archived=0
            and (
                    PrimaryTransferId in (select TransferId from @AllTransferIds)
                 or SecondaryTransferId in (select TransferId from @AllTransferIds)
                )
            UNION ALL
            select 
                 tl.TransferLinkId
                ,tl.TransferLinkTypeId
                ,tl.PrimaryTransferId
                ,tl.SecondaryTransferId
                ,tl.UpdatedDate
                ,COUNT(tl.PrimaryTransferId) OVER (Partition by tl.PrimaryTransferId) AS DuplicateCount
                ,RANK() OVER (PARTITION BY tl.TransferLinkTypeId, tl.PrimaryTransferId ORDER BY tl.UpdatedDate DESC, tl.TransferLinkId) as RankVal
            from TransferLink tl WITH (NOLOCK) 
            join TransferLinkType tlt WITH (NOLOCK)  on (tl.TransferLinkTypeId = tlt.TransferLinkTypeId and tlt.EnumTransferLinkType = 'Deviance')
            --where 
            --  tl.Archived=0
            and (
                    PrimaryTransferId in (select TransferId from @AllTransferIds)
                 or SecondaryTransferId in (select TransferId from @AllTransferIds)
                )
 

Ниже приведена упрощенная версия реализации моего РАНГА.

 var transferLinks = (from tl in _repo.Context.TransferLink
                                   join tlt in _repo.Context.TransferLinkType on new { TLinkId = tl.TransferLinkTypeId, EType = "Deviance" } equals new { TLinkId = tlt.TransferLinkTypeId, EType = tlt.EnumTransferLinkType }
                                   let duplicateCount = _repo.Context.TransferLink.Where(tl1 => tl1.SecondaryTransferId != null amp;amp; tl.SecondaryTransferId != null amp;amp;
                                                                                                   tl1.SecondaryTransferId == tl.SecondaryTransferId.Value).Count()
                                   where
                                     (allTransferIds.Contains(tl.PrimaryTransferId) || allTransferIds.Contains(tl.SecondaryTransferId)) amp;amp;
                                     !tl.Archived
                                   select new
                                   {
                                       TransferLinkId = tl.TransferLinkId,
                                       TransferLinktypeId = tl.TransferLinkTypeId,
                                       PrimaryTransferId = tl.PrimaryTransferId,
                                       SecondaryTransferId = tl.SecondaryTransferId,
                                       DuplicateCount = duplicateCount, //COUNT(tl.SecondaryTransferId) OVER (Partition by tl.SecondaryTransferId) AS DuplicateCount
                                       UpdatedDate = tl.UpdatedDate,
                                       RankVal = _repo.Context.TransferLink
                                                    .Where(tl1 => tl1.SecondaryTransferId != null amp;amp; tl1.SecondaryTransferId == tl.SecondaryTransferId)
                                                    .OrderByDescending(tl1 => tl1.UpdatedDate)
                                                    .ThenBy(tl1 => tl1.TransferLinkId)
                                                    .GroupBy(tl1 => new { tl1.TransferLinkTypeId, tl1.SecondaryTransferId })
                                                    .SelectMany(g => g.Select((x, i) => new { data = g.Key, index = i   1 }))
                                                    .First(g => g.data.SecondaryTransferId == tl.SecondaryTransferId amp;amp; g.data.TransferLinkTypeId == tl.TransferLinkTypeId).index
                                   }).AsEnumerable().Union(
                                      from tl in _repo.Context.TransferLink
                                      join tlt in _repo.Context.TransferLinkType on new { TLinkId = tl.TransferLinkTypeId, EType = "Deviance" } equals new { TLinkId = tlt.TransferLinkTypeId, EType = tlt.EnumTransferLinkType }
                                      let duplicateCount = _repo.Context.TransferLink.Where(tl1 => tl1.PrimaryTransferId.Value == tl.PrimaryTransferId.Value amp;amp;
                                                                                                tl.PrimaryTransferId != null amp;amp; tl1.PrimaryTransferId != null).Count()
                                      where
                                        (allTransferIds.Contains(tl.PrimaryTransferId) || allTransferIds.Contains(tl.SecondaryTransferId)) amp;amp;
                                        !tl.Archived
                                      select new
                                      {
                                          TransferLinkId = tl.TransferLinkId,
                                          TransferLinktypeId = tl.TransferLinkTypeId,
                                          PrimaryTransferId = tl.PrimaryTransferId,
                                          SecondaryTransferId = tl.SecondaryTransferId,
                                          DuplicateCount = duplicateCount, //COUNT(tl.SecondaryTransferId) OVER (Partition by tl.SecondaryTransferId) AS DuplicateCount
                                          UpdatedDate = tl.UpdatedDate,
                                          RankVal = _repo.Context.TransferLink
                                                    .Where(tl1 => tl1.PrimaryTransferId != null amp;amp; tl1.PrimaryTransferId == tl.SecondaryTransferId)
                                                    .OrderByDescending(tl1 => tl1.UpdatedDate)
                                                    .ThenBy(tl1 => tl1.TransferLinkId)
                                                    .GroupBy(tl1 => new { tl1.TransferLinkTypeId, tl1.PrimaryTransferId })
                                                    .SelectMany(g => g.Select((x, i) => new { data = g.Key, index = i   1 }))
                                                    .First(g => g.data.PrimaryTransferId == tl.PrimaryTransferId amp;amp; g.data.TransferLinkTypeId == tl.TransferLinkTypeId).index
                                      }).ToList();
 

Ниже приведен результат SQL-запроса:

И результаты моего запроса LINQ:

 { TransferLinkId = 1868745, TransferLinktypeId = 3, PrimaryTransferId = , SecondaryTransferId = 44403182, DuplicateCount = 2, UpdatedDate = 2021/08/16 18:23:19, RankVal = 1 }
{ TransferLinkId = 1868751, TransferLinktypeId = 3, PrimaryTransferId = 44403188, SecondaryTransferId = 44403182, DuplicateCount = 2, UpdatedDate = 2021/08/16 19:03:25, RankVal = 1 }
{ TransferLinkId = 1868745, TransferLinktypeId = 3, PrimaryTransferId = , SecondaryTransferId = 44403182, DuplicateCount = 0, UpdatedDate = 2021/08/16 18:23:19, RankVal = 0 }
{ TransferLinkId = 1868751, TransferLinktypeId = 3, PrimaryTransferId = 44403188, SecondaryTransferId = 44403182, DuplicateCount = 1, UpdatedDate = 2021/08/16 19:03:25, RankVal = 1 }
 

Счетчик дубликатов вычисляется правильно, но я, кажется, делаю что-то не так с RankVal.

Есть идеи, чего мне не хватает?