In addition to delaying actualising the collection as late as possible, also try to do all filtering (.Where) before all transforms (.Select) where possible. Flipping them will create more expensive queries which retrieve more data into memory on the server before filtering the set down.
When you really need to actualise, prefer .ToArray over .ToList. Arrays are lighter because they have less functionality, being of fixed size. Your code then also communicates to other parts of the code that the collection is not modifiable, unlike lists, which invite consumers to add or remove items from the list. This pattern is a side-effect of one of the tenets of functional programming: preferring immutability wherever possible.
Get to grips with .Aggregate. Correctly-structured .Aggregate calls can be run in SQL at the server-side, saving the amount of data to be brought across the wire (much like how good filtering - .Where - also helps). It's a balancing act though -- if you're getting information for a desktop app, it's often better to rather retrieve more data from a local datastore (eg SQL server on the network) than get the SQL server to do all the work. The best advice I can give here is to profile different approaches and find the one with the optimal outcome.
Beware refactoring within your LINQ queries -- whilst you may be able to pull out methods which work in pure LINQ, you'll find that they won't work when translated to SQL because the translation will literally contain the names of your refactored methods.
Please do test your LINQ-to-SQL queries against the actual data source you're going to use. Don't mock them (eg NSubstitute). Don't run them against a different engine (eg sqlite) for testing. There are differences in what LINQ code can be converted to SQL for different targets. For example: SQLCE does not support the LOWER function, so having LINQ-to-SQL which uses .ToLower won't work against SQLCE, but will work against SQL server. If you want to test against a local variant of SQL server, LocalDb is exactly the same engine, so use that.
Shameless plug: I have a bunch of nuget packages for providing temporary databases, specifically for testing. They implement the IDisposable interface, so you can, for example:
using(vardb=newTempDBLocalDb()){// in this block, you may attach your EF context to `db` and// perform queries. You will have to run any migrations necessary// to set up the required schema, if you're using a migration// strategy like EF migrations or FluentMigrator. If you're not,// you can always run in a schema script (or pass one or more// schema scripts to the constructor above)}
Good article; some thoughts, if I may:
In addition to delaying actualising the collection as late as possible, also try to do all filtering (
.Where) before all transforms (.Select) where possible. Flipping them will create more expensive queries which retrieve more data into memory on the server before filtering the set down.When you really need to actualise, prefer
.ToArrayover.ToList. Arrays are lighter because they have less functionality, being of fixed size. Your code then also communicates to other parts of the code that the collection is not modifiable, unlike lists, which invite consumers to add or remove items from the list. This pattern is a side-effect of one of the tenets of functional programming: preferring immutability wherever possible.Get to grips with
.Aggregate. Correctly-structured.Aggregatecalls can be run in SQL at the server-side, saving the amount of data to be brought across the wire (much like how good filtering -.Where- also helps). It's a balancing act though -- if you're getting information for a desktop app, it's often better to rather retrieve more data from a local datastore (eg SQL server on the network) than get the SQL server to do all the work. The best advice I can give here is to profile different approaches and find the one with the optimal outcome.Beware refactoring within your LINQ queries -- whilst you may be able to pull out methods which work in pure LINQ, you'll find that they won't work when translated to SQL because the translation will literally contain the names of your refactored methods.
Please do test your LINQ-to-SQL queries against the actual data source you're going to use. Don't mock them (eg
NSubstitute). Don't run them against a different engine (eg sqlite) for testing. There are differences in what LINQ code can be converted to SQL for different targets. For example: SQLCE does not support theLOWERfunction, so having LINQ-to-SQL which uses.ToLowerwon't work against SQLCE, but will work against SQL server. If you want to test against a local variant of SQL server, LocalDb is exactly the same engine, so use that.Shameless plug: I have a bunch of nuget packages for providing temporary databases, specifically for testing. They implement the
IDisposableinterface, so you can, for example:Thank you so much for your insight. I'll definitely take these into consideration going forward.