Nested Select linq query with max clause

[Giovanna]

i would use a nested linq query for this select:
   
    select * from T_VERSION ver where (ver.id_file,VER.VERSION) in(select
     id_file, max(version) from T_VERSION group by id_file)

suppose there are this field
ID | ID_FILE | VERSION
1  | 1| 1
2  | 1| 2
3  | 1| 3
4  | 2| 1
5  | 2| 2

I would obtain rows: 3  | 1| 3   and 5  | 2| 2  that is max version groupby ID_FILE

i tried with this code but it is wrong
    dataprovider => from fileVer in dataprovider
    where fileVer.ID == (from ver in dataprovider select ver).Max(u => u.VERSION)
select fileVer).ToList();

it will be use in a win application, c#
can you help me?thanks in advance

[Thomas]

I think the intended SQL looks like

select *
from t_versions ver
join (select id_file, max(t_version) as z from t_versions group by id_file) as x 
        on (x.id_file = ver.id_file and x.z = ver.version);

The IN statement is not usable, as it is limited to single values, not tuples.
Unfortunately, there is no LINQ that DataAccess translates into this SQL, and you will need to express this functionality via SQL directly.

Regards,
Thomas
Telerik