locked
How can i convert this sql query to entity framework RRS feed

  • Question

  • WITH addColumn AS (    select rank() OVER (ORDER BY  [m-pay_sub_ops_date] ) as rank, *
       From [m-pay_subscriber_operations]
        ),

     partitioned AS (
      SELECT
        *,
       rank - ROW_NUMBER() OVER (order by rank) AS grp
      FROM addColumn
      WHERE  [m-pay_sub_ops_result_code] = '73' and [m-pay_sub_ops_msisdn_pioneer] = '22103248'
    ),
    counted AS (
      SELECT
        *,
        COUNT(*) OVER (PARTITION BY  grp) AS cnt
      FROM partitioned
    ),
    ranked AS (SELECT

    *,
        RANK() OVER (ORDER BY [m-pay_sub_ops_date] desc, grp) AS rnk
    FROM counted where cnt >5 

    )
    SELECT
      [m-pay_sub_ops_date] , [m-pay_sub_ops_msisdn_pioneer], [m-pay_sub_ops_result_code], grp, cnt
    FROM ranked
    WHERE rnk = 1
    ;

    Thursday, May 19, 2016 1:24 PM

Answers

  • Hi Firas Farhat,

    Please refer the following code.

    var addColumn = db.m_pay_subscriber_operations.OrderBy(s => s.m_pay_sub_ops_date)
                        .Select(s => new
                        {
                            Id = s.Id,
                            m_pay_sub_ops_date = s.m_pay_sub_ops_date,
                            m_pay_sub_ops_msisdn_pioneer = s.m_pay_sub_ops_msisdn_pioneer,
                            m_pay_sub_ops_result_code = s.m_pay_sub_ops_result_code,
                            rank = db.m_pay_subscriber_operations.Where(o=>o.m_pay_sub_ops_date<s.m_pay_sub_ops_date).Count() + 1
                        });
    
                    var partitioned = addColumn.OrderBy(a => a.rank)
                        .Where(a=>a.m_pay_sub_ops_result_code == "73" && a.m_pay_sub_ops_msisdn_pioneer == "22103248").AsEnumerable().Select((a,i) => new
                    {
                        Id = a.Id,
                        m_pay_sub_ops_date = a.m_pay_sub_ops_date,
                        m_pay_sub_ops_msisdn_pioneer = a.m_pay_sub_ops_msisdn_pioneer,
                        m_pay_sub_ops_result_code = a.m_pay_sub_ops_result_code,
                        rank = a.rank,
                        grp = a.rank -(i+ 1)
                    });
    
                    var counted = partitioned.GroupBy(c => c.grp).SelectMany(c=> c.Select(s=> new {
                        Id = s.Id,
                        m_pay_sub_ops_date = s.m_pay_sub_ops_date,
                        m_pay_sub_ops_msisdn_pioneer = s.m_pay_sub_ops_msisdn_pioneer,
                        m_pay_sub_ops_result_code = s.m_pay_sub_ops_result_code,
                        rank = s.rank,
                        grp = s.grp,
                        cnt = c.Count()
                    }));
    
                    var ranked = counted.Where(r => r.cnt > 5).OrderByDescending(r => r.m_pay_sub_ops_date).ThenBy(r => r.grp)
                        .Select((r,i) => new
                        {
                            Id = r.Id,
                            m_pay_sub_ops_date = r.m_pay_sub_ops_date,
                            m_pay_sub_ops_msisdn_pioneer = r.m_pay_sub_ops_msisdn_pioneer,
                            m_pay_sub_ops_result_code = r.m_pay_sub_ops_result_code,
                            rank = r.rank,
                            grp = r.grp,
                            cnt = r.cnt,
                            rnk = i + 1
                        });
    
                    var result = ranked.Where(r => r.rnk == 1);
    

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, May 23, 2016 4:28 AM

All replies

  • Firas your quesiton is very broad and what you asking for is impossible:

    you can do something like that:

     var result = context.Database.SqlQuery<string>( YourSqlQuery).ToList();

    and then with group by you build own data type:

      select new
                {
                    q.DataTypeID,
                    q.Name,
                    DataValues = String.Join(",", q.DataValues),
                }).ToList();

    The generted data type you can use it in your application but it will not be tracked from EF.

    Do not forget to vote and to mark the answer if this solve your problem^^

    Thursday, May 19, 2016 3:59 PM
  • Hi Firas Farhat,

    Please refer the following code.

    var addColumn = db.m_pay_subscriber_operations.OrderBy(s => s.m_pay_sub_ops_date)
                        .Select(s => new
                        {
                            Id = s.Id,
                            m_pay_sub_ops_date = s.m_pay_sub_ops_date,
                            m_pay_sub_ops_msisdn_pioneer = s.m_pay_sub_ops_msisdn_pioneer,
                            m_pay_sub_ops_result_code = s.m_pay_sub_ops_result_code,
                            rank = db.m_pay_subscriber_operations.Where(o=>o.m_pay_sub_ops_date<s.m_pay_sub_ops_date).Count() + 1
                        });
    
                    var partitioned = addColumn.OrderBy(a => a.rank)
                        .Where(a=>a.m_pay_sub_ops_result_code == "73" && a.m_pay_sub_ops_msisdn_pioneer == "22103248").AsEnumerable().Select((a,i) => new
                    {
                        Id = a.Id,
                        m_pay_sub_ops_date = a.m_pay_sub_ops_date,
                        m_pay_sub_ops_msisdn_pioneer = a.m_pay_sub_ops_msisdn_pioneer,
                        m_pay_sub_ops_result_code = a.m_pay_sub_ops_result_code,
                        rank = a.rank,
                        grp = a.rank -(i+ 1)
                    });
    
                    var counted = partitioned.GroupBy(c => c.grp).SelectMany(c=> c.Select(s=> new {
                        Id = s.Id,
                        m_pay_sub_ops_date = s.m_pay_sub_ops_date,
                        m_pay_sub_ops_msisdn_pioneer = s.m_pay_sub_ops_msisdn_pioneer,
                        m_pay_sub_ops_result_code = s.m_pay_sub_ops_result_code,
                        rank = s.rank,
                        grp = s.grp,
                        cnt = c.Count()
                    }));
    
                    var ranked = counted.Where(r => r.cnt > 5).OrderByDescending(r => r.m_pay_sub_ops_date).ThenBy(r => r.grp)
                        .Select((r,i) => new
                        {
                            Id = r.Id,
                            m_pay_sub_ops_date = r.m_pay_sub_ops_date,
                            m_pay_sub_ops_msisdn_pioneer = r.m_pay_sub_ops_msisdn_pioneer,
                            m_pay_sub_ops_result_code = r.m_pay_sub_ops_result_code,
                            rank = r.rank,
                            grp = r.grp,
                            cnt = r.cnt,
                            rnk = i + 1
                        });
    
                    var result = ranked.Where(r => r.rnk == 1);
    

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, May 23, 2016 4:28 AM