3

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?

Roger Johansson
  • 22,764
  • 18
  • 97
  • 193

1 Answers1

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.

Community
  • 1
  • 1
Stephen McDaniel
  • 2,938
  • 2
  • 24
  • 53
  • For some reason it explodes, I've even tried to remove the parameters and hardcoded constants into the defining expression.. but it still says the generated sql is incorrect... ideas? – Roger Johansson Aug 30 '11 at 08:58
  • 1
    I expect it explodes because `CONTAINS` in SQL server is not a function. It is a predicate. Try to wrap it in custom function. – Ladislav Mrnka Aug 30 '11 at 09:07