LinQ Query for selecting multiple data in single row

debugcn Published at Dev

mnu-nasir

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.

Saket

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

Collected from the Internet

Please contact [email protected] to delete if infringement.