none
Filter the result after quering the database in EF RRS feed

  • Question

  • After quering the database I need to filter the result disconnected mode. when I use this query that end with ToList()

               var query = (from c in context.DimCustomers
                                 join i in context.FactInternetSales on c.CustomerKey equals i.CustomerKey
                                 where c.YearlyIncome < 100000
                                 select new { c.CustomerKey, c.CommuteDistance, i.DimCurrency, c.YearlyIncome }).ToList();
    
                    var query1 = (from q in query where q.YearlyIncome == 60000 select q);

    The sql would look like this and I can filter this result on yearlyIncome disconnected mode like ( var query1 = (from q in query where q.YearlyIncome == 60000 select q);)

    SELECT 
    [Extent1].[CustomerKey] AS [CustomerKey], 
    [Extent1].[CommuteDistance] AS [CommuteDistance], 
    [Extent3].[CurrencyKey] AS [CurrencyKey], 
    [Extent3].[CurrencyAlternateKey] AS [CurrencyAlternateKey], 
    [Extent3].[CurrencyName] AS [CurrencyName], 
    [Extent1].[YearlyIncome] AS [YearlyIncome]
    FROM   [dbo].[DimCustomer] AS [Extent1]
    INNER JOIN (SELECT 
          [FactInternetSales].[ProductKey] AS [ProductKey], 
          [FactInternetSales].[OrderDateKey] AS [OrderDateKey], 
          [FactInternetSales].[DueDateKey] AS [DueDateKey], 
          [FactInternetSales].[ShipDateKey] AS [ShipDateKey], 
          [FactInternetSales].[CustomerKey] AS [CustomerKey], 
          [FactInternetSales].[PromotionKey] AS [PromotionKey], 
          [FactInternetSales].[CurrencyKey] AS [CurrencyKey], 
          [FactInternetSales].[SalesTerritoryKey] AS [SalesTerritoryKey], 
          [FactInternetSales].[SalesOrderNumber] AS [SalesOrderNumber], 
          [FactInternetSales].[SalesOrderLineNumber] AS [SalesOrderLineNumber], 
          [FactInternetSales].[RevisionNumber] AS [RevisionNumber], 
          [FactInternetSales].[OrderQuantity] AS [OrderQuantity], 
          [FactInternetSales].[UnitPrice] AS [UnitPrice], 
          [FactInternetSales].[ExtendedAmount] AS [ExtendedAmount], 
          [FactInternetSales].[UnitPriceDiscountPct] AS [UnitPriceDiscountPct], 
          [FactInternetSales].[DiscountAmount] AS [DiscountAmount], 
          [FactInternetSales].[ProductStandardCost] AS [ProductStandardCost], 
          [FactInternetSales].[TotalProductCost] AS [TotalProductCost], 
          [FactInternetSales].[SalesAmount] AS [SalesAmount], 
          [FactInternetSales].[TaxAmt] AS [TaxAmt], 
          [FactInternetSales].[Freight] AS [Freight], 
          [FactInternetSales].[CarrierTrackingNumber] AS [CarrierTrackingNumber], 
          [FactInternetSales].[CustomerPONumber] AS [CustomerPONumber]
          FROM [dbo].[FactInternetSales] AS [FactInternetSales]) AS [Extent2] ON [Extent1].[CustomerKey] = [Extent2].[CustomerKey]
    LEFT OUTER JOIN [dbo].[DimCurrency] AS [Extent3] ON [Extent2].[CurrencyKey] = [Extent3].[CurrencyKey]
    WHERE [Extent1].[YearlyIncome] < cast(100000 as decimal(18))

    but when not using ToList();

    the sql look like this

    SELECT 
    [Extent1].[CustomerKey] AS [CustomerKey], 
    [Extent1].[CommuteDistance] AS [CommuteDistance], 
    [Extent3].[CurrencyKey] AS [CurrencyKey], 
    [Extent3].[CurrencyAlternateKey] AS [CurrencyAlternateKey], 
    [Extent3].[CurrencyName] AS [CurrencyName], 
    [Extent1].[YearlyIncome] AS [YearlyIncome]
    FROM   [dbo].[DimCustomer] AS [Extent1]
    INNER JOIN (SELECT 
          [FactInternetSales].[ProductKey] AS [ProductKey], 
          [FactInternetSales].[OrderDateKey] AS [OrderDateKey], 
          [FactInternetSales].[DueDateKey] AS [DueDateKey], 
          [FactInternetSales].[ShipDateKey] AS [ShipDateKey], 
          [FactInternetSales].[CustomerKey] AS [CustomerKey], 
          [FactInternetSales].[PromotionKey] AS [PromotionKey], 
          [FactInternetSales].[CurrencyKey] AS [CurrencyKey], 
          [FactInternetSales].[SalesTerritoryKey] AS [SalesTerritoryKey], 
          [FactInternetSales].[SalesOrderNumber] AS [SalesOrderNumber], 
          [FactInternetSales].[SalesOrderLineNumber] AS [SalesOrderLineNumber], 
          [FactInternetSales].[RevisionNumber] AS [RevisionNumber], 
          [FactInternetSales].[OrderQuantity] AS [OrderQuantity], 
          [FactInternetSales].[UnitPrice] AS [UnitPrice], 
          [FactInternetSales].[ExtendedAmount] AS [ExtendedAmount], 
          [FactInternetSales].[UnitPriceDiscountPct] AS [UnitPriceDiscountPct], 
          [FactInternetSales].[DiscountAmount] AS [DiscountAmount], 
          [FactInternetSales].[ProductStandardCost] AS [ProductStandardCost], 
          [FactInternetSales].[TotalProductCost] AS [TotalProductCost], 
          [FactInternetSales].[SalesAmount] AS [SalesAmount], 
          [FactInternetSales].[TaxAmt] AS [TaxAmt], 
          [FactInternetSales].[Freight] AS [Freight], 
          [FactInternetSales].[CarrierTrackingNumber] AS [CarrierTrackingNumber], 
          [FactInternetSales].[CustomerPONumber] AS [CustomerPONumber]
          FROM [dbo].[FactInternetSales] AS [FactInternetSales]) AS [Extent2] ON [Extent1].[CustomerKey] = [Extent2].[CustomerKey]
    LEFT OUTER JOIN [dbo].[DimCurrency] AS [Extent3] ON [Extent2].[CurrencyKey] = [Extent3].[CurrencyKey]
    WHERE ([Extent1].[YearlyIncome] < cast(100000 as decimal(18))) AND (cast(60000 as decimal(18)) = [Extent1].[YearlyIncome])
    go

    is there any other solution to filter disconnectedly other than using Tolist() in first query?



    Monday, June 11, 2012 8:27 PM

Answers

  • Hi IssacW,

    Welcome to MSDN Forum.

    Please refer to this link. The section of 'Immediate Query Execution' introduces all the solutions to force the query to be executed immediately and let us to get a disconnected collection. Based on this issue, ToList is the only selection. Why don't want to use ToList?

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, June 12, 2012 8:35 AM
    Moderator

All replies

  • Hi IssacW,

    Welcome to MSDN Forum.

    Please refer to this link. The section of 'Immediate Query Execution' introduces all the solutions to force the query to be executed immediately and let us to get a disconnected collection. Based on this issue, ToList is the only selection. Why don't want to use ToList?

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, June 12, 2012 8:35 AM
    Moderator
  • Thank you; that was very helpfull.
    Tuesday, June 19, 2012 7:54 PM