Nested select top 1 in Linq

Question

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

This can indeed be done such that it results in only one SQL query underneath.

If you perform the subselect as you have written against Entity Framework, then the Linq query will become a single SQL query.

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

If your tables have proper primary and foreign key relationships, and proper navigation associations in Entity Framework then you can also achieve the same results this way:

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

And finally, to stay completely consistent and use only lambda-expressions:

    var q = data.chickens
       .Where(c => chicken.photographer_nr == nr && chicken.Lang == lang)
       .Select(c => new
            {
                c.photographer,
                c.Code,
                c.Title,
                Photo = c.chicken_photos.Select(cp => cp.Photograph_Nr).FirstOrDefault()
            }
        );

I prefer relying on entity navigation, as it forces the developer to create proper navigation associations in Entity Framework and proper foreign key relationships in the database. This will almost always result in optimized SQL underneath.

It's not always up to the developer how the database is structured, so you may have to stick with the first approach and write the sub-select yourself.