LINQ takes 40s to end query - Performance

Question

I am making a call to an external database outside of my local network, however the query takes 40 seconds to end..

i am making the call using edmx.

String Example = "Example";
var Result = EDMXEntity.Entities
    .Where(
        x => 
        (
            x.Name.ToString().ToLower().Contains(Example.ToLower())
        ))
    .Take(50)
    .ToList();

Current code thanks to Szer:

var Result = EDMXEntity.Entities.Where(x => SqlFunctions.PatIndex(x.Name.ToString().ToLower(), Example.ToLower()) > 0).Take(50).ToList();

Answer 1

Late answer from my comment above:

Problem is that you are loading all Entities from DB to memory and filtering on your machine. You should filter your query before with help of your DB engine.

To do that you should use SqlFunctions which are mapped to direct T-SQL code by LINQ SQL provider. In your case you could replace string.Contains() to SqlFunctions.PatIndex which is almost the same (it returns int instead of bool)

Like this:

var result = EDMXEntity.Entities
   .Where(x => SqlFunctions.PatIndex(
      stringPattern: x.Name.ToString().ToLower(), 
      target:        Example.ToLower()) > 0)
   .Take(50)
   .ToList();