none
Problem with SELECT DISTINCT TOP RRS feed

  • Question

  • Hello,

    i have a problem with a LINQ Query.

    I want to have something like that

    var query = (asd).Take(100).Distinct()

    so that i only get a small amount of all results.

     

    I did notice that it does

    SELECT TOP (100) asd

    FROM (

    SELECT DISTINCT xxx

    )

    So the problem is that the inner query with the distinct could habe millions of lines and it causes a timeout.

    Is there any way to get a query like SELECT DISTINCT TOP 100 xxx ?

    Regards,

    Joe

    Wednesday, March 18, 2015 3:10 PM

Answers

  • I think you will have to do 2 queries to accomplish your problem.

    The first query (sorry in VB but same in C# except for the syntax)

            Dim dc As New DataClasses1DataContext
            Dim q = From rs In dc.RSongInfos Select rs.Artist Distinct

    The second would be:

            Dim r = (From rs2 In q Select rs2 Take 100).ToString

    the ToString is there only to show the generated query which is:

    SELECT TOP (100) [t1].[Artist]
    FROM (
        SELECT DISTINCT [t0].[Artist]
        FROM [dbo].[RSongInfo] AS [t0]
        ) AS [t1]

    As you can see it will generate one query that will execute.  

    If your problem is that the original set is too big then you need to switch the order of the two queries.

    That will generate:

    SELECT DISTINCT [t1].[Artist]
    FROM (
        SELECT TOP (100) [t0].[Artist]
        FROM [dbo].[RSongInfo] AS [t0]
        ) AS [t1]
    


    Lloyd Sheen


    Wednesday, March 18, 2015 3:39 PM
  • Hello Joe,

    >>Is there any way to get a query like SELECT DISTINCT TOP 100 xxx ?

    It seems that you are using LINQ to SQL provider, for what you want, you could have a try with LINQ to Entities based on Entity Framework provider, this provider is smart enough to firstly make a query against database, for a LINQ query similar with yours:

    var result1 = db.OrderDetails.Take(100).Distinct().ToList();

    It would generate the sql query as below to, since the returned result contains primary key, it will ignore the Distinct operation:

    SELECT TOP (100) 
    
        [c].[OrderDetailID] AS [OrderDetailID], 
    
        [c].[OrderDetailName] AS [OrderDetailName], 
    
        [c].[OrderID] AS [OrderID], 
    
        [c].[Count] AS [Count]
    
    FROM [dbo].[OrderDetail] AS [c]
    

    And if specifying not a primary column, it would be smart enough to make the TOP query as the sub query:

    var result1 = db.OrderDetails.Select(o=>o.OrderDetailName).Take(100).Distinct();

    SQL:

    SELECT 
    
        [Distinct1].[OrderDetailName] AS [OrderDetailName]
    
        FROM ( SELECT DISTINCT [distinct].[OrderDetailName] AS [OrderDetailName]
    
            FROM ( SELECT TOP (100) 
    
                [c].[OrderDetailName] AS [OrderDetailName]
    
                FROM [dbo].[OrderDetail] AS [c]
    
            )  AS [distinct]
    
        )  AS [Distinct1]
    

    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, March 19, 2015 6:27 AM
    Moderator

All replies

  • I think you will have to do 2 queries to accomplish your problem.

    The first query (sorry in VB but same in C# except for the syntax)

            Dim dc As New DataClasses1DataContext
            Dim q = From rs In dc.RSongInfos Select rs.Artist Distinct

    The second would be:

            Dim r = (From rs2 In q Select rs2 Take 100).ToString

    the ToString is there only to show the generated query which is:

    SELECT TOP (100) [t1].[Artist]
    FROM (
        SELECT DISTINCT [t0].[Artist]
        FROM [dbo].[RSongInfo] AS [t0]
        ) AS [t1]

    As you can see it will generate one query that will execute.  

    If your problem is that the original set is too big then you need to switch the order of the two queries.

    That will generate:

    SELECT DISTINCT [t1].[Artist]
    FROM (
        SELECT TOP (100) [t0].[Artist]
        FROM [dbo].[RSongInfo] AS [t0]
        ) AS [t1]
    


    Lloyd Sheen


    Wednesday, March 18, 2015 3:39 PM
  • Hello Joe,

    >>Is there any way to get a query like SELECT DISTINCT TOP 100 xxx ?

    It seems that you are using LINQ to SQL provider, for what you want, you could have a try with LINQ to Entities based on Entity Framework provider, this provider is smart enough to firstly make a query against database, for a LINQ query similar with yours:

    var result1 = db.OrderDetails.Take(100).Distinct().ToList();

    It would generate the sql query as below to, since the returned result contains primary key, it will ignore the Distinct operation:

    SELECT TOP (100) 
    
        [c].[OrderDetailID] AS [OrderDetailID], 
    
        [c].[OrderDetailName] AS [OrderDetailName], 
    
        [c].[OrderID] AS [OrderID], 
    
        [c].[Count] AS [Count]
    
    FROM [dbo].[OrderDetail] AS [c]
    

    And if specifying not a primary column, it would be smart enough to make the TOP query as the sub query:

    var result1 = db.OrderDetails.Select(o=>o.OrderDetailName).Take(100).Distinct();

    SQL:

    SELECT 
    
        [Distinct1].[OrderDetailName] AS [OrderDetailName]
    
        FROM ( SELECT DISTINCT [distinct].[OrderDetailName] AS [OrderDetailName]
    
            FROM ( SELECT TOP (100) 
    
                [c].[OrderDetailName] AS [OrderDetailName]
    
                FROM [dbo].[OrderDetail] AS [c]
    
            )  AS [distinct]
    
        )  AS [Distinct1]
    

    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, March 19, 2015 6:27 AM
    Moderator