none
How is the Max operation handled RRS feed

  • Question

  • Hi!

    I have written the following query that I thought should resolve in database but it seems as if the Max operation is performed in the data layer. Is that due to some incorrect setting on my part or is it how it works? If the underlying table is large the operation gets very time consuming. I am using EF 4.4.

    The following statement:

    var date = portfolioContext.Portfolio.First(p => p.PortfolioId == portfolioId).Holding.Max(h => h.Date);

    results in the following database calls:

    exec sp_executesql N'SELECT TOP (1) 
    [Extent1].[PortfolioId] AS [PortfolioId], 
    [Extent1].[PortfolioName] AS [PortfolioName], 
    [Extent1].[StartDate] AS [StartDate], 
    [Extent1].[EndDate] AS [EndDate], 
    [Extent1].[FundId] AS [FundId], 
    [Extent1].[Comment] AS [Comment], 
    [Extent1].[InsuranceId] AS [InsuranceId]
    FROM [SafetyPortfolio].[tblTPPortfolioNames] AS [Extent1]
    WHERE [Extent1].[PortfolioId] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=177
    
    exec sp_executesql N'SELECT 
    [Extent1].[PortfolioId] AS [PortfolioId], 
    [Extent1].[InstrumentId] AS [InstrumentId], 
    [Extent1].[Quantity] AS [Quantity], 
    [Extent1].[Price] AS [Price], 
    [Extent1].[FXRate] AS [FXRate], 
    [Extent1].[PortfolioShare] AS [PortfolioShare], 
    [Extent1].[Date] AS [Date]
    FROM [SafetyPortfolio].[Holding] AS [Extent1]
    WHERE [Extent1].[PortfolioId] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=177
    Thanks in advance!
    Monday, October 28, 2013 12:24 PM

Answers

  • Hello,

    >>it seems as if the Max operation is performed in the data layer

    Yes, we can see that all the 12 Max method will return the IEnumerable type rather than IQuerable type.

    As we know,

    For IEnumerable:

    1.  While query data from database, IEnumerable execute select query on server side, load data in-memory on client side and then filter data.

    For IQuerable:

    1. While query data from database, IQueryable execute select query on server side with all filters.

    So that is why the Max operation is performed in the data layer.

    And for using Max() in linq, please have a look at the link below:

    http://stackoverflow.com/questions/3197529/how-to-achieve-group-results-by-max-date-in-entity-framework

    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.

    Tuesday, October 29, 2013 1:52 AM
    Moderator