Linq joins on returnable duplicate results
I am using LINQ to try and get all my characters' clothes.
My request 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 serialize it for use in JSON in javascript.
This code returns the user for every outfit I have, I wanted it to group all the clothes in an array. How can i do this?
Current result:
thank
source to share
For this you need to use GroupJoin
: Note the addition into
at the end of the connection
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 */ }
More on MSDN: Perform group connections
Note that if you are using EF there is no need to write connections. Correctly defining navigation properties and using them Include
will give you what you are looking for to a much lesser extent.
Finally, note that even so, Where
this returns IEnumerable<>
results. If you only want one record with all this information, add it to the end, wrap it all, FirstOrDefault
or better yet, remove the clause Where
in the request and run:
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);
source to share
See the 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;
}
}
source to share