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?

Community · Accepted Answer · 2017-05-23 09:59:08Z

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.

edited May 23, 2017 at 9:59

CommunityBot

11 silver badge

answered Aug 30, 2011 at 8:25

Stephen McDaniel

2,9722 gold badges25 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Roger Johansson Over a year ago

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?

Ladislav Mrnka Over a year ago

I expect it explodes because CONTAINS in SQL server is not a function. It is a predicate. Try to wrap it in custom function.

Collectives™ on Stack Overflow

Entity Framework calling database SQL string functions using Linq?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related