Joining 2 table with LINQ to Entities

Question

Joining 2 table with LINQ to Entities

asp.net-mvc-3 entity-framework linq razor

Question

I have two tables, for Users and Songs. I need to JOIN them both on the UserID column and return a list to the view. I tried writing an anonymous type but I'm getting an error about:

'Only parameterless constructors and initializers are supported in LINQ to Entities.'

How can I JOIN and return to list?

 public class User
    {
        [Key]
        public virtual Guid UserId { get; set; }

        [Required]
        public virtual String Username { get; set; }

        [Required]
        public virtual String Email { get; set; }

        [Required, DataType(DataType.Password)]
        public virtual String Password { get; set; }

}





 public class Song
    {
        public int SongId { get; set; }

        public virtual Guid UserId { get; set; }

        public string SongTitle { get; set; }

    }

I tried implementing a method like this one here:

What is the return type of my linq query?

The query I ended up with was:

var query = from u in db.Users
                        join s in db.Songs
                        on u.UserId equals s.UserId
                        select new HomeSongList(s.SongId, s.UserId, u.Username);


            return View(query.ToList());

user547794

Popular Answer

Well the error message is rather clear, no ?

You try to create a new HomeSongList in an linq2entity query (which will be translated in sql : not possible)

With the "User property" correction, you should make

var query = db.Songs.Select(s => new{
                       songId = s.SongId, 
                       userId = s.User.UserId, 
                       userName = s.User.Username})
                     .ToList()
                     .Select(x => new HomeSongList(x.songId, x.userId, x.userName);

or with your actual code

var query = (from u in db.Users
                        join s in db.Songs
                        on u.UserId equals s.UserId
                        select new {
                          songId = s.SongId, 
                          userId = s.UserId, 
                          userName = u.Username
                         })//new anonymous object is possible in linq2entities
                        .ToList()
                        .Select(x => new HomeSongList(x.songId, x.userId, x.userName);

RaphaÃ«l Althaus

View more on Stack Overflow

Guvante · Accepted Answer · 5/10/2012 11:16:15 PM

Accepted Answer

You can't use parameterized constructors but you can use initializers, if you change your select to the following it should work.

select new HomeSongList{SongId=s.SongId, UserId=s.UserId, Username=u.Username};

Note that this will require that HomeSongList has a parameterless constructor and writable properties.

Guvante

Joining 2 table with LINQ to Entities

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related