0

I have a query in which I pass the search term to filter the list of Companies, either by Email or company Title which is stored in another table (TranslationTexts) as Text column for multiple locales.

The query runs fine but it is very heavy and takes time. How can I make it more efficient?

See Table Diagram Image

The query:

gm.ListData = context.Companies.ToList()
                     .Where(a => a.AspNetUser.Email.NullableContains(searchTerm) ||
                                 a.TitleTranslation.TranslationTexts
                                  .Where(b => b.Text.NullableContains(searchTerm)).Any()
                    ).Select(c => new ListCompany
                    {
                        CompanyID = c.CompanyID,
                        EmailID = c.AspNetUser.Email,
                        Title = c.TitleTranslation.TranslationTexts.FirstOrDefault(d => d.Locale == Locale).Text
                    }).ToList();
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mohammed Kamran Azam
  • 269
  • 2
  • 16
  • Try making a View and query against it or the alternate would be to use Include to prevent lazy loading and it behaves like joins instead of inner queries. e.g. http://stackoverflow.com/a/19319663/1433093 and http://www.entityframeworktutorial.net/EntityFramework4.3/eager-loading-with-dbcontext.aspx – Kundan Singh Chouhan Jul 31 '16 at 19:36
  • What is this `NullableContains` method? – Gert Arnold Jul 31 '16 at 21:18
  • The first call to `ToList` (`Companies.ToList`) retrieves all the companies in the database, causing the `Where` query to be run on the in-memory collection rather than on the database. This will generally be slower than having the `Where` run on the database instead. Try removing the first call to `ToList`. In addition, try including the `TranslationTexts` from the get-go (`Companies.Include(c => c.TitleTranslation.TranslationTexts).Where`). – OJ Raqueño Aug 01 '16 at 03:35
  • The NullableContains is a custom extension method which handles the string finding logic. As it is not a store expression that is why i have to call ToList on the context before i can use the extension method. I know it retrieves all the companies records then does further query, but i need to call the extension method. How can i create the views using Linq? – Mohammed Kamran Azam Aug 01 '16 at 05:04
  • The key is to turn into an expression what you do in `NullableContains`. So, what does it do? – Gert Arnold Aug 02 '16 at 13:41
  • the NullableContains code. public static bool NullableContains(this string text, string searchTerm) { if (string.IsNullOrWhiteSpace(text)) { return false; } return text.IndexOf(searchTerm, StringComparison.OrdinalIgnoreCase) >= 0; } – Mohammed Kamran Azam Aug 03 '16 at 11:56

0 Answers0