Nested Select linq query with max clause

2 posts, 0 answers
  1. Giovanna
    Giovanna avatar
    1 posts
    Member since:
    Jan 2015

    Posted 22 Jan 2015 Link to this post

    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
    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
  2. Thomas
    Thomas avatar
    588 posts

    Posted 23 Jan 2015 Link to this post

    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.

    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
  3. DevCraft banner
Back to Top