Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

LINQ Returning Duplicate Results and return a row number.

Posted on 2010-11-17
13
999 Views
Last Modified: 2012-05-10
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
Comment
Question by:Rob Gaudet
  • 6
  • 3
  • 2
  • +1
13 Comments
 
LVL 25

Expert Comment

by:kevp75
ID: 34163878
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
 

Author Comment

by:Rob Gaudet
ID: 34164402
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 34166167
It looks like you are doing a Left Join, and not checking to see if "m" is null.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:Rob Gaudet
ID: 34183948
I tried a left and right join. To no avail, any LINQ suggestions?
0
 
LVL 25

Expert Comment

by:kevp75
ID: 34187932
can you give us some test data?  I will see if I can get something going
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 34189262
I agree that it is difficult for you, without knowing your data, to spot any cartesian products, which usually explain duplicate data...
0
 

Author Comment

by:Rob Gaudet
ID: 34310756
I have this issue resolved. Thanks.
0
 
LVL 25

Expert Comment

by:kevp75
ID: 34310875
what did you do to resolve it?
0
 

Author Comment

by:Rob Gaudet
ID: 34310898
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
 

Author Comment

by:Rob Gaudet
ID: 34310914
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
 

Author Comment

by:Rob Gaudet
ID: 34311078
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
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 35154569
Question PAQ'd and stored in the solution database.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Button to go back 3 28
convert null in sql server 12 45
Load data upon clicking a button 8 34
Need return values from a stored procedure 8 19
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question