none
Assistance with linq to sql query to return list of unique values

    Question

  • I am trying to use linq to sql to return a unique {by oldest date} values from a non-relational sql table.

    so based on what is below, I need to return a single entry for each day.  some days the user has more then one occurrence and need to just pull in the oldest one.

    for example for aacosta2 on 10-15-2013 has 2 entries.  I just need the first occurrence in the list. 

    I am stuck on using grouping I believe..

    data is in this format


    Thank.  sorry about that.  I am using sql 2008 and vs 2010 with .net 4

    format is pretty simple. 

    WTXEP2081    aacosta2    1    10/10/2013 9:00
    WTXEP1967    aacosta2    1    10/11/2013 10:57
    WTXEP2103    aacosta2    1    10/14/2013 8:59
    WTXEP2045    aacosta2    1    10/14/2013 13:13
    WTXEP2134    aacosta2    1    10/15/2013 9:04
    WTXEP2066    aacosta2    1    10/15/2013 17:00
    WTXEP1443    aacosta2    1    10/17/2013 12:22
    WTXEP1988    aacosta2    1    10/18/2013 9:04
    WTXEP2130    aacosta2    1    10/18/2013 11:24
    WTXEP1442    aacosta2    1    10/21/2013 8:52
    WTXEP1428    aacosta2    1    10/21/2013 8:55
    WTXEP1459    aacosta2    1    10/22/2013 8:51
    WTXEP1782    aacy    1    10/10/2013 10:07
    WTXEP1363    aacy    1    10/11/2013 7:28
    WTXEP1765    aacy    1    10/14/2013 7:34
    WTXEP1539    aadkins1    1    10/10/2013 7:06
    WTXEP1449    aadkins1    1    10/10/2013 7:07
    WTXEP2052    aadkins1    1    10/10/2013 9:59

    • Edited by jayc2 Thursday, October 24, 2013 12:25 PM image lost
    Wednesday, October 23, 2013 6:12 PM

All replies

  • Hello,

    Unfortunately, the image is broken.

    Could please share it again?

    And what database have you used?

    And could you please share the table structure?

    With these information, we can help you better I think.

    And there are samples for Linq To Sql, you can have a look:

    http://msdn.microsoft.com/en-us/vstudio/bb688085.aspx

    http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

    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.

    Thursday, October 24, 2013 1:59 AM
    Moderator
  • This is what I have now, but still not giving me a correct result.

     Dim query = (From logon In dc.tblLogonData_Hists
                        Where logon.PCName.ToUpper = item.ServerName.ToUpper And logon.dtSubmitted >= startdate And logon.dtSubmitted < enddate
                        Group logon By logon.PCName, logon.UserID, logon.dtSubmitted Into grp = Group
                        Select New tblLogonData_Hist With {.PCName = PCName, .UserID = UserID, .dtSubmitted = (grp.Max(Function(x) x.dtSubmitted))}).tolist

    WTXEP1400    aacosta2    1    10/23/2013 8:52
    WTXEP1414    aacosta2    1    10/4/2013 15:16
    WTXEP1415    aacosta2    1    10/9/2013 8:55
    WTXEP1422    aacosta2    1    10/1/2013 8:55
    WTXEP1428    aacosta2    1    10/2/2013 7:28
    WTXEP1428    aacosta2    1    10/21/2013 8:55
    WTXEP1442    aacosta2    1    10/21/2013 8:52
    WTXEP1443    aacosta2    1    10/17/2013 12:22
    WTXEP1459    aacosta2    1    10/7/2013 6:48
    WTXEP1459    aacosta2    1    10/11/2013 8:45
    WTXEP1459    aacosta2    1    10/22/2013 8:51
    WTXEP1449    aadkins1    1    10/10/2013 7:07
    WTXEP1450    aadkins1    1    10/9/2013 7:54
    WTXEP1447    aaguila3    1    10/9/2013 14:45
    WTXEP1453    aaguila3    1    10/9/2013 22:23

    Thursday, October 24, 2013 3:56 PM
  • Hi jayc2;

    The following query should give you the resuts you are looking for.

    Dim results = From t in DataContext.TableName _
                  Group t By key = t.NameColumn, t.DateColumn.Date Into Group _
                  Select Group.OrderByDescending(Function(d) d.DateColumn).First

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Thursday, October 24, 2013 4:04 PM
  • on my side it keeps bombing out with a "Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

    server is up and running fine.  I can query table at will, with other queries

    Using dce As New logondataDataContext
    
                        Dim query = (From logon In dce.tblLogonData_Hists
                        Where logon.PCName.ToUpper = servername And logon.dtSubmitted >= startdate And logon.dtSubmitted < enddate
                        Group logon By key = logon.PCName, logon.UserID, logon.dtSubmitted Into Group
                        Select Group.OrderByDescending(Function(d) Convert.ToDateTime(d.dtSubmitted)).First).tolist





    Thursday, October 24, 2013 6:54 PM
  • Hi; 

    Why are you doing this in the Select statement, Convert.ToDateTime(d.dtSubmitted), isn't d.dtSubmitted a date time already?


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Thursday, October 24, 2013 7:10 PM
  • just experimenting. yes it is already a date field.   It still is crashing when removed.
    Friday, October 25, 2013 3:43 PM
  • Hi jayc2;

    The statement, "It still is crashing when removed", is not really helpful in solving the issue. Please post the exception message and as important the inner exception message.

    Thanks. 


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    NOTE: If I ask for code, please provide something that I can drop directly into a project and run (including XAML), or an actual application project. I'm trying to help a lot of people, so I don't have time to figure out weird snippets with undefined objects and unknown namespaces.

    Friday, October 25, 2013 4:46 PM
  •         public static IEnumerable<TSource> DistinctBy<TSource, TKey>
                             (this IEnumerable<TSource> source, Func<TSource, TKey> keySelector)
            {
                HashSet<TKey> seenKeys = new HashSet<TKey>();
                foreach (TSource element in source)
                {
                    if (seenKeys.Add(keySelector(element)))
                    {
                        yield return element;
                    }
                }
            }

      This is an extension method to implement DistinctBy in a query.

      You could then use:

       using (XXXDataContext context = XXXDataAccess.GetContext())
                {
                    var values = context.TblXXXs.OrderByDescending(o => o.ModifiedDate)

                            .DistinctBy(d => d.ModifiedDate.Date).ToList();
                }

          

    rr

    Wednesday, November 06, 2013 6:19 PM
  • the timeout was a server issue.  I have not had a chance to work on this.  Will return to it, hopefully this weekend.
    Thursday, November 07, 2013 3:07 PM