How to select multiple columns in one variable in entity framework code first?

Question

I have two tables in sql (Singer, Albums) now my query is as below

IQuerable<AlbumVM> = (
    from c in context.Singers 
    join d in context.Albums on c.SingerID equals d.SingerID
    select new AlbumVM()
    { 
        SingerName = c.SignerName,
        AlbumsName = d.AlbumName
    } );

Now suppose a singer has 50 columns,this query returns me 50 rows, then I need to iterate through it.

I feel I am missing something here. Is there someway in which I can get just one record as

SingerName , Albums[]

I am using Entity framework code-first.

is that helpful or worked for you ? – Pranay Rana Jul 08 '15 at 19:19 — Pranay Rana, Jul 08 '15 at 19:19

JamieD77 · Accepted Answer · 2015-07-09T14:12:51.760

If you're using code first you should have a foreign key on Albums to Singers

public class Album
{
   public int Id {get;set;}
   public virtual Singer Singer {get;set;}
}

public class Singer
{
   public int Id {get;set;}
   public virtual ICollection<Album> Albums {get;set;}
}

then you would just need

var singers = (
    from s in context.Singers.Include("Albums")
    select s);

this would give you an collection of Singers and each singer would have a collection of Albums

foreach(var s in singers)
{
   foreach(var a in s.Albums)
   { 
      //
   }
}

To get the album name from MasterAlbums you just expand your Include. It should look like this

var singers = (
    from s in context.Singers.Include("Albums.MasterAlbum")
    select s);

Then each Album would have a property of MasterAlbum.Name

@model IEnumerable<MvcApplication1.Models.Singer>
<dl>
    @foreach (var s in Model)
    {
        <dt>s.Name</dt> // Singer Name
        <dd>
            <ul>
                @foreach (var a in s.Albums)
                {
                    <li>a.MasterAlbum.Name</li> // Album Name
                }
            </ul>
        </dd>
    }
</dl>

Yes! People always forget to use these navigation properties. — Gert Arnold, Jul 08 '15 at 19:38
@Jamie, thanks Yes this is what I was looking for **but** the thing is that the Albums table doesn't have names it just have AlbumId, the Album Name is stored in other table(MasterAlbums)..now this MasterAlbums doesn't have direct relation to Singer. Singer has one to may relation with Albums and MasterAlbums have one to many relation with Albums. So how do I fetch AlbumsName ?? — Kgn-web, Jul 09 '15 at 08:32
i added some more info to get Album Name. The names in the `Include` may not be the same pluralization/singularization in your case. Typically the one-to-many relationships `Albums` is plural and one-to-one `MasterAlbum` is singular. — JamieD77, Jul 09 '15 at 14:00

Pranay Rana · Answer 2 · 2015-07-08T19:21:31.087

1

you can try out group join like this

How to: Perform Grouped Joins (C# Programming Guide)

Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
            Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
            Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
            Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };

            Pet barley = new Pet { Name = "Barley", Owner = terry };
            Pet boots = new Pet { Name = "Boots", Owner = terry };
            Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte };
            Pet bluemoon = new Pet { Name = "Blue Moon", Owner = terry };
            Pet daisy = new Pet { Name = "Daisy", Owner = magnus };

     // Create two lists.
            List<Person> people = new List<Person> { magnus, terry, charlotte, arlene };
            List<Pet> pets = new List<Pet> { barley, boots, whiskers, bluemoon, daisy };


var query = from person in people
             join pet in pets on person equals pet.Owner into gj
             select new { OwnerName = person.FirstName, Pets = gj };

Here in example owner i.e. person object is compared but in your case property will get compared

var query = from c in context.Singers 
            join d in context.Albums on c.SingerID equals d.SingerID into gj
             select new { SingerName = c.SingerName, Albums = gj };

edited Jul 08 '15 at 19:21

answered Jul 08 '15 at 19:14

Pranay Rana

175,020
35
237
263

Yes your ans is useful enough but Album table doesn't have names it just have ids, Album names are stored in another table MasterAlbums..so now can you please bit tell me how will I get the Album names matching to id in the same query.. – Kgn-web Jul 09 '15 at 09:40
@Chetan - than you can create one more join between album and album detail table in same query and take grou p – Pranay Rana Jul 09 '15 at 09:45
`var query = from c in context.Singers join d in context.Albums on c.SingerID equals d.SingerID into gj join e in context.MasterAlbums on d.AlbumID equals e.AlbumID select new { } ` But I don't get d.AlbumId, it throws error **The name d doesnot exist in the left side of equals ** – Kgn-web Jul 09 '15 at 10:02
@Pranay..Plus Singer doesn't have any direction relation with MasterAlbums – Kgn-web Jul 09 '15 at 10:05
@Chetan - try like this var categorizedProducts = from p in product join pc in productcategory on p.Id equals pc.ProdId join c in category on pc.CatId equals c.Id select new { ProdId = p.Id, // or pc.ProdId CatId = c.CatId // other assignments }; check this answer : http://stackoverflow.com/questions/9720225/how-to-perform-join-between-multiple-tables-in-linq-lambda for multiple join – Pranay Rana Jul 09 '15 at 10:15
No mate if I do such then it raise intial error i.e., it returns me the row equal to the number of AlbumId. In my case for **1 singer it returns me 3 rows since this singer have 3 ids** @Pranay If I collect AlbumsId into 1 variable as you mentioned in your first comment, it returns 1 Singer row have 3 AlbumIds.. now I need to get the names of Albums matching to the ids that we have collected in the varialble – Kgn-web Jul 09 '15 at 10:23

How to select multiple columns in one variable in entity framework code first?

2 Answers2