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