Nested select top 1 in Linq
having some problems figuring this one out.
select *,(select top 1 chicken_nr from chicken_photo where chicken = code order by [sort]) as Photo f开发者_高级运维rom 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}
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.
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() };
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...
精彩评论