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}
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With