none
LINQ To SQL distinct RRS feed

  • Question

  • I am very new to LINQ but I have added two tables to my DataContext and they are joined on their uniqueidentifer columns. The two tables are define below:

    Profiles
    UserID    uniqueidentifer (primary key)
    FullName varchar(50)

    Blogs
    BlogID     int ( primary key )
    AuthorID uniqueidentifer - (tied to the Profiles.UserID)

    Now what I am trying to do is populate a combo box with a list of all the Blog authors full name. Obviously I dont want the same name listed twice I am having trouble understanding how to specify that I want the records to be distinct on the Blogs.AuthorID column.

    Here is my code...

        WebDBDataContext dc = new WebDBDataContext();
    
    
    
        var blogs = (from b in dc.Blogs
    
            select b).Distinct() ;
    
    
    
        ddlAuthor.ClearSelection();
    
    
    
        if (blogs != null)
    
        {
    
         foreach ( Blog blog in blogs)
    
         {
    
          ddlAuthor.Items.Add(blog.Profile.FullName);
    
         }
    
        }
    
    

     Lastly I assume it is clear I am trying to select all the columns in those tables but only filter / distinct on one of the columns.

    I dont want to do a query that will only return just the distinct AuthorIDs and nothing else so please dont post that as an example. I want to do one query that returns all coulmns.

     

     

     

     


    Anthony
    • Edited by Anthony Jowers Friday, April 23, 2010 4:53 AM clarification
    Friday, April 23, 2010 4:43 AM

Answers

  • I assume you put 'AuthorID' and not 'Author' as I put in my sample.

    You should create a relationship between the two classes so that Blog gets an 'Author' property that points to the instance of the author object thus preventing you from having to write code to take the authorid and go and lookup an author object every time.

    If you have a foreign key relationship set up in the tables in SQL then you will find the LINQ to SQL designer automatically creates such an association when you drag them in (although it may default to 'Profile' - I forget).

    [)amien

    Friday, April 23, 2010 6:09 AM
    Moderator

All replies

  • You've specified Distinct on the blogs themselves which isn't what you want to as you want distinct authors.

    You could distinct the authors themselves or go directly to dc.Profiles.

    Try this:

    WebDBDataContext dc = new WebDBDataContext();
    
        ddlAuthor.ClearSelection();
    
         foreach (var author in dc.Blogs.Select(b => b.Author).Distinct())
         {
          ddlAuthor.Items.Add(author.FullName);
         }
        }

    [)amien

    Friday, April 23, 2010 4:48 AM
    Moderator
  • I think that is close but the C# editor is telling me that the resulting var author is a Guid, not a Blog record, but just the AuthorID, which is what I was getting when I tried to select the AuthorID to do my distinct on.

    I know I am new at this but it has to be common sense that you must specify what coulmns you are "selecting" separte from what column you want to be "distinct". In every example on the internet they always specify only the one coulmn to be distinct which also means their result set is only that one coulmn. Surely LINQ is better than that?


    Anthony
    Friday, April 23, 2010 5:05 AM
  • I assume you put 'AuthorID' and not 'Author' as I put in my sample.

    You should create a relationship between the two classes so that Blog gets an 'Author' property that points to the instance of the author object thus preventing you from having to write code to take the authorid and go and lookup an author object every time.

    If you have a foreign key relationship set up in the tables in SQL then you will find the LINQ to SQL designer automatically creates such an association when you drag them in (although it may default to 'Profile' - I forget).

    [)amien

    Friday, April 23, 2010 6:09 AM
    Moderator
  • Hi Anthony,

     

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, April 29, 2010 9:30 AM
    Moderator