This is a migrated thread and some comments may be shown as answers.
Nested Select linq query with max clause
1 Answer 19 Views
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Giovanna
Top achievements
Rank 1
Giovanna asked on 22 Jan 2015, 03:45 PM
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

1 Answer, 1 is accepted

Sort by
0
Thomas
Telerik team
answered on 23 Jan 2015, 10:28 AM
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
 
OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
 
Tags
LINQ (LINQ specific questions)
Asked by
Giovanna
Top achievements
Rank 1
Answers by
Thomas
Telerik team
Share this question
or