convert select query to linq on sql server 2008

Question

I have two tables: Album(AlbumId, AlbumName) and Photo(PhotoId, PhotoName, AlbumId)

I have :

 select top 1 Photo.PhotoId
 from Photo
 where Album.AlbumId=Photo.AlbumId
 order by NEWID()

This is return one record random

I want convert it to Linq to sql. I tried but not success.

Answer 1

I believe you would need to join the tables. Also, it depends on what language you are using when you want to create a random Guid - my example uses C#. In query syntax, it should be something like:

from p in Photo
join a in Album on a.AlbumId equals p.AlbumId
orderby Guid.NewGuid()
take 1
select p.PhotoId

Answer 2

try this:

var photoId = ( from photo in collecPhotos
                join album in collecAlbums on photo.AlbumId equals album.AlbumId
                orderby NEWID()
                select photo.PhotoId).First();

where collecPhotos and collecAlbums are the collection of your Photo and Album entity i.e. data from the tables. so it can also be myDbContext.Photos and myDbContext.Albums where myDbContext is your Linq-to-Sql DataContext.