I've started using Linq-to-entities with .NET 3.5, and I've come across a scenario that I can do real easy in SQL, but I'm having great difficulty with linq. I have a table in a SQL database with three fields, for purposes of this example I'll call them foo, bar, and foo_index and populate them like so:
Blockquote
foo | bar | foo_index
555 101 1
555 101 2
555 101 3
555 101 4
555 101 5
To get what bar is at the max foo_index where foo=555 the SQL is
SELECT bar, max(foo_index) as max_foo_index
FROM T
WHERE foo=555
GROUP BY bar
I've written some linq-to-entities code in C# that works, but I have a feeling there is a much more elegent solution out there, and that I'm missing some concept that would make the following much easier. Also, is there any way other then a foreach to get the data out of a var?
db_entity db = new db_entity();
var q =
from table in db.T
where table.FOO == 555
select new { table.BAR, table.FOO_INDEX };
var q2 =
from t2 in q
where t2.FOO_INDEX == table.Max(r => r.FOO_INDEX)
select new { t2.BAR };
int result = 0;
foreach (var record in q2}
{
result = record.BAR.Value;
}
Any help or guidance would be appreciated, Thanks!