Entity Framework calling database SQL string functions using Linq?

Question

If I want to call the SQL Server function Contains(field,value) using Linq, how can I accomplish this?

Doing something like:

var result = context.Documents.Where(d => d.SomeField.Contains(param)).ToList();

Does not use the Contains function in the generated SQL query. The field is a textfield with full text search enabled.

I know there should be some SQL Server string functions already mapped somewhere in the framework. Is Contains one of those and where can I find them?

Answer 1

You could try using Store Functions. Ladislav Mrnka explains them well over at this question.

Basically, you'll define the Contains in the EDMX and tell EF how it maps to something that SQL Server understands (note: in my example, I call this FullTextContains so it's not confused with the Contains method in C# - I'm not sure if this is necessary or not):

<Function Name="FullTextContains" ReturnType="Edm.String">
  <Parameter Name="field" Type="Edm.String" />
  <Parameter Name="value" Type="Edm.String" />
  <DefiningExpression>
    Contains(field, value)
  </DefiningExpression>
</Function>

Then you make a stub method in C# so you can call it via Linq:

public static class EdmFunctions
{
    [EdmFunction("YourModel", "FullTextContains")]
    public static string FullTextContains(string field, string value)
    {
        throw new NotSupportedException("This function is only for L2E query.");
    }
}

Then you'll be able to use that method in a Linq query and EF should translate it correctly.