Linq join returning duplicate results

Question

I'm using LINQ to try to get all the clothes of my characters.

My query looks like this:

var characterList = from characters in ContextFactory.Instance.Characters
                    where characters.UserId == user.Id

                    join traits in ContextFactory.Instance.CharacterTraits
                    on characters.Id equals traits.CharacterId
                    join clothes in ContextFactory.Instance.CharacterClothes
                    on characters.Id equals clothes.CharacterId
                    select new { 
                        characters.Id, 
                        characters.Name, 
                        characters.Gender, 
                        characters.Level, 
                        characters.Money, 
                        characters.Bank, 
                        characters.LastLogin, 
                        characters.PlayedTime, 
                        traits, 
                        clothes };

Then I'm serializing it to use as JSON in javascript.

This code is returning an user for each clothe I have, I wanted it to group all the clothes in an array. How could I do that?

Current result:

Thank you

Answer 1

To do so you need to use GroupJoin: Notice the addition of the into at the end of the join

var characterList = from characters in ContextFactory.Instance.Characters
                    where characters.UserId == user.Id
                    join t in ContextFactory.Instance.CharacterTraits
                    on characters.Id equals t.CharacterId into traits 
                    join c in ContextFactory.Instance.CharacterClothes into clothes
                    on characters.Id equals c.CharacterId
                    select new { /* As before */ }

Read more on MSDN: Perform Grouped Joins

Notice that if you are using EF there is no need for writing the joins. Properly defining the Navigation Properties and using Include will give you what you are looking for in much less work.

---

Last, notice that even when having the Where condition this will return an IEnumerable<> of results. If you want only one record with all this information then add at the end, wrapping it all, a FirstOrDefault or better still remove the where clause in the query and do:

var character = (from characters in ContextFactory.Instance.Characters
                 join t in ContextFactory.Instance.CharacterTraits
                 on characters.Id equals t.CharacterId into traits 
                 join c in ContextFactory.Instance.CharacterClothes into clothes
                 on characters.Id equals c.CharacterId
                 select new { /* As before */ }).FirstOrDefault( x=> x.id = user.Id);

Answer 2

See code below :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            User user = new User();

            var characterList = (from characters in ContextFactory.Instance.Characters
            where characters.UserId == user.Id
            join traits in ContextFactory.Instance.CharacterTraits
            on characters.Id equals traits.CharacterId
            join clothes in ContextFactory.Instance.CharacterClothes
            on characters.Id equals clothes.CharacterId
            select new { characters = characters, traits = traits, clothes = clothes })
            .GroupBy(x => x.characters.Id)
            .Select(x => new { 
                id = x.FirstOrDefault().characters.Id, 
                name = x.FirstOrDefault().characters.Name, 
                gender = x.FirstOrDefault().characters.Gender, 
                level = x.FirstOrDefault().characters.Level, 
                money = x.FirstOrDefault().characters.Money, 
                bank = x.FirstOrDefault().characters.Bank, 
                lastLogin = x.FirstOrDefault().characters.LastLogin, 
                playedTime = x.FirstOrDefault().characters.PlayedTime, 
                traits = x.FirstOrDefault().traits, 
                clothes = x.Select(y => y.clothes.clothes).ToArray()
            }).FirstOrDefault();

        }
    }

    public class ContextFactory
    {
        public static Instance Instance = null;
    }
    public class Instance
    {
        public List<Characters> Characters;
        public List<CharactersTraits> CharacterTraits;
        public List<CharactersClothes> CharacterClothes;
    }
    public class Characters
    {
        public int Id;
        public int UserId;
        public string Name;
        public string Gender;
        public string Level;
        public string Money;
        public string Bank;
        public string LastLogin;
        public string PlayedTime;
    }
    public class CharactersTraits
    {
        public int CharacterId;
    }
    public class CharactersClothes
    {
        public int CharacterId;
        public string clothes;
    }
    public class User
    {
        public int Id;
    }
}