Well to start with, I'd find out what query is being generated (in LINQ to SQL you'd set the Log on the data context) and then profile it in SQL Server Management Studio. Play with it there until you've found something that is fast enough (either by changing the query or adding indexes) and if you've had to change the query, work out how to represent that in LINQ.
I suspect the problem is that you're combining
Take - which means it potentially needs to find out all the results in order to work out which the top 100 would look like. Is
Code indexed? If not, try indexing that - it may help by allowing the server to consider records in the order in which they'd be returned, so it can stop after it's found 100 records. You should look at indexes for the other columns too.
Take(100) translates to "Select Top 100" etc. This would help if your problem was an otherwise huge result set, where there are a lot of columns returned. I bet though that your problem is a table scan resulting from the query. In this case,
.Take(100) might not help much at all.
So, the likely culprit is the same as if you were doing SQL using ADO.NET: How are your Indxes? Are the fields being searched fields for which you don't have good indexes? This would cause a drastic decrease in performance compared to queries that do utilize good indexes. Add an index that includes
Name and see what happens. Not using an index for
Code is guaranteed to hose you, because of the
Order By. Also, what field links Genealogy_Accounts and Genealogy_AccountClass? A lack of index on either table could hose things. (I would guess an index including Searchable is unlikely to help.)
Use SQL Profiler to see the actual query being run (though you can do this in VS too), and to see how bad it really is on the server.
The problem might be LINQ doing something stupid generating the query, but this is probably not the case. We're finding LINQ-to-SQL often makes better queries than we do. Even if it looks goofy, it's usually very efficient. You can put the SQL in Query Analyzer, and check out the query plan. Then rewrite the SQL to be more human-simple and see if it improve things -- I bet it won't. I think you'll still see a table scan, indicating something is wrong with your index.
- Enumerable.Empty<T>().AsQueryable(); This method supports the LINQ to Entities infrastructure and is not intended to be used directly from your code
- Does this LINQ code perform multiple lookups on the original data?
- Shorten this LINQ query via the help of an anonymous type?
- LINQ Source Code Available
- How does this linq code that splits a sequence work?
- multiple orderby in this linq code
- How can I combine this code into one or two LINQ queries?
- How can I further simplify this piece of LINQ code
- Is there a bug in this code from 101 LINQ Samples on MSDN? (Update: Fixed)
- C# Linq query help removing foreach loops creating cleaner code
- LINQ approach to this code
- Linq expression. Help mimize code
- creating Linq to sqlite dbml from DbLinq source code
- How do i convert this linq code to inline sql
- I need help speeding up this EF LINQ query
- Need help turning this LINQ expression into LINQ statement
- why does this linq code get exponentially slower when applying First() to projection?
- How to convert this recursive code to Linq
- Using Linq to build a graph class; can you make this code look better?
- How to write this code using the Linq Extension Method-Syntax?
- Convert this LINQ code back to a Loop (or Why is this object sometimes null)
- How can I refactor this code for LINQ filtering?
- Why this Linq code always throws System.StackOverflowException?
- Need help rewriting this Linq query to move it from code-behind to DAL as reusable object
- My code is very inefficient for this simple Linq usage
- How I change this code to be in linq style
- Why the extension method of where for LINQ in this code would print out a single number while it shouldn't print anything at all?
- Can someone help me improve this linq
- Linq - how to convert this code to linq
- Get attribute values
- LINQ: dictionary from string
- How to Convert a LINQ result to DATATABLE?
- Fetching data from 50+ tables using Linq-to-Nhibernate
- Unexpected Result LINQ Where Clause
- Linq ToList/ToArray/ToDictionary performance
- Expose Foreign key through fluent API
- return a list of objects ordered by number of items in navigation property with linq to entity
- How to format a string inside an array?
- Filtering child collection with subquery
- Entity Framework/LINQ - Returning data transfer objects from a large nested entity data set
- Filter by nullable datetime field using Linq
- Linq Joining two tables in a VM with GroupBy & OrderByDescending
- LINQ Sort Distinct with Where clause
- Does using AsNoTracking() make a difference when only returning fields?
- How to query the first entry in each group in NHibernate
- How to do a Conditional Join in Sql using Linq in C#?
- Parsing Conditional Expressions to String
- FirstOrDefault() throws Object reference not set to an instance of an object error
- Automapping error: The type does not have a default constructor