Nested top 1 selection in Linq

Question

There are some problems with this.

select *, (select top 1 chicken_nr from chicken_photo, where chicken = code order [sort]) as the chicken photo

Code is a column in Table Chicken

Basically getting a cover for this chicken.

To make it clearer, I want it to return a few rows from the Chicken table. But only one 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 really be done so that the result is only one SQL query.

If you are executing a subquery, as you wrote against the Entity Framework, then the Linq query will become the only 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()
            };

Entity Framework, :

    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()
            };

, , -:

    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()
            }
        );

, Entity Framework . SQL .

, , , , -.

Answer 2

.

, :)

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() };

Answer 3

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

...