CODEWITHSUNDEEP
c#entity-frameworklinq
OrElse
OrElse

Reputation: 9999

Linq query returns duplicate results

The following query returns duplicate results, in the second select query.

Country has 0..1 to * relationship with leagues.

Leagues have 1 to * relationship with userLeagues.

    return from ul in userLeagues
            select new Map.Country
            {
                id = ul.Country.CountryId,
                name = ul.Country.Common_Name,
                leagues =  userLeagues.Where(x => x.CountryId.Value == ul.CountryId.Value)
                                     .Select(x => new Map.League
                                     {
                                         id = x.LeagueID,
                                         name = x.leagueNameEN,
                                     })

            };

I tried using Distinct with no luck. It seems that either i have to use distinct or groupby countryId

The output is such as

[
  {
    "id": 1,
    "name": "Europe",
    "leagues": [
      {
        "id": 2,
        "name": "Champions League",
      },
      {
        "id": 3,
        "name": "Europa league",
      }
    ]
  },
  {
    "id": 1,
    "name": "Europe",
    "leagues": [
      {
        "id": 2,
        "name": "Champions League",
      },
      {
        "id": 3,
        "name": "Europa league",
      }
    ]
  }
]

Upvotes: 0

Views: 2617

Answers (3)

15ee8f99-57ff-4f92-890c-b56153
15ee8f99-57ff-4f92-890c-b56153

Reputation: 37066

Think about what you're doing: For each league in userLeagues, you're creating a Map.Country for the country that league belongs to. If three leagues are in France, that's three Frances. France is a wonderful country, but let's not go overboard.

Instead, you want to start with a distinct list of countries. For each one, create one Map.Country, and give that Map.Country a list of the leagues that should belong to it.

First, let's make Country implement IEquatable<Country> for Distinct purposes:

public class Country : IEquatable<Country>
{
    public bool Equals(Country other)
    {
        return other.CountryID == CountryID;
    }

Second, you want to start with a distinct list of countries, and then populate them with leagues. 

var q =
    from ctry in userLeagues.Select(ul => ul.Country).Distinct()
    select new
    {
        id = ctry.CountryID,
        name = ctry.Common_Name,
        leagues = userLeagues.Where(x => x.Country == ctry)
                             .Select(x => new
                             {
                                 id = x.LeagueID,
                                 name = x.leagueNameEn
                             }).ToList()
    };

I didn't recreate your Map.League and Map.Country classes, I just used anonymous objects, and I left it that way because this code definitely works just as it is. But filling in your class names is trivial.

If it's not practical to make Country implement IEquatable<T>, just write a quick equality comparer and use that:

public class CountryComparer : IEqualityComparer<Country>
{
    public bool Equals(Country x, Country y)
    {
        return x.CountryID == y.CountryID;
    }

    public int GetHashCode(Country obj)
    {
        return obj.CountryID.GetHashCode();
    }
}

...like so:

var cc = new CountryComparer();

var q =
    from ctry in userLeagues.Select(ul => ul.Country).Distinct(cc)
    select new
    {
        id = ctry.CountryID,
        name = ctry.Common_Name,
        leagues = userLeagues.Where(x => cc.Equals(x.Country, ctry))
                             .Select(x => new
                             {
                                 id = x.LeagueID,
                                 name = x.leagueNameEn
                             }).ToList()
    };

This is logically equivalent to a GroupBy, which is probably a more respectable way to do it. But somebody else thought of that before I did, so he earned the glory.

Upvotes: 1

Tomas Chabada
Tomas Chabada

Reputation: 3019

You need to group it by CountryId and Common_Name to get expected results:

var result = from ul in userLeagues
             group ul by new { ul.Country.CountryId, ul.Country.Common_Name } into g
             select new Map.Country
             {
                 id = g.Key.CountryId,
                 name = g.Key.Common_Name,
                 leagues = g.Select(x => new Map.League
                 {
                     id = x.LeagueID,
                     name = x.leagueNameEN,
                 })
             };

Upvotes: 2

WellerEE
WellerEE

Reputation: 338

I would say the you need to reverse your query. So instead of starting with userLeagues, start with country and include the child leagues.

Upvotes: 0

Related Questions