CODEWITHSUNDEEP
.netentity-frameworklinq-to-entities
SiberianGuy
SiberianGuy

Reputation: 25312

Convert SQL to LINQ To Entities

How can I convert the following SQL from this article:

select type, variety, price
from fruits
where price = (select min(price) from fruits as f where f.type = fruits.type)

I tried it this way:

ctx.Fruits.
Where(f => f.Price == ctx.CreateObjectSet<Fruit>().
Where(f1 => f1.Type == f.Type).Min(f1 => f1.Price));

but it doesn't work because ctx.CreateObjectSet can not be translated to SQL.

Upvotes: 1

Views: 138

Answers (3)

Ian Mercer
Ian Mercer

Reputation: 39297

Might be clearer and easier if you express it as orderby price, group by fruit.type and then just 'pick' the first fruit in each group. And since GroupBy preserves order you can do the sort first (or second) depending on which is easier to read (or based on performance if that matters).

Upvotes: 1

StriplingWarrior
StriplingWarrior

Reputation: 156728

Michael Sagalovich's answer is the correct one for the SQL given, but be aware that if multiple fruits of the same type have the same minimum price, you'll get multiple entries for that type.

This may be more what you're going for: Grabbing only a single smallest-priced fruit for each type:

ctx.Fruits.GroupBy(f => f.Type)
    .Select(g => g.OrderBy(f => f.Price).FirstOrDefault())

Upvotes: 1

Michael Sagalovich
Michael Sagalovich

Reputation: 2549

Why not just 

ctx.Fruits.
Where(f => f.Price == ctx.Fruits.
Where(f1 => f1.Type == f.Type).Min(f1 => f1.Price));

Upvotes: 2

Related Questions