LinQ query to select multiple data in one row
I have the following two data tables.
Table: Pond
Pond_ID Pond_Owner
01 Nasir
02 John
Table: Fish
Pond_ID Fish_Name
01 Koi
01 Carp
02 Cat Fish
02 Gold Fish
02 Comet
02 Magur
It is noted that the Pond_ID field is the primary key in the pond table and the foreign key in the fish table . Now I would like to write a LinQ Query to the result like below.
Expected Result
Pond_ID Pond_Owner Fish_Name
01 Nasir Koi, Carp
02 John Cat Fish, Gold Fish, Comet, Magur
So anyone can help me write this linQ query. Thanks in advance.
+3
source to share
2 answers
You need to group them by PondID and OwnerName :
var result = from p in db.pond
join f in db.Fish on p.Pond_Id equals f.Pond_Id
group f by new
{
f.Pond_Id,
f.Owner_name
} into g
select new
{
Owner = g.Key.Owner_Name,
Fishes = String.Join(",",g.Select(x=>x.Fish_Name))
}
then iterating over the result set:
foreach(var item in result)
{
Console.WrtieLine(String.Format("Owner Name : {0} , Fishes : {1}",item.Owner,item.Fishes))
}
Strike>
UPDATE:
var result = from p in db.pond
join f in db.Fish on p.PondID equals f.PondID
group f by new { f.PondID,p.OwnerName } into g
select new { Owner = g.Key.OwnerName, Fishes = String.Join(",",g.Select(x=>x.FishName))};
foreach(var item in result)
{
Console.WriteLine(String.Format("Owner : {0} and Fishses : {1}",item.Owner,item.Fishes));
}
+3
source to share
You can do union operations in LINQ, for example:
var result = (from p in dbContext.Pond
join f in dbContext.Fish
on p.Pond_ID == f.Pond_ID
select new
{
Pond_ID = p.Pond_ID,
Pond_Owner = p.Pond_Owner,
Fish_Name = f.Fish_Name
}).ToList();
A full connection will be performed above the request. If you want to do a left outer join, you can perform the same operation using DefaultIfEmpty()
like:
var result = (from p in dbContext.Pond
join f in dbContext.Fish
on p.Pond_ID == f.Pond_ID into group1
from g1 in group1.DefaultIfEmpty()
select new
{
Pond_ID = p.Pond_ID,
Pond_Owner = p.Pond_Owner,
Fish_Name = g1.Fish_Name
}).ToList();
+1
source to share