Nested select top 1 in Linq

Question

Hey, having some problems figuring this one out.

select *,(select top 1 chicken_nr from chicken_photo where chicken = code order by [sort]) as Photo from Chicken

Code is a column in Table Chicken

Basically getting the cover photo for this chicken.

To make it clearer, I want it to return multiple rows from table Chicken. But only a single entry from chicken_photo.

var q = from chicken in data.chickens
                    join photos in data.chicken_photos
                    on chicken.Code equals photos.chicken                    
                    where chicken.Lang==lang && chicken.photographer_nr == nr
                    group chicken by new     {chicken.photographer,photos.Photograph_Nr,chicken.Title,chicken.Code}              

Answer 1

var photo = (from c in chicken_photo where c.code = chicken orderby c.sort select c.chicken_nr).Take(1).SingleOrDefault();

You should really flesh out your question more...

Answer 2

I figured it out.

Pretty obvious actually, too obvious :)

var q = from chicken in data.chickens
                where chicken.photographer_nr == nr && lang == chicken.Lang
                select new { chicken.photographer, chicken.Code, chicken.Title,Photo = (from b in data.chicken_photos where b.chicken==chicken.Code orderby b.Sort select b.Photograph_Nr).FirstOrDefault() };