I honestly don't see anything particularly wrong with that Linq query, but there are a number of other things you should consider:
- Converting the query to a
List<T> forces immediate evaluation of the query and requires the allocation of an single, contiguous portion of memory to store the results. If there are several thousand records, this can be fairly costly. If you later filter / re-order / group this list, it must be done in memory, which is usually much slower than allowing the database to filter / re-order / group the results. Generally, you should try to put off evaluating results until as late as possible.
- Even if you're not doing any further processing on the list, it may not be necessary to actually store the result set as a
List<T>. You really should only use a
List<T> when you need to be able to add or remove items from the result set dynamically. If you only need to be able to access elements in the result set by index (particularly in O(n) time), I'd recommend using an array instead (with
ToArray). If all you need to do is iterate through the result set one item at a time, I'd strongly recommend using an
IEnumerable<T> instead (with
AsEnumerable). This way, your application doesn't need to pull all items in at once, and it doesn't have to allocate a single, contiguous portion of memory to store them.
- If you've done all you can to speed up the C# code, and it still runs slowly, you should look at optimizing the database—your code can't run any faster than the back-end database, after all. Perhaps you need to add an index to the
item_parent column (and probably a foreign key as well).
If you've setup a proper navigation property you could write your query a bit more cleanly, although I doubt this would run any faster, since this will still translate to an
INNER JOIN in SQL:
List<myItem> items =
(from x in db.myItems
Or in fluent syntax:
List<myItem> items = db.myItems
.Where(x => !x.tblParent.hidden)
.OrderBy(x => x.name)