Linq. Help me set it up!

Question

I have a linq request that causes some timeout problems. Basically, I have a query that returns the top 100 results from a table containing about 500,000 records.

Here is the request:

using (var dc = CreateContext())
        {
            var accounts = string.IsNullOrEmpty(searchText)
                            ? dc.Genealogy_Accounts
                                .Where(a => a.Genealogy_AccountClass.Searchable)
                                .OrderByDescending(a => a.ID)
                                .Take(100)
                            : dc.Genealogy_Accounts
                                .Where(a => (a.Code.StartsWith(searchText)
                                            || a.Name.StartsWith(searchText))
                                            && a.Genealogy_AccountClass.Searchable)
                                .OrderBy(a => a.Code)
                                .Take(100);
            return accounts.Select(a => 
        }
    }

Oddly enough, this is the first linq request that causes a timeout. I thought that by doing "Take", we would not need to scan all 500 thousand records. However, this must be what is happening. I assume that connecting to find what is “searchable” is causing the problem. I cannot denormalize tables ... so I wonder if there is a way to rewrite the linq query so that it returns faster ... or should I just write this query as a stored procedure (and if so, how it might look) . Thank.

Answer 1

, , ( LINQ to SQL Log ), SQL Server Management Studio. , -, ( , ), , , LINQ.

, , OrderBy Take - , , , 100. Code? , - , , , , 100 . .

Answer 2

Take(100) " Top 100" .. , , . , - , . .Take(100) .

, , SQL ADO.NET: Indxes? , ? , . , Code Name, , . Code - Order By. , Genealogy_Accounts Genealogy_AccountClass? . ( , , Searchable, .)

SQL Profiler, ( VS), , .

, LINQ - , , , , . , LINQ-to-SQL , . , . SQL Query Analyzer . SQL, , -, , . , - , , - .