locked
Compare records from 2 tables and count for each participant RRS feed

  • Question

  • User-1857885180 posted

    I need to count the following for every participant:

    Match.HomeGoals/AwayGoals == UserMatch.Homegoals/AwayGoals where Match.Id == UserMatch.MatchId

    With my current code i get the sum of goals. I dont know how to proceed.

    public class Match
        {
            public Match()
            {
                UserMatches = new HashSet<UserMatch>();
            }
    
            public int Id { get; set; }
            public int HomeTeamId { get; set; }
            public virtual Team HomeTeam { get; set; }
            public int AwayTeamId { get; set; }
            public virtual Team AwayTeam { get; set; }
            public int? HomeGoals { get; set; }
            public int? AwayGoals { get; set; }
            public DateTime? StartTime { get; set; }
    
            public virtual ICollection<UserMatch> UserMatches { get; set; }
        }
    
    public class UserMatch
        {
            public int ParticipantId { get; set; }
            public int MatchId { get; set; }
            public virtual Participant Participant { get; set; }
            public virtual Match Match { get; set; }
            public int? HomeGoals { get; set; }
            public int? AwayGoals { get; set; }
        }
    
    public class Participant
        {
            public Participant()
            {
                UserMatches = new HashSet<UserMatch>(); 
            }
    
            public int Id { get; set; }
            public string Navn { get; set; }
            public int PlayerId { get; set; }
            public virtual Player TopScorer { get; set; }
            //public int TeamId { get; set; }
            //public virtual Team WorldChampion { get; set; }
    
            public virtual ICollection<UserMatch> UserMatches { get; set; }
    
        }
     
    public ActionResult Index()
            {
                
                var homegoals = db.UserMatches.Where(a => db.Matches.Select(s => s.HomeTeamGoals).Equals(a.HomeGoals)).ToList();
                var awaygoals = db.UserMatches.Where(a => db.Matches.Select(s => s.AwayTeamGoals).Equals(a.AwayGoals)).ToList();
     
                var result = homegoals.Union(awaygoals)
                    .GroupBy(s => new {
                        s.ParticipantId,
                        s.Participant.Name
                    })
                    .Select(s => new ResultatListeViewModel
                    {
     
                        Navn = s.Key.Name,
                        TotalScore= s.Sum(r => r.HomeGoals) + s.Sum(r => r.AwayGoals)
     
                    }).OrderByDescending(a => a.Poengsum).ToList();
     
                return View(result);
     
            }
    <table class="table">
        <tr>
            <th>Name</th>
            <th>Score</th>
        </tr>
        @foreach (var item in Model)
        {
            <tr>
                <td>@item.Name</td>
                <td>@item.TotalScore</td>
            </tr>
        }
     
    </table>

    Wednesday, April 11, 2018 12:25 AM

All replies

  • User1080785583 posted

    I recommond using IEqualityComparer<IMatch>

    Create an interface called IMatch that both Match and UserMatch can implement.

    Then make use of it like this

    using System.Collections.Generic;

    public class CompareValuesSample :IEqualityComparer<IMatch> {

    public bool Equals(IMatch x, IMatch y){

    // here is where you check

    // plenty of samples online also

    // interface is always a nice route to take for the unknowns

    }

    public int GetHashCode(IMatch obj){

    // implement

    }

    }

    // usage

    var homeMatch = new List<HomeMatch>(); // IMatch

    var awayMatch = new List<HomeMatch>(); // IMatch

    bool equalScore = awayMatch.SequenceEqual(homeMatch, new CompareValuesSample());

    // etc

    Monday, December 17, 2018 6:47 PM