There is one big difference between these two methods: if you query the database each time you need the data you will get the most recent data, whereas if you read in all the data in one go and reuse it then you will not see the newest changes until you read everything in again. Both systems have advantages and disadvantages, but you need to be aware of this difference.
Regarding performance differences - don't assume that LINQ to Objects on local data will always be faster than a database query. Databases are incredibly well optimized for different types of queries and can take advantage of indexes. LINQ to Object queries generally just iterate over the entire data set. So even if you have the data locally unless you make an effort to index the data yourself some queries might actually be slower than if you get the database to do the work.
Even for queries where indexes can't be used databases can still beat a naive LINQ to Objects approach. Database have some very advanced algorithms that aren't implemented in LINQ to Objects. For example, a common query is to fetch the top 100 items sorted by some criteria with a filter. Even without a usable index for a sufficient large result set the database might still outperform LINQ to Objects because
OrderBy(x => x.Foo).Take(100) will first perform an O(n log n) sort and then afterwards take the first hundred elements and discard the rest. The SQL Server team knows that this type of query is common and so they added a special optimization call TOP N SORT which can perform this operation in O(n) time. I imagine that Oracle has a similar optimization. I have written another answer that goes into more details on this point, including some performance measurements of a LINQ to SQL versus a LINQ to Objects query.
- LINQ Performance for Large Collections
- LINQ Source Code Available
- creating Linq to sqlite dbml from DbLinq source code
- Poor performance comparing collections of objects using reflections and Linq Except/Intersect
- LINQ to SQL / LINQ to Collections Performance
- Low performance with linq and INTERSECT/EXCEPT in small collections
- source code for LINQ 101 samples
- c# Linq or code to extract groups from a single list of source data
- How to improve linq performance with large collections (Code first POCO class)
- Convert string to int in one line of code using LINQ
- Code equivalent to the 'let' keyword in chained LINQ extension method calls
- LINQ Ring: Any() vs Contains() for Huge Collections
- Linq code to select one item
- LINQ performance FAQ
- How to use LINQ with dynamic collections
- How are people unit testing code that uses Linq to SQL
- linq question: querying nested collections
- How to merge a collection of collections in Linq
- Performance of LINQ Any vs FirstOrDefault != null
- foreach + break vs linq FirstOrDefault performance difference
- For vs. Linq - Performance vs. Future
- What is the Efficiency and Performance of LINQ and Lambda Expression in .Net?
- LINQ performance Count vs Where and Count
- Aggregate vs Sum Performance in LINQ
- LINQ Max extension method gives an error on empty collections
- How to concatenate two collections by index in LINQ
- Is multiple .Where() statements in LINQ a performance issue?
- Linq ToList/ToArray/ToDictionary performance
- SQL vs LINQ performance
- Match elements between 2 collections with Linq in c#
- how to turn a string into a linq expression?
- Dynamic Linq Datetime where
- Is there a good LINQ way to do a cartesian product?
- How can I select all in a linq statement?
- Adding items to sub-array of an array doesn't work
- Algorithm to calculate nearest location based on longitude & latitude
- When does LINQ execute an updated data list?
- Linq Query For AutoComplete Match With Multiple Columns C#
- Linq "join" with a IList<T> getting "Error Unable to create a constant value.."
- Necessary files to deploy for website using linq
- how to get selected item in CheckBoxList in Asp.net
- ToSkip elements on C# circular list
- combining these two methods into single one
- Linq and var keyword
- Linq query-dateOfBirth to age
- Un-Nesting List Iterations for Performance
- How to use ToList() to print multiple list items?
- Save linq query as data table
- Proper way to construct linq queries to achieve fastest performance?
- Linq outer join and group by with count in select