none
Need help with a (complicated?) query RRS feed

  • Question

  • I am currently doing the following using multiple LINQ to SQL queries, and I'd like to compact it into a single query as much as possible to reduce execution time:

    var results1 = (from v in dc.Visits 
        where ...
        select new VisitPosition(v.Id, v.Latitude, v.Longitude)).Distinct();
    
    // This gives me a unique list of visits by id and latitude/longitude
    
    // I then iterate through these results filtering those I don't want...
    // The filters each have their own LINQ to SQL queries in them based on p.Id
    
    foreach (VisitPosition p in results1)
    {
        if (PassesFilters(p))
            newResults.add(new OtherStructure(p.Latitude, p.Longitude));
    }
    The filters are conditional, so some may be there, and some may not

    I'm finding that the iteration through the results is slow when applying the filters.  So I'd like to move the filtering back up into my query.

    One such filter:

    var results = (from i in dc.Images
        where i.Id == p.Id
        orderby i.Date descending).First();
    Gives me the latest record in the Images table by date that matches the Id from the initial query.

    I looked at

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/144df341-b6f1-459b-aaef-183014b82cd6

    but I'm not sure where to incorporate the .First() to get the single record.

    Essentially what I want is a record of unique id/latitude/longitude values from one table, along with the most recent record by date from a second table that matches the id from the first table.

    Any guidance is most appreciated.

    Thanks,
    ...Matt
    http://www.insidercoding.com/

    Wednesday, December 9, 2009 4:52 PM

Answers

  • Hello Matt,

     

    Welcome to LINQ to SQL forum!

     

    Are you looking for such a query to put the separate queries into a single one? 

    ================================================================================
                    var query = from v in db.Visits.Select(v => new { v.Id, v.Latitude, v.Longitude }).Distinct()

                                let date = (from i in db.Images

                                            where i.Id == v.Id

                                            orderby i.Date descending

                                            select i).First()

                                where date != null

                                select new { v.Latitude, v.Longitude };

    ================================================================================

    I used a Let keyword here.  You can modify this query according to your detailed scenario.

     

    Here is the T-SQL generated by this query:

    ================================================================================

    SELECT [t1].[Latitude], [t1].[Longitude]

    FROM (

        SELECT DISTINCT [t0].[Id], [t0].[Latitude], [t0].[Longitude]

        FROM [dbo].[Visit] AS [t0]

        ) AS [t1]

    WHERE EXISTS(

        SELECT TOP (1) NULL AS [EMPTY]

        FROM [dbo].[Image] AS [t2]

        WHERE [t2].[Id] = ([t1].[Id])

        ORDER BY [t2].[Date] DESC

        )
    ================================================================================

     

    If you have any questions, please feel free to let me know.

     

    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, December 10, 2009 3:36 AM
    Moderator
  • Hello Matt,

     

    I think your query is also great.  The performance is similar.   But NOTE: If there are 10 records returned.  We will have 11 database calls.  The first call is the WHERE query on the Visit table and the rest calls are WHERE queries by each Visit.Id on the Image table. 

     

    The easiest way to check the T-SQL generated by LINQ to SQL provider would be using DataContext.Log property. 

    =====================================================================================
    db.Log = Console.Out;
    =====================================================================================

     

    More references about LINQ to SQL debugging:

    http://msdn.microsoft.com/en-us/library/bb386930.aspx

    http://weblogs.asp.net/scottgu/archive/2007/07/31/linq-to-sql-debug-visualizer.aspx

     

    To check the query performance, we can use SQL Server Profiler, http://msdn.microsoft.com/en-us/library/ms181091.aspx. 

     

    If you have any questions, please feel free to let me know.

     

    Have a great 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.
    Tuesday, December 15, 2009 2:22 AM
    Moderator

All replies

  • Hello Matt,

     

    Welcome to LINQ to SQL forum!

     

    Are you looking for such a query to put the separate queries into a single one? 

    ================================================================================
                    var query = from v in db.Visits.Select(v => new { v.Id, v.Latitude, v.Longitude }).Distinct()

                                let date = (from i in db.Images

                                            where i.Id == v.Id

                                            orderby i.Date descending

                                            select i).First()

                                where date != null

                                select new { v.Latitude, v.Longitude };

    ================================================================================

    I used a Let keyword here.  You can modify this query according to your detailed scenario.

     

    Here is the T-SQL generated by this query:

    ================================================================================

    SELECT [t1].[Latitude], [t1].[Longitude]

    FROM (

        SELECT DISTINCT [t0].[Id], [t0].[Latitude], [t0].[Longitude]

        FROM [dbo].[Visit] AS [t0]

        ) AS [t1]

    WHERE EXISTS(

        SELECT TOP (1) NULL AS [EMPTY]

        FROM [dbo].[Image] AS [t2]

        WHERE [t2].[Id] = ([t1].[Id])

        ORDER BY [t2].[Date] DESC

        )
    ================================================================================

     

    If you have any questions, please feel free to let me know.

     

    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, December 10, 2009 3:36 AM
    Moderator
  • Hello Matt,

     

    How is the problem?  If you need any further assistance, please feel free to let me know.

     

    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.
    Monday, December 14, 2009 1:16 AM
    Moderator
  • Thanks Lingzhi,

    I actually was not aware of your post.

    I solved it by the following:
    var query in (from Visit in dc.Visits
        where Visit.Latitude != null && Visit.Longitude != null &&
        Visit.Latitude <= nw.Latitude && Visit.Latitude >= se.Latitude
        select new 
        { 
            Visit, 
            Image = (from i in dc.Images 
                where i.Id == Visit.Id 
                orderby i.Date descending 
                select i).First() 
        };
    However, which do you think is more efficient/faster?

    BTW, how can I get the T-SQL from a LINQ statement?

    Thanks,
    ...Matt
    Monday, December 14, 2009 2:42 PM
  • Hello Matt,

     

    I think your query is also great.  The performance is similar.   But NOTE: If there are 10 records returned.  We will have 11 database calls.  The first call is the WHERE query on the Visit table and the rest calls are WHERE queries by each Visit.Id on the Image table. 

     

    The easiest way to check the T-SQL generated by LINQ to SQL provider would be using DataContext.Log property. 

    =====================================================================================
    db.Log = Console.Out;
    =====================================================================================

     

    More references about LINQ to SQL debugging:

    http://msdn.microsoft.com/en-us/library/bb386930.aspx

    http://weblogs.asp.net/scottgu/archive/2007/07/31/linq-to-sql-debug-visualizer.aspx

     

    To check the query performance, we can use SQL Server Profiler, http://msdn.microsoft.com/en-us/library/ms181091.aspx. 

     

    If you have any questions, please feel free to let me know.

     

    Have a great 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.
    Tuesday, December 15, 2009 2:22 AM
    Moderator
  • I think your query is also great.  The performance is similar.   But NOTE: If there are 10 records returned.  We will have 11 database calls.  The first call is the WHERE query on the Visit table and the rest calls are WHERE queries by each Visit.Id on the Image table. 



    There may be 11 calls, but the performance is much better than when I was filtering myself.

    Another question though, I tried using your structure and I'm finding that there's a seperate query for each 'let' that occurs.  However, your T-SQL statement shows the let portion integrated into the single query.  Am I missing something?

    Thanks,
    ...Matt
    Thursday, December 17, 2009 2:48 PM
  • Hello Matt,

     

    I think you select the first image as well.  But my method does not put the Images in the final select projection, I select only the longitude and latitude.  If I put the Image in the selection too, my approach is similar with yours. 

     

    To decrease the database calls, we can consider using a JOIN query and make full use of the relationship between the Visit and Image tables.  Is there a foreign key between these two tables?   Please provide me with the detailed structure.  I will do my best to help you decrease the database calls. 

     

    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.
    Monday, December 21, 2009 6:10 AM
    Moderator
  • Hello Matt,

     

    How is the problem?  If you need any further assistance, please feel free to let me know.

     

    Merry Christmas!
     

    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.
    Friday, December 25, 2009 12:32 AM
    Moderator