LinQ Query for selecting multiple data in single row

Question

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.

Can you show us the entities please? Do you use navigation properties? — user2900970, Nov 18 '14 at 07:18
This might help: http://stackoverflow.com/questions/614542/use-linq-to-concatenate-multiple-rows-into-single-row-csv-property — Milen, Nov 18 '14 at 07:19
he SmartDev, without joining how the code is working. please browse the following link and let me know i am new in EF. http://imgur.com/Nu2qjSa — mnu-nasir, Nov 18 '14 at 11:31
HI SmartDev I solved the problem using your solution. but there are space showing. can you explain. http://i.imgur.com/GCmkXbE.png — mnu-nasir, Nov 18 '14 at 12:12

Ehsan Sajjad · Accepted Answer · 2014-11-19T04:34:57.560

4

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.

edited Nov 19 '14 at 04:34

answered Nov 18 '14 at 07:56

Ehsan Sajjad

61,834
16
105
160

Hi thanks for your answer. Can you please give a code to iterate the collection to show the records in console as required. – mnu-nasir Nov 18 '14 at 09:09
you can iterate on `result` using foreach – Ehsan Sajjad Nov 18 '14 at 09:13
I did so. but the result is not showing exactly. in each row pond id show for one time and fish name should show by concatenating them. but it does not. – mnu-nasir Nov 18 '14 at 09:38
what is the output have you used `String.Join` the way i have posted? – Ehsan Sajjad Nov 18 '14 at 09:51
I have added a inner foreach loop too. but the result is not showing as expected. Like pond id should show for once in each row and fish name should show by concatenating them for each pond id. – mnu-nasir Nov 18 '14 at 11:39
@md.nasir see the updated post tested it on my system – Ehsan Sajjad Nov 18 '14 at 12:13
Can you give a solution of this question: https://stackoverflow.com/questions/67112991/join-multiple-tables-and-get-with-comma-separated-values-with-other-property?noredirect=1#comment118633710_67112991 – mnu-nasir Apr 16 '21 at 05:16

Saket Kumar · Answer 2 · 2014-11-18T08:08:15.847

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

Are you afraid of using `Lambda LinQ` syntax? – Kutyel Nov 18 '14 at 09:01 — Kutyel, Nov 18 '14 at 09:01

LinQ Query for selecting multiple data in single row

2 Answers2

UPDATE: