locked
Supressing duplicate values from Query? RRS feed

  • Question

  • Hello all...

    I've clearly hit the wall in my noobish understanding of LINQ to SQL :S and I hope someone will help me through this.  I'm doing a query in which I've joined 3 tables to extract my results and it works (as far as I can tell) just fine.  However, one field ("UserName") returns repeating values, as I expected for each entry associated with that name.  Hopefully I'm explaining this properly...  So I'm trying to "suppress" the repeats so that my listview will ultimately have a name, followed by the results for that name rather than the name repeating x number of times.  I've been fiddling around with "distinct" which I though was the answer but so far no joy.  My query is as follows:



    var friendQuery = from connects in fw.Connections

    whereconnects.ConnectsTo == Membership.GetUser().ProviderUserKey.ToString()

    joinwants in fw.Wants onconnects.MemberID equalswants.UserID

    join users in fw.aspnet_Users on wants.UserID equals users.UserId.ToString()

    where connects.InGroup == wants.VisibleTo

    orderby users.UserName, wants.DateCreated descending

    select new

    {                                 
    users.UserName,
    wants.Category,wants.DateCreated,wants.Commments,wants.PostID  

    };       

    I'd be very grateful if someone could point me in the right direction!

    Thanks for any help!                






    Monday, February 27, 2012 6:56 PM

All replies

  • Could you please post your expected output?
    Tuesday, February 28, 2012 9:08 AM
  • Hi PaulBinCT,

    Welcome!

    I think you can Composite joins:

    var friendQuery = (from connects in fw.Connections
    join wants in fw.Wants on new{ID=connects.MemberID, Group=connects.InGroup} equals new{ID=wants.UserID,Group=VisibleTo}
    join users in fw.aspnet_Users on wants.UserID equals users.UserId.ToString()
    where connects.ConnectsTo == Membership.GetUser().ProviderUserKey.ToString() 
    orderby users.UserName, wants.DateCreated descending
    select new
    {                                  users.UserName,wants.Category,wants.DateCreated,wants.Commments,wants.PostID  
     
    }).Distinct();  
    
    Have a nice day.

    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, February 28, 2012 9:15 AM
  • Hi Alan...

    Thank you very very much for taking the time to construct a suggestion!  However it gives me the same result I have, although probably with better performance.  I did make one change to your code, since I don't entirely understand it, I hope I was correct.  Here you had:

    equals new{ID=wants.UserID,Group=VisibleTo} it was flagged, so I changed it to:
    equals new{ID=wants.UserID,Group=wants.VisibleTo}

    In the case of both of our queries, the output I'm getting (which I expected) is in the form of:

    John Doe, Category, Date, Comments

    John Doe, Category, Date, Comments

    John Doe, Category, Date, Comments

    What I'm hoping to get is:

    John Doe, Category, Date, Comments

                    Category, Date, Comments

                    Category, Date, Comments

    Jane Doe,  Category, Date, Comments

                    Category, Date, Comments

    Again, thank you for your efforts on my behalf.  I know there are many others asking for help, if you can give my problem any more thought I'd be very grateful!

    Paul

    Tuesday, February 28, 2012 2:00 PM
  • Hi Paul,

    I think you should group by your result:

    var friendQuery = (from i in
     (from connects in fw.Connections
    join wants in fw.Wants on new{ID=connects.MemberID, Group=connects.InGroup} equals new{ID=wants.UserID,Group=wants.VisibleTo}
    join users in fw.aspnet_Users on wants.UserID equals users.UserId.ToString()
    where connects.ConnectsTo == Membership.GetUser().ProviderUserKey.ToString() 
    orderby users.UserName, wants.DateCreated descending
    select new
    { 
    users.UserName,
    wants.Category,
    wants.DateCreated,
    wants.Commments,
    wants.PostID  
    })
    group i by {i.UserName} into g select 
    new
    {
    g.Key,List=g.ToList()
    }).Distinct(); 

    Have a nice day.

    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 29, 2012 3:10 AM
  • Hi,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions?
    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, March 2, 2012 9:04 AM
  • Hi Alan!

    First of all, thank you for your kindness in keeping track of my problem!  I apologize for not replying sooner, I was distracted by another unrelated section of work.  I tried your last suggestion and it won't compile at

    group i by {i.UserName} into g
                select new
    {
    g.Key,List=g.ToList()
    }).Distinct(); 

    And frankly I'm not experienced enough with what you're doing to troubleshoot it.  I just thought that rather than make you crazy(crazier ;) ) with my dilemma, I'd live with it and call it a feature... ;)

    Although I'd be happy to hear any other ideas you have...  and thank you again for all your time and efforts!

    Paul



    Friday, March 2, 2012 3:26 PM