Filter the result after quering the database in EF

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?

Answer 1

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

Answer 2

Thank you; that was very helpfull.