locked
MySql Query to Linq sql C# RRS feed

  • Question

  • select
    h.PUN,
    DATE_FORMAT(convert_tz(h.event_timestamp_utc,'+00:00','-05:00'),'%m-%d-%Y') as LDate,
    u.eid,
    SUM(h.distance) OLM,
    First(h.distance) - Last(h.distance) as OHM,
    ''  OBM
    from event_log h
    Inner Join users u
    on u.id=h.user_id
    WHere h.PUN is not Null and h.PUN not like '' AND h.deleted_at is Null
    group by h.PUN,u.external_id,DATE_FORMAT(convert_tz(h.event_timestamp_utc,'+00:00','-05:00'),'%m-%d-%Y')
    order by h.PUN,LDate,u.eid


                    var milesDataList = (from events in context.event_log
                                                  
                                                   from user in context.users.Where(t=>t.id == events.uid)
                                                   group new { events, user } by new
                                                   {
                                                       events.pun,
                                                       user.eid,
                                                       events.event_timestamp_utc.Value.Date,
                                                   }).Select(x => new MilesData
                                                   {
                                                       eid = x.Key.eid,
                                                       pun= x.Key.pun,
                                                       LDate = x.Key.Date,
                                                       OLM= x.Sum(t=>t.events.distance).ToString(),
                                                       OHM= (x.LastOrDefault().events.distance - x.FirstOrDefault().events.distance).ToString(),
                                                       OBM= string.Empty
                                                   }).ToList();


    • Edited by Satya_S Wednesday, July 12, 2017 8:55 AM
    Wednesday, July 12, 2017 7:28 AM