none
Convert SQL Query to Linq RRS feed

  • Question

  • Below is the SQL Query...Just need to Convert to Linq or IQuerable

    SELECT Serial,Name,Date
    FROM
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY Serial ORDER BY COALESCE(NULLIF(Date,''),'99991231') DESC) AS Seq,*
    FROM TableName
    )t
    WHERE Seq = 1
    Need to Convert into Linq

    Monday, March 13, 2017 6:43 PM

Answers

  • Hi ID GO,

    Please refer to the following code:

    DateTime dt = DateTime.MaxValue;
    
                    var subResults = db.Emphistories
                     .GroupBy(row => row.Serial)
                     .Select(group => group.OrderByDescending(r => r.Date == null ? dt : r.Date).FirstOrDefault())
                     .Select(t => new { Serial = t.Serial, Name = t.Name, Date = t.Date });
    
                
    
                    var results = from p in db.Employees
                                  join s in subResults on p.EmployeeID equals s.Serial into gj
                                  from x in gj.DefaultIfEmpty()
                                  select new
                                  {
                                      Domain = p.Domain,
                                      EmployeeID = p.EmployeeID,
                                      FullName = p.FullName,
                                      NickName = p.NickName,
                                      Date = x.Date,
                                      Name = x.Name
                                  };
    

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ID GO Tuesday, March 21, 2017 6:42 AM
    Tuesday, March 21, 2017 6:21 AM
    Moderator

All replies

  • Hi ID GO,

    Please refer to the following linq.

     var results = db.Tables
                     .GroupBy(row => row.Serial)
                     .Select(group => group.OrderByDescending(r => r.Date == null ? "'99991231'" : r.Date).First())
                     .Select(t=> new { Serial = t.Serial, Name = t.Name, Date = t.Date})
                     ;

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 14, 2017 3:12 AM
    Moderator
  • I am Getting the Below error for this Line

      .Select(group => group.OrderByDescending(r => r.Date == null ? "'99991231'" : r.Date).First())

    Severity Code Description Project File Line Suppression State
    Error CS0173 Type of conditional expression cannot be determined because there is no implicit conversion between 'string' and 'System.DateTime?'

    Tuesday, March 14, 2017 6:14 AM
  • Hi ID GO,

    Please refer to the following linq.

     var results = db.Tables
                     .GroupBy(row => row.Serial)
                     .Select(group => group.OrderByDescending(r => r.Date == null ? "'99991231'" : r.Date).First())
                     .Select(t=> new { Serial = t.Serial, Name = t.Name, Date = t.Date})
                     ;

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    I am Getting the Below error for this Line

     .Select(group => group.OrderByDescending(r => r.Date == null ? "'99991231'" : r.Date).First())

    Severity Code Description  Project File Line Suppression State
    Error CS0173 Type of conditional expression cannot be determined because there is no implicit conversion between 'string' and 'System.DateTime?'

    Tuesday, March 14, 2017 6:14 AM
  • with stats as (
    SELECT Serial,Name,Date
    FROM
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY Serial ORDER BY COALESCE(NULLIF(Date,''),'99991231') DESC) AS Seq,*
    FROM Emphistory
    )t
    WHERE Seq = 1
    )
    SELECT A.Domain,a.[Employee ID], a.[Full Name],a.[Nick Name],b.Date,b.Name
    FROM Employees A
    LEFT JOIN stats B
    ON A.[Employee ID] = B.Serial
    If you could give the Linq for this Query it will be More Help Full
    • Edited by ID GO Tuesday, March 14, 2017 7:44 AM
    Tuesday, March 14, 2017 7:44 AM
  • Hi ID GO,

    Please refer to the following code:

    DateTime dt = DateTime.MaxValue;
    
                    var subResults = db.Emphistories
                     .GroupBy(row => row.Serial)
                     .Select(group => group.OrderByDescending(r => r.Date == null ? dt : r.Date).FirstOrDefault())
                     .Select(t => new { Serial = t.Serial, Name = t.Name, Date = t.Date });
    
                
    
                    var results = from p in db.Employees
                                  join s in subResults on p.EmployeeID equals s.Serial into gj
                                  from x in gj.DefaultIfEmpty()
                                  select new
                                  {
                                      Domain = p.Domain,
                                      EmployeeID = p.EmployeeID,
                                      FullName = p.FullName,
                                      NickName = p.NickName,
                                      Date = x.Date,
                                      Name = x.Name
                                  };
    

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ID GO Tuesday, March 21, 2017 6:42 AM
    Tuesday, March 21, 2017 6:21 AM
    Moderator
  • thank you cole
    Tuesday, March 21, 2017 6:42 AM