Answered by:
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?
- Edited by Bassam Issac Wassouf Tuesday, June 12, 2012 1:35 AM
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
- Marked as answer by Allen_MSDN Monday, June 18, 2012 2:23 AM
Tuesday, June 12, 2012 8:35 AM
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
- Marked as answer by Allen_MSDN Monday, June 18, 2012 2:23 AM
Tuesday, June 12, 2012 8:35 AM -
Thank you; that was very helpfull.Tuesday, June 19, 2012 7:54 PM