DB Result Set and Model Result Sets Different | Rows Duplicating

Adrian N 81

Hey Everyone

Just wondering if I can get some help with something. I created an application where people can sign up for a competition. Basically the user can enter their team details, such as Team Name and the Players the are involved.

The application has the ability to view the list of players that have sign up for the competition, where it displays the Team Name and players that are involved.
When I do an SQL Query on the database, the results are fine and shows the correct information. However when I do the same from the app, the rows are for some reason duplicated.

I've attached the images from the db and the application.

DB Result set from: SELECT * FROM [dbo].[CompetitionBookingParticipant_View] WHERE CompetitionId='4b0f9684-1ac6-484a-9fe2-c02ecd098ac2';

View that I receive when asking for the same information.
Query: List<CompetitionBookingParticipant_View> participantsList = await _context.CompetitionBookingParticipant_View.Where(booking => booking.CompetitionId == competition.Id).OrderBy(booking => booking.Division).ThenBy(booking => booking.TeamName).ThenBy(booking => booking.Name).ToListAsync();

Viorel 112.1K Reputation points

2022-08-18T05:48:47.157+00:00

Maybe the property of CompetitionBookingParticipant_View, which corresponds to Player column, is static?

Show some details about CompetitionBookingParticipant_View class and SQL view.

Adrian N 81

Hey @Viorel

Below are the Classes and the SQL View in MSSMS.

public class CompetitionParticipantViewModel  
{  
    public Competition Competition { get; set; }  
    public List<CompetitionBookingParticipant_View> CompetitionBookingParticipants { get; set; }  

    public CompetitionParticipantViewModel() { }  

    public CompetitionParticipantViewModel(Competition competition, List<CompetitionBookingParticipant_View> competitionBookingParticipants)  
    {  
        Competition = competition;  
        CompetitionBookingParticipants = competitionBookingParticipants;  
    }  

}

Adrian N 81 Reputation points

2022-08-22T00:22:52.687+00:00
public class CompetitionBookingParticipant_View
{
public string Id { get; set; }
public string CompetitionId { get; set; }
public string TeamName { get; set; }
public string Division { get; set; }
public string PrimaryContactName { get; set; }
public string PrimaryContactPhone { get; set; }
public string CompetitionBookingId { get; set; }
public string Name { get; set; }

public CompetitionBookingParticipant_View() { } public CompetitionBookingParticipant_View(string id, string competitionId, string teamName, string division,

``string primaryContactName, string primaryContactPhone, string competitionBookingId, string name)
{
Id = id;
CompetitionId = competitionId;
TeamName = teamName;
Division = division;
PrimaryContactName = primaryContactName;
PrimaryContactPhone = primaryContactPhone;
CompetitionBookingId = competitionBookingId;
Name = name;
}
}
Adrian N 81 Reputation points

2022-08-22T00:23:03.797+00:00

Database View

SELECT dbo.CompetitionBooking.Id, dbo.CompetitionBooking.CompetitionId, dbo.CompetitionBooking.TeamName, dbo.CompetitionBooking.Division, dbo.CompetitionBooking.PrimaryContactName,
dbo.CompetitionBooking.PrimaryContactPhone, dbo.CompetitionParticipant.CompetitionBookingId, dbo.CompetitionParticipant.Name
FROM dbo.CompetitionBooking INNER JOIN
dbo.CompetitionParticipant ON dbo.CompetitionBooking.Id = dbo.CompetitionParticipant.CompetitionBookingId

Adrian N 81

Below is the code I used to grab the information and display it to the view.

        Models.Competition competition = await _context.Competition.FindAsync(id);  
        List<CompetitionBookingParticipant_View> participantsList = await _context.CompetitionBookingParticipant_View

.Where(booking => booking.CompetitionId == competition.Id)
.OrderBy(booking => booking.Division)
.ThenBy(booking => booking.TeamName)
.ThenBy(booking => booking.Name)
.ToListAsync();

        // Add the values into the ViewModel and pass it into the view.  
        CompetitionParticipantViewModel viewModel = new CompetitionParticipantViewModel();  
        viewModel.Competition = competition;  
        viewModel.CompetitionBookingParticipants = participantsList;  

        return View(viewModel);

Viorel 112.1K Reputation points

2022-08-22T03:26:14.083+00:00

Try using the Debugger to check the contents of participantsList variable. If it is valid (no duplicates), then maybe it is not correctly displayed. Check the code that displays these results.
Adrian N 81 Reputation points

2022-09-01T03:35:25.497+00:00

Hey Sorry for the delayed response. I ran a print line for each participant, before it gets thrown into the view, and it looks like its the data that its getting is incorrect somehow.
So the view is showing the data correctly, however when the code is asking to get the information, it somehow just creates duplicates.

Team: White Men Can Jump | Division: U14 Boys | Player: Bligh Hall-Leeke
Team: White Men Can Jump | Division: U14 Boys | Player: Bligh Hall-Leeke
Team: White Men Can Jump | Division: U14 Boys | Player: Bligh Hall-Leeke
Team: WMBA | Division: U14 Girls | Player: Alexis Powell
Team: WMBA | Division: U14 Girls | Player: Alexis Powell
Team: WMBA | Division: U14 Girls | Player: Alexis Powell
Team: WMBA | Division: U14 Girls | Player: Alexis Powell
Team: Team name to be confirmed later | Division: U16 Boys | Player: Brendan Cheng
Team: Team name to be confirmed later | Division: U16 Boys | Player: Brendan Cheng
Team: Team name to be confirmed later | Division: U16 Boys | Player: Brendan Cheng
Team: Team name to be confirmed later | Division: U16 Boys | Player: Brendan Cheng
Team: Team name to be confirmed later | Division: U16 Boys | Player: Brendan Cheng
Team: ADRIAN TEST | Division: U23 Boys | Player: TEST BILLY
Team: ADRIAN TEST | Division: U23 Boys | Player: TEST BILLY
Team: ADRIAN TEST | Division: U23 Boys | Player: TEST BILLY