none
Cannot get a single record per ID RRS feed

  • Question

  • I am building an application for internal use, that I can only post the LINQ code from.  I am writing an ASP.NET MVC application using C# in Visual Studio 2013.  I am hoping that someone can help me either in code or razor to accomplish my goal.

    In this application, there is a table for a ticket number.  This value is entered once, but the ID number of that record is used to insert update records in another table.  All update records are kept for reporting purposes, but for the "management" view, I want to only show the most recent record.  The updates are numbered sequentially with an auto-number field in a SQL 2012 database.  I will have other views and will most likely end up reusing the code that answers my question.

    Here is my query:

    var list = from u in DB.Ticket_Updates
               join o in DB.Tickets on u.Ticket_No equals o.Ticket_No
               join d in DB.Req_Depts on o.Req_Dept equals d.Dept_Code
               join e in DB.Employees on o.Assignee equals e.ID
               join e2 in DB.Employees on u.Update_By equals e2.ID
               join s in DB.Status_Codes on u.StatusCode equals s.StatusCode
               join t in DB.Req_Types on o.Req_Code equals t.Req_Code
               where o.Comp_Date.Equals(null) || o.Comp_Date <= DateTime.Now.AddDays(-7)
               orderby o.Open_Date, u.Update_No descending
               select new OpenTicketView
               {
                    Ticket_No = o.Ticket_No,
                    Assignee = e.Name,
                    Department = d.Department,
                    Exp_Comp_Date = o.Exp_Comp_Date,
                    Request_Type = t.Request_Type,
                    Status = s.Status,
                    Description = u.Description,
                    Open_Date = o.Open_Date,
                    Update_By = e2.Name,
                    Update_No = u.Update_No
               };
    This works fine, but for records that have more than one update - say 2, I get 2 records for that ticket in the resulting list.  I have tried to do separate selects and then merge them with one last select, grouping and ordering and I either end up with errors or no results.  Before the code above, I tried starting off with the base table (o) and get the same exact results.  I'm sure I'm overlooking something, I just can't figure out what.  Any assistance will be greatly appreciated!


    Senior System Admin, VM Admin, Developer


    • Edited by ole88 Sunday, June 28, 2015 12:07 PM
    Friday, June 26, 2015 8:21 PM

Answers

  • Hello ole88,

    >> want to only show the most recent record.

    From your description, I think what you want is to get the select last 1 for each group in linq, here I made an example which returns records which are in the last sequence:

    Demo tables:

    CREATE TABLE [dbo].[Order] (
    
        [OrderID]   INT          NOT NULL,
    
        [OrderName] VARCHAR (50) NULL,
    
        CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED ([OrderID] ASC)
    
    );
    
    
    CREATE TABLE [dbo].[OrderDetailSet] (
    
        [ODID]    INT            NOT NULL,
    
        [ODName]  NVARCHAR (MAX) NOT NULL,
    
        [OrderID] INT            NULL,
    
        CONSTRAINT [PK_OrderDetailSet] PRIMARY KEY CLUSTERED ([ODID] ASC),
    
        CONSTRAINT [FK_OrderOrderDetail] FOREIGN KEY ([OrderID]) REFERENCES [dbo].[Order] ([OrderID])
    
    );
    

    Demo Data:

    OrderID, OrderName

    1

    1

    2

    2

    3

    3

    ODID,ODName,OrderID

    1

    1

    1

    2

    2

    1

    3

    1

    2

    4

    2

    2

    5

    3

    3

    6

    3

    1

    Demo query:

    using (DFDBEntities db = new DFDBEntities())
    
                {
    
                    var result = (from o in db.Orders
    
                                  join od in db.OrderDetailSets on o.OrderID equals od.OrderID
    
                                  select new { o.OrderID, o.OrderName, od.ODID, od.ODName } into ood
    
                                  group ood by new { ood.OrderID, ood.OrderName } into groups
    
                                  select groups.OrderByDescending(s => s.ODName).FirstOrDefault()).ToList();
    
                }
    

    The result would be:

    OrderID,OrderName,ODID,ODName

    1              1              6              3

    2              2              4              2

    3              3              5              3             

    As you can see, it would return records whose ODName value is in last sequence.

    Regards.


    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.

    • Marked as answer by ole88 Wednesday, July 1, 2015 12:44 PM
    Monday, June 29, 2015 4:53 AM
    Moderator
  • Hello,

    >>but I still get all updates listed for each ticket instead of just the most recent one.

    It is because you group by the “Update_No” at the same time, please remove it to from your group by statement:

    group odata by odata.Ticket_No into groups
    
               select groups.OrderByDescending(s => s.Update_No).FirstOrDefault()).ToList();
    

    Regards.


    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.

    • Marked as answer by ole88 Wednesday, July 1, 2015 12:42 PM
    Wednesday, July 1, 2015 7:17 AM
    Moderator

All replies

  • Hello ole88,

    >> want to only show the most recent record.

    From your description, I think what you want is to get the select last 1 for each group in linq, here I made an example which returns records which are in the last sequence:

    Demo tables:

    CREATE TABLE [dbo].[Order] (
    
        [OrderID]   INT          NOT NULL,
    
        [OrderName] VARCHAR (50) NULL,
    
        CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED ([OrderID] ASC)
    
    );
    
    
    CREATE TABLE [dbo].[OrderDetailSet] (
    
        [ODID]    INT            NOT NULL,
    
        [ODName]  NVARCHAR (MAX) NOT NULL,
    
        [OrderID] INT            NULL,
    
        CONSTRAINT [PK_OrderDetailSet] PRIMARY KEY CLUSTERED ([ODID] ASC),
    
        CONSTRAINT [FK_OrderOrderDetail] FOREIGN KEY ([OrderID]) REFERENCES [dbo].[Order] ([OrderID])
    
    );
    

    Demo Data:

    OrderID, OrderName

    1

    1

    2

    2

    3

    3

    ODID,ODName,OrderID

    1

    1

    1

    2

    2

    1

    3

    1

    2

    4

    2

    2

    5

    3

    3

    6

    3

    1

    Demo query:

    using (DFDBEntities db = new DFDBEntities())
    
                {
    
                    var result = (from o in db.Orders
    
                                  join od in db.OrderDetailSets on o.OrderID equals od.OrderID
    
                                  select new { o.OrderID, o.OrderName, od.ODID, od.ODName } into ood
    
                                  group ood by new { ood.OrderID, ood.OrderName } into groups
    
                                  select groups.OrderByDescending(s => s.ODName).FirstOrDefault()).ToList();
    
                }
    

    The result would be:

    OrderID,OrderName,ODID,ODName

    1              1              6              3

    2              2              4              2

    3              3              5              3             

    As you can see, it would return records whose ODName value is in last sequence.

    Regards.


    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.

    • Marked as answer by ole88 Wednesday, July 1, 2015 12:44 PM
    Monday, June 29, 2015 4:53 AM
    Moderator
  • Fred,

    Thanks for the example.  I implemented it and it works great, but I still get all updates listed for each ticket instead of just the most recent one.  I'm not sure why it is working this way.  Here is the code I have updated to.

    var list = (from o in DB.Tickets
               join ou in DB.Ticket_Updates on o.Ticket_No equals ou.Ticket_No
               join oea in DB.Employees on o.Assignee equals oea.ID
               join od in DB.Req_Depts on o.Req_Dept equals od.Dept_Code
               join oec in DB.Employees on o.Created_By equals oec.ID
               join ot in DB.Req_Types on o.Req_Code equals ot.Req_Code
               join oue in DB.Employees on ou.Update_By equals oue.ID
               join ous in DB.Status_Codes on ou.StatusCode equals ous.StatusCode
               select new
               {
                   Ticket_No = o.Ticket_No,
                   Open_Date = o.Open_Date,
                   Assignee = oea.Name,
                   Requestor = o.Requestor,
                   Department = o.Req_Dept,
                   Description = o.Description,
                   Exp_Comp_Date = o.Exp_Comp_Date,
                   Created_By = oec.Name,
                   Request_Type = ot.Request_Type,
                   Update_By = oue.Name,
                   Update_No = ou.Update_No,
                   UpdateStatus = ous.Status,
                   UpdateDescription = ou.Description,
                   UpdateDate = ou.Date
               } into odata
               group odata by new { odata.Ticket_No, odata.Update_No } into groups
               select groups.OrderByDescending(s => s.Update_No).FirstOrDefault()).ToList();
    
    
    Any additional ideas and/or assistance you can provide will be greatly appreciated.


    Senior System Admin, VM Admin, Developer

    Tuesday, June 30, 2015 5:52 PM
  • Hello,

    >>but I still get all updates listed for each ticket instead of just the most recent one.

    It is because you group by the “Update_No” at the same time, please remove it to from your group by statement:

    group odata by odata.Ticket_No into groups
    
               select groups.OrderByDescending(s => s.Update_No).FirstOrDefault()).ToList();
    

    Regards.


    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.

    • Marked as answer by ole88 Wednesday, July 1, 2015 12:42 PM
    Wednesday, July 1, 2015 7:17 AM
    Moderator
  • Thanks Fred!  That did the trick.  I am definitely going to document this as it will come in handy on other projects too.

    Senior System Admin, VM Admin, Developer

    Wednesday, July 1, 2015 12:43 PM