S.A.Parkhid S.A.Parkhid - 4 months ago 10
SQL Question

LINQ how to merge two columns in one column of query result

Hi the datebase schema is something look like this :

enter image description here

and please take look at query below (I want to get inventory of a player 5000 for game with gameID 1001) :

var gameID=1001;
var playerID=5000;
var q = from p in Players
join gp in GamePlays on p.PlayerID equals gp.PlayerID
join gpi in GamePlayItems on gp.GamePlayID equals gpi.GamePlayID
join it in Items on gpi.ItemID equals it.ItemID
where p.PlayerID == playerID
where it.GameID == gameID

select new
{
GamePlayID = gp.GamePlayID,
ItemName = it.ItemName,
ItemValue = gpi.ItemValue,
};
q.Dump();


the result is depicted below:

enter image description here

but I don't want that the column GamePlayID to be repeated . I want just get only record which the second column is hash set or a list contains Inventories.(I want to merge column two and three in one column). how can achieve this? for example my desire result is :

GamePlayID Items
--------- -----
6 A List => { (ItemName1, ItemValue1), ....}


UPDATE :

by ruuning Gilad Green Query the result is something look like this :
query :

var q = (from p in db.Players
join gp in db.GamePlays on p.PlayerID equals gp.PlayerID
join gpi in db.GamePlayItems on gp.GamePlayID equals gpi.GamePlayID
join it in db.Items on gpi.ItemID equals it.ItemID
where p.PlayerID == playerID
where it.GameID == gameID
group new
{
ItemName = it.ItemName,
ItemValue = gpi.ItemValue
}
by new
{
gp.GamePlayID,
gp.BestTimeRecord,
gp.PlayedNumber,
gp.ScoreNumber,
gp.WinNumber
}

into groups
select new { Statictics = groups.Key, Items = groups }).ToList();


enter image description here

but i don't want have key in items...
why this occurs?

Answer

Try this query for the group by:

from p in Players
join gp in GamePlays on p.PlayerID equals gp.PlayerID
join gpi in GamePlayItems on gp.GamePlayID equals gpi.GamePlayID
join it in Items on gpi.ItemID equals it.ItemID
where p.PlayerID == playerID
where it.GameID == gameID

group new 
{ 
    ItemName = it.ItemName, 
    ItemValue = gpi.ItemValue 
} 
by new 
{
    GamePlayID = gp.GamePlayID
    //Here you add other keys you want
} into groups

select new { GamePlayID = groups.Key.GamePlayID, Items = groups.ToList() };
Comments