none
Help with LINQ RRS feed

  • Question

  •  Dim Activities = (From a In db.SSL_Activities _
            Join b In db.SSL_Activities_Attendees _
            On a.SSLActivityID Equals b.SSLActivityID _
            Where ((a.ActivityTime <> 0.0) And a.Type = 1 And _
            (a.Date >= projyear And (a.Date <= DateAdd(DateInterval.Day, -1, DateAdd(DateInterval.Year, 1, projyear)))) And _
            (From p In db.SSLNew_Profiles _
                             Join pa In db.SSLNew_Profiles_Annuals On p.CodeID Equals pa.CodeID _
                             And pa.SPCodeID = CType(Session("CID"), Integer) _
                             Select p.CodeID).Contains(b.CID)) _
                    Select a.ActivityID, a.Type, a.ActivityTime, a.Date, a.CreatedBy, a.Comments, a.NextSteps, b.SignedDate).Distinct()

    I'm getting multiple records since b.SignedDate field can return multiple records per ActivityID...however, I wasnt to display ActivityID only once.  How do It get Distinct on ActivityID and not on all the columns?

    Thanks.

    Tuesday, August 27, 2013 9:47 PM

Answers

  • Hi Inkaln,

    We could use groupby method to remove the duplicate of your results, have a look at the code below:

    Dim Activities = (From a In db.SSL_Activities _
            Join b In db.SSL_Activities_Attendees _
            On a.SSLActivityID Equals b.SSLActivityID _
            Where ((a.ActivityTime <> 0.0) And a.Type = 1 And _
            (a.Date >= projyear And (a.Date <= DateAdd(DateInterval.Day, -1, DateAdd(DateInterval.Year, 1, projyear)))) And _
            (From p In db.SSLNew_Profiles _
                             Join pa In db.SSLNew_Profiles_Annuals On p.CodeID Equals pa.CodeID _
                             And pa.SPCodeID = CType(Session("CID"), Integer) _
                             Select p.CodeID).Contains(b.CID)) _
                    Select a.ActivityID, a.Type, a.ActivityTime, a.Date, a.CreatedBy, a.Comments, a.NextSteps, b.SignedDate).ToList()

    Dim listDistinct = list.GroupBy(Function(i) i.ActivityID, Function(key, group) group.First()).ToArray()

    Best Regards,


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Thursday, August 29, 2013 9:01 AM
    Moderator

All replies

  • You'll want to group by ActivityID if you only want 1 instance per ActivityID.  However this will also mean that you'll likely have to change the columns you're returning because a group by will return a single row to represent possibly many rows so non-grouped columns won't be available.  I recommend trying LINQPad to get the query correct as grouping isn't quite as clean as most LINQ queries.

    Michael Taylor
    http://msmvps.com/blogs/p3net

    Wednesday, August 28, 2013 5:09 PM
    Moderator
  • Hi Inkaln,

    We could use groupby method to remove the duplicate of your results, have a look at the code below:

    Dim Activities = (From a In db.SSL_Activities _
            Join b In db.SSL_Activities_Attendees _
            On a.SSLActivityID Equals b.SSLActivityID _
            Where ((a.ActivityTime <> 0.0) And a.Type = 1 And _
            (a.Date >= projyear And (a.Date <= DateAdd(DateInterval.Day, -1, DateAdd(DateInterval.Year, 1, projyear)))) And _
            (From p In db.SSLNew_Profiles _
                             Join pa In db.SSLNew_Profiles_Annuals On p.CodeID Equals pa.CodeID _
                             And pa.SPCodeID = CType(Session("CID"), Integer) _
                             Select p.CodeID).Contains(b.CID)) _
                    Select a.ActivityID, a.Type, a.ActivityTime, a.Date, a.CreatedBy, a.Comments, a.NextSteps, b.SignedDate).ToList()

    Dim listDistinct = list.GroupBy(Function(i) i.ActivityID, Function(key, group) group.First()).ToArray()

    Best Regards,


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Thursday, August 29, 2013 9:01 AM
    Moderator