• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1023
  • Last Modified:

LINQ Returning Duplicate Results and return a row number.

I am receiving duplicate records with this query... any ideas?
Second question here, I need to know the row number for each record, via a count. Thanks.

      var members = from f in dc.MemberFollowerCounts
                                join m in dc.Members
                                on f.MemberId equals m.MemberId into joinedMemberCount
                                from m in joinedMemberCount.DefaultIfEmpty()
                                orderby f.FollowerCount descending
                                select new
                                {
                                      name = m.Name,
                                      biography = m.Biography,
                                      followers = f.FollowerCount,
                                      profileImageURL = m.ProfileImageURL,
                                      TwitterUserName = m.TwitterUserName.Replace("@",""),
                                      status = m.Status
                                };

0
Rob Gaudet
Asked:
Rob Gaudet
  • 6
  • 3
  • 2
  • +1
1 Solution
 
kevp75Commented:
It's probably because of your Join.  more than likely there are more than one record in dc.Members that match records in dc.MemberFollowerCounts

for the count, you could add a variable in your loop, and add one each time it iterates
0
 
Rob GaudetAuthor Commented:
Member to MemberFollowerCount is a one to many relationship. Result set incorrectly contains a new row for each record in MemberFollowerCount. I am looking for a single row for each member, that row should also show the highest FollowerCount (int) for each member from the follower count table. I could do this with a sub query in TSQL, not sure how to do this in LINQ.

Member (table)
MemberId
MemberName

MemberFollowerCounts (table)
MemberId
FollowerCount
0
 
Bob LearnedCommented:
It looks like you are doing a Left Join, and not checking to see if "m" is null.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rob GaudetAuthor Commented:
I tried a left and right join. To no avail, any LINQ suggestions?
0
 
kevp75Commented:
can you give us some test data?  I will see if I can get something going
0
 
Bob LearnedCommented:
I agree that it is difficult for you, without knowing your data, to spot any cartesian products, which usually explain duplicate data...
0
 
Rob GaudetAuthor Commented:
I have this issue resolved. Thanks.
0
 
kevp75Commented:
what did you do to resolve it?
0
 
Rob GaudetAuthor Commented:
I abandoned the initial effort to join the tables. The follower count is now a scalar record in the member table instead of updated in a followercount table.
0
 
Rob GaudetAuthor Commented:
I did have second question here that is pending.

I need to know the rank of the returned row.

Just an off the cuff example, that demonstrates..

from a in stuff
let counter = 0
select new { count = counter++, a.Name };
0
 
Rob GaudetAuthor Commented:
BTW, I solved this as well, I used the Row_Number() function with OVER in SQL and called a stored proc from LINQ instead of LINQ. I would like to know how to increment a row from LINQ though.
-- Region Parameters
DECLARE @p0 Int = 0
DECLARE @p1 Int = 0
DECLARE @p2 NVarChar(1000) = '@'
DECLARE @p3 NVarChar(1000) = ''
-- EndRegion
SELECT ROW_NUMBER() OVER (ORDER BY [Followers] DESC) AS 'RowNumber', [t0].[Name] AS [name], [t0].[Biography] AS [biography], 
    (CASE 
        WHEN [t0].[Followers] IS NULL THEN @p1
        ELSE [t0].[Followers]
     END) AS [followers], [t0].[ProfileImageURL] AS [profileImageURL], REPLACE([t0].[TwitterUserName], @p2, @p3) AS [TwitterUserName], [t0].[Status] AS [status]
FROM [Member] AS [t0]
WHERE ([t0].[UpdatedOn] IS NOT NULL) AND ([t0].[Followers] > @p0) AND (((NOT ([t0].[IsDeleted] = 1)) AND (NOT ([t0].[IsInactive] = 1))) OR (([t0].[IsDeleted] IS NULL) AND ([t0].[IsInactive] IS NULL)))
ORDER BY [t0].[Followers] DESC

Open in new window

0
 
ee_autoCommented:
Question PAQ'd and stored in the solution database.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now