SQL和LINQ查询

在linq中,我没有这方面的经验,所以我只给出sql server查询

SELECT a.*,b.MyPhoto FROM PhotoAlbums a,(SELECT Top 1 myPhoto from Photos Where AlbumID = a.ID) b

或者这个

SELECT a.AlbumID, a.Title, a.Date, (SELECT TOP (1) c.PhotoID FROM Photos c WHERE(c.AlbumID = a.AlbumID)) AS PhotoID FROM PhotoAlbums as a

如果桌子是这样的:

PhotoAlbums
AlbumID    PK
...

Photos
PhotoID    PK
AlbumID    FK
Photo
...

下面是SQL查询:

SELECT
    a.*,p.Photo 
    FROM PhotoAlbums a
        LEFT OUTER JOIN (SELECT
                             AlbumID,MIN(PhotoID) AS MinPhotoID
                             FROM Photos
                             GROUP BY AlbumID
                        ) dt ON a.AlbumID=dt.AlbumID
        LEFT OUTER JOIN Photos p ON dt.MinPhotoID=p.PhotoID

工作示例:

DECLARE @PhotoAlbums table (AlbumID  int)
INSERT @PhotoAlbums VALUES (1)
INSERT @PhotoAlbums VALUES (2)
INSERT @PhotoAlbums VALUES (3)

DECLARE @Photos table (PhotoID int,AlbumID int, Photo varchar(10))
INSERT @Photos VALUES (1,1,'A')
INSERT @Photos VALUES (2,1,'B')
INSERT @Photos VALUES (3,1,'C')
INSERT @Photos VALUES (4,2,'AA')
INSERT @Photos VALUES (5,3,'AAA')
INSERT @Photos VALUES (6,3,'BBB')

SELECT
    a.*,p.Photo 
    FROM @PhotoAlbums a
        LEFT OUTER JOIN (SELECT
                             AlbumID,MIN(PhotoID) AS MinPhotoID
                             FROM @Photos
                             GROUP BY AlbumID
                        ) dt ON a.AlbumID=dt.AlbumID
        LEFT OUTER JOIN @Photos p ON dt.MinPhotoID=p.PhotoID

输出:

AlbumID     Photo
----------- ----------
1           A
2           AA
3           AAA

(3 row(s) affected)

linq查询可能是这样的:

from album in context.PhotoAlbums
from photo in album.Photos.Take(1).DefaultIfEmpty()
select new
   {
        Album = album,
        Photo = photo
   }