SQL, LINQ, and lambda query statements compare INNER JOIN and group by combination usage and anonymous type processing

Using EF's own small functions need to encounter inner join and group by combination of use and anonymous type of processing, search a lot, basically can not meet their own needs, so summed up also realized on their own write out, has been prepared to view and partner query Reference (General statement query does not say, the network search a lot)

Statement query background (or not directly look at the statement is also very tough): the main want to achieve similar features of QQ album display, the page shows all album list, mainly include: album in the album number, a photo album in a picture as the album's default background, album name, album Description , Delete and edit features

SQL Server:(this is relatively simple)

Select COUNT (Photoalbumid) as counts,C.photoalbumname,c.describe,c.photoalbumid, MAX (Photourl) from
(Select B.photoalbumid,b.photourl,a.id,a.photoalbumname,a.describe
From PhotoAlbum as a
INNER JOIN Photos as B on A.id=b.photoalbumid) as C
GROUP BY Photoalbumname,describe,photoalbumid

Linq:

var photolist2 = (from a in db. PhotoAlbum
Join B in DB. Photos on a.id equals b.photoalbumid
group New {A, B} by new {a.photoalbumname, A.describe, a.photocounts, b.photoalbumid} to C

Select New Photoalbumlistmodel
{
ID = C.key.photoalbumid,
Photoalbumname = C.key.photoalbumname,
Describe = C.key.describe,
Photocounts = C.key.photocounts,

Firstphotourl = C.max (d = d.b.photourl),//This can not be seen, see your own needs

}). ToList ();

Note:(Photoalbumlistmodel This is my new entity class, because the original query result set is anonymous type, to the page can not be recognized, need to convert, of course, but also using the method of foreach conversion)

Lambda:

var photolist3 = db. PhotoAlbum.Join(Db. Photos, a = a.id, B = b.photoalbumid, (A, b) and new
{
a.ID,
A.photoalbumname,
A.describe,
A.photocounts,
B.photourl,
B.photoalbumid,
}).GroupBy (c = new {C.photoalbumid, C.photoalbumname, C.describe, c.photocounts}). Select (d = newPhotoalbumlistmodel
{
ID = D.key.photoalbumid,
Photoalbumid = D.key.photoalbumid,
photoalbumname = D.key.photoalbumname,
Describe = D.key.describe,
photocounts = d.key.photocounts,
Firstphotourl = D.max (e = e.photourl),//This can not be seen, see your own needs
}). ToList ();

Note:(Photoalbumlistmodel This is my new entity class, because the original query result set is anonymous type, to the page can not be recognized, need to convert, of course, but also using the method of foreach conversion)

Of course, there are more than one way, there are other better and more aspects can also leave a message to me, do not understand can also discuss with each other, I will look at once a day. Write bad, need to improve, can make comments. Thank you

SQL, LINQ, and lambda query statements compare INNER JOIN and group by combination usage and anonymous type processing