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!

1
0
9/20/2017 12:26:50 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

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.

0
9/20/2017 1:35:16 PM

Related Questions

Using SqlQuery<Dictionary<string, string>> in Entity Framework 6

You can use ...SqlQuery... to directly populate an object if the object has a default constructor and property setters. The result can then be used to create a dictionary. For example:...public class QueryResult { public string A { get; set; } public string B { get; set; } } ...// the colulmn/alias names need to match property names string ...
.net c# dictionary entity-framework sql
asked by jjj

Entity Framework 6 - Getting the field size

I need to grab the field size of a column in a model using Entity Framework 6 (the model was defined in the database and the field size is not known at run-time)....I see some sample code for EF4/5 here:...entity framework 5 MaxLength...But unfortunately, this code is broken in EF6:... var test = objectContext.MetadataWorkspace.GetItems(DataSpace.C...
c# entity-framework entity-framework-6
asked by Andy Midd

Forcing Case Insensitive String.Contains in Entity Framework Core

Is there any way to force LINQ to SQL to perform a case-insensitive Contains?...I'm working with PostgreSQL on Entity Framework Core.
.net c# case-insensitive entity-framework-core string
asked by SuperJMN

Entity Framework Using a Where Clause on a Nested Entity

I'm trying to filter child objects on the retrieval using the following...var loanExtractExample = be.LoanCategoryMonitors .Where(lcm => lcm.LoanNumber == 5000435) .Include(l => l.Loan) .Include(le => le.LoanExtracts) .Select(le => le.LoanExtracts.Borrowers.Where(b => b.BorrowerNum == ...
c# entity-framework entity-framework-6
asked by Tim

Entity Framework is it possible to generate string pattern value on adding entity to database?

I have a model...public User { public long Id {get;set;} public string UniqueUserId{get;set;} public string Company {get;set;} public string City {get;set;} } ...here Id is primary key with auto increment, Company and City which are the values entered by user. UniqueUserId is pattern value. its 16 digit value. It contains 1st 4 cha...
asp.net-core c# database entity-framework entity-framework-core
asked by Ajit Hegde

Entity Framework Core incorrect field size

The problem is the ...type: "varchar"... argument to all these ...table.Column<string>... calls. When this parameter is supplied, ...maxLength... and some other parameters are ignored by the migration SQL generator. Because the ...type... is supposed to be the actual database type, including the size and other constraints, for instance ...varchar(1...
c# entity-framework-core entity-framework-core-2.1
asked by Ivan Stoev

Convert Enum From String To Int In DB Then Back To String - Entity Framework Core 3

This will return the enum string:...enum E : int { Element1 = 42 } //... int i = 42; string s = ((E)i).ToString();// returns "Element1" //...
asp.net-core-webapi c# entity-framework entity-framework-core enums
asked by beothunder

.NET Framework and Entity Framework Core: How can I get hold of the connection string

I've installed EF Core in a .NET Framework 4.7.2 class library. This is just a project containing some specific functionality, not the ...StartUp... project in the solution. The project with EF Core is then referenced by the main project, where the ...web.config... and IoC setup lives. The solution is for a web site....According to ...this page...,...
.net .net-4.7.2 c# entity-framework-core
asked by ptf

Entity Framework Core - This may indicate either a bug or a limitation in EF Core

I want to load from a user the newest private chat messages....My code to load the newest messages... public async Task<List<UserChatMessage>> GetUserPrivateChatMessagesAsync(string userId, string userChatPartnerId, int limit, int skip, CancellationToken cancellationToken) { cancellationToken.ThrowIfCancellationRequested(); if (userId =...
asp.net entity-framework-core linq
asked by Timo Falter

Linq To Entities: String.Contains a list

I am using ...ASP.net Core 3.0... with ...Entity Framework Core 3.0... and ...Pomelo.EntityFrameworkCore... provider for ...MySQL..., I need to query all the users that are from specific Towns. Lets say for example I have a list of strings called ...targettedTowns... in which I have the following towns...var targettedTowns = new List<string>() {"ko...
c# entity-framework-core linq pomelo-entityframeworkcore-mysql
asked by Hamza Khanzada

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow