CODEWITHSUNDEEP
linqentity-framework
mnu-nasir
mnu-nasir

Reputation: 1760

LinQ Query for selecting multiple data in single row

I have following two tables with data.

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 Pond_ID field is the primary key in Pond Table and Foreign key in Fish Table. Now I would like to write a LinQ Query to the result like bellow.

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 to write this linQ query. Thanks in advance.

Upvotes: 4

Views: 3441

Answers (2)

Ehsan Sajjad
Ehsan Sajjad

Reputation: 62488

You have to group them on 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 iterate on result set:

foreach(var item in result)
{
  Console.WrtieLine(String.Format("Owner Name : {0} , Fishes : {1}",item.Owner,item.Fishes))
}

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));
    }

See this WORKING FIDDLE EXAMPLE for more.

Upvotes: 4

Saket Kumar
Saket Kumar

Reputation: 4835

You can perform join operations on LINQ like:

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();

Above query will perform full Join. In case you want to perform left outer join, you can do the same operation using DefaultIfEmpty() as:

  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();

Upvotes: 1

Related Questions