none
Avoid repeated rows on a Select. With a group by? RRS feed

  • Question

  • I'm developing an Entity Framework 4.4.0.0 Code First library with C# and .NET Framework 4.0.

    I have this data on Messages table:

    As you can see, there are repeated messages.

    And I have this LinQ to retrieve that messages:

    var msgs = from message in context.Messages
                where message.TalkId == talkId &&
                    (message.SentBy == userId || message.SentTo == userId)
                orderby message.DateUtcSent
                select message;
    

    But I get most of them.

    How can I do to get that messages without repetition?<br/>

    If I'm user with userId == 15, I want to get messages with MessageId: 32, 36, 39 and 42.

    Do you know how can I do it with LinQ?

    Thursday, November 28, 2013 6:09 PM

Answers

  • Hello,

    >>How can I do to get that messages without repetition?

    I am confused that why you think these messages are repeated. Even messages with message Id 32 and 33, their sendto column are different values.

    I do not know why you have thought they are repeated.

    If you persist in using one table, then you have to lose the sendto column in the result set using codes like below:

                    var msgs = from message in context.Messages
    
                               where message.TalkId == talkId &&
    
                                   (message.SentBy == userId || message.SentTo == userId)
    
                               orderby message.DateUtcSent
    
                               group message by new { message.DateUtcSent, message.SendBy, message.Read, message.Title, message.Body, message.File, message.Type, message.fState, message.TalkId } into messagenew
    
                               select new { message.Key.DateUtcSent, message.Key.SendBy, message.Key.Read, message.Key.Title, message.Key.Body, message.Key.File, message.Key.Type, message.Key.fState, message.Key.TalkId };

    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.


    Friday, November 29, 2013 2:40 AM
    Moderator

All replies

  • Hello,

    >>How can I do to get that messages without repetition?

    I am confused that why you think these messages are repeated. Even messages with message Id 32 and 33, their sendto column are different values.

    I do not know why you have thought they are repeated.

    If you persist in using one table, then you have to lose the sendto column in the result set using codes like below:

                    var msgs = from message in context.Messages
    
                               where message.TalkId == talkId &&
    
                                   (message.SentBy == userId || message.SentTo == userId)
    
                               orderby message.DateUtcSent
    
                               group message by new { message.DateUtcSent, message.SendBy, message.Read, message.Title, message.Body, message.File, message.Type, message.fState, message.TalkId } into messagenew
    
                               select new { message.Key.DateUtcSent, message.Key.SendBy, message.Key.Read, message.Key.Title, message.Key.Body, message.Key.File, message.Key.Type, message.Key.fState, message.Key.TalkId };

    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.


    Friday, November 29, 2013 2:40 AM
    Moderator
  • Thnaks for your answer.

    Why do you mean with: 'If you persist in using one table'?

    Friday, November 29, 2013 6:31 AM
  • Hi,

    In my opinion, I think it is better that one table for "SendBy" and anther table for "SnedTo" to log the records.

    And I updated my first post, the message.SentTo should not be included.

    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.


    Friday, November 29, 2013 6:34 AM
    Moderator