none
How to convert SQL query RRS feed

  • Question

  • Hi All,
    How to convert this query to LINQ query? I need to get IEnumerable<Parameters> for specific account effective at some specific date.
    Thanks
    Vladimir
    WITH numbered AS (
       SELECT Account, Parameter, Value, 
              rowno = row_number() OVER(PARTITION BY Parameter  ORDER BY EffectiveDate DESC)
              FROM TableParameters
              Where EffectiveDate <= '2009-10-04' and account='11111')
    )
    SELECT Account, Parameter, Value
    FROM   numbered
    WHERE  rowno = 1
    Monday, October 5, 2009 9:30 PM

Answers

  • There is no [direct] 1:1 translation of that, but this should give you the same results:

    DateTime effectiveUntil = new DateTime(2009,10,04);
    string account = "11111";
    
    var query1 =
        from tpi in dc.Tableparameters
        where tpi.Effectivedate <= effectiveUntil
            && tpi.Account == account
        group tpi by new { tpi.Account, tpi.Parameter } into tpg
        select new { tpg.Key.Account, tpg.Key.Parameter, Effectivedate = tpg.Max(d => d.Effectivedate) };
    
    var query2 =
        from tp in dc.Tableparameters
        join tg in query1 on new { tp.Account, tp.Parameter, tp.Effectivedate } equals new { tg.Account, tg.Parameter, tg.Effectivedate }
        group tp by new { tp.Account, tp.Parameter } into tg2
        select new { tg2.Key.Account, tg2.Key.Parameter, Value = tg2.Min(v => v.Value) };
    

    Kristofer - Huagati Systems Co., Ltd. - Cool tools for Linq-to-SQL and Entity Framework: www.huagati.com/dbmltools (VS designer add-in), www.huagati.com/L2SProfiler (query profiler for L2S)
    • Marked as answer by Zhipeng Lee Monday, October 12, 2009 3:20 AM
    Tuesday, October 6, 2009 4:05 AM
    Answerer