Entity Framework String Size Limitation on Contains Clause

Question

Entity Framework String Size Limitation on Contains Clause

asp.net-mvc c# entity-framework entity-framework-6

Question

So, I have this lambda expression and it works just fine

list = list.Where(x => x.ListaDocumentoCaixa.Any(d => d.Observacao.Contains(term.Trim())));

I must add that this column is a varchar(6000) field. So far, this has been working just fine as I mentioned, but just recently I've ran into an issue. It seems that if the term of the search occurs from position 4001 of the string and on, the query fails to return anything to me.

After some debbuging I've found this commented on the query produced by Entity Framework

-- p__linq__0: 'maria stela gonsa' (Type = String, Size = 4000)

Then after some research I found this to be Entity's common behaviour, however, I can't have this kind of limitation on the application. My question is: Is there any way to change this behaviour ? I would like very much to avoid having to write this query as plain text and run this with ExecuteQuery if possible.

Thanks in advance for the help!

Raoni Medinilha

View more on Stack Overflow

Kevin Hirst · Accepted Answer · 9/20/2017 1:35:16 PM

I would recommend you follow the following article, assuming you are using SQL server, about how to create a full text search index, and use it in Entity Framework with C#.

Running LIKE statements (which is what Contains() maps to) is HIGHLY inefficient on large varchar fields.

https://www.mikesdotnetting.com/article/298/implementing-sql-server-full-text-search-in-an-asp-net-mvc-web-application-with-entity-framework

EDIT: The summary of the link is:

1.) Create a full text index on the field using SQL server's wizard. That full text field will allow CONTAINS and FREETEXT searches on the whole field, and be much more efficient.

2.) Write a stored procedure that joins the table in question to results from the free text index.

3.) Make an Entity Framework class to represent results from that stored procedure, and use EF to call in and return a list of those results.

Entity Framework String Size Limitation on Contains Clause

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related