3

Linq-to-Entities does not seem to recognize IndexOf(String,Int). Int being the starting index. Does anyone know of a workaround that does not require materialization? I would like to keep the iqueryable, without setting tolist or IEnumerable.

Below is the use case. 

query = query.Where(Function(TableName) TableName.TableColumn.IndexOf(previousItem) <    TableName.TableColumn.IndexOf(item, TableName.TableColumn.IndexOf(previousItem)))

query = IQueryable

This line of code sits right after the Nth (where n>0) keyword. The Starting index is necessary to ensure that in the string "ABC%EFG", "EFG" is after "ABC"

Dassina
  • 162
  • 9
  • how would you write that query in sql? – Daniel A. White Dec 23 '13 at 22:32
  • This would be unnecessary in SQL. This line is only necessary because LINQ to Entities does not provide the SQL Like Operator. Instead it uses Contains, Endswith, and StartsWith. Which necessitates the ability to tell in what order Contains will be called. Use case: "A%BC" finding "AXYBC" and not "BCABC" – Dassina Dec 23 '13 at 22:36
  • 1
    If you are using SQLServer, you can use SqlFunctions.PatIndex http://msdn.microsoft.com/en-us/library/system.data.objects.sqlclient.sqlfunctions.patindex(v=vs.110).aspx – Jurica Smircic Dec 24 '13 at 00:02
  • Well, actually [VB.NET does provide a Like operator for you](http://msdn.microsoft.com/en-us/library/swf8kaxw.aspx). Use that anywhere you need it, including LINQ. – Victor Zakharov Dec 27 '13 at 01:45
  • @Neolisk, I am unable to test that atm, but suspect that your answer would require materialization. While VB has a Like operator, LINQ to Entities does not. – Dassina Dec 27 '13 at 04:45
  • Check this out then: [LINQ to Entity, using a SQL LIKE operator](http://stackoverflow.com/a/2493719/897326). – Victor Zakharov Dec 27 '13 at 14:11
  • Unless I missed something, that link does not provide a solution. I am aware of contains, and am already using it. The issue is the order 2 contains come in. – Dassina Dec 28 '13 at 23:05

1 Answers1

1

As Jure suggests, go with SqlFunctions.PatIndex

Test: Given a Strings table with 3 records in it:

  1. Billy Bob Joe
  2. Bob Billy Joe
  3. Joe Billy Bob

Linq

var q = db.Strings
    .Where(x => SqlFunctions.PatIndex("%Joe%Bil%Bob", x.Text) > 0)
    .Select(x => x.Text);

returns: Joe Billy Bob

T-SQL Profiler

SELECT 
[Extent1].[Text] AS [Text]
FROM [dbo].[Strings] AS [Extent1]
WHERE ( CAST(PATINDEX(N'%Joe%Bil%Bob', [Extent1].[Text]) AS int)) > 0
Vland
  • 4,151
  • 2
  • 32
  • 43
  • My example was simplified. In the case of ABCDEFG, with a search term of A%BC% your solution would not work. When I search for Joe Billy Bob, I may not know how he spells his name or if it was entered into the db correctly. So a search of Joe%Bil%Bob% will have issues. The issue at hand is that without a Like operator or materialization, I have no way of knowing in what order the search term is true. So Bob Billy Joe could be returned when I want Billy Bob Joe – Dassina Dec 27 '13 at 04:51
  • Then go with jure solution, try PatIndex: .Where(x => SqlFunctions.PatIndex("%Joe%Bil%Bob", x.MySearchField) > 0) – Vland Dec 27 '13 at 11:08
  • Another easy way to solve is to just write some SQL code or call a stored procedure passing your search parameter. – Vland Dec 27 '13 at 11:17
  • I am currently out of the office and will not be able to test jure's solution for a few days. However, I am aware that I can create a stored proc for this search, but was trying to stay consistent with the current project framework. – Dassina Dec 28 '13 at 23:08
  • @Dassina yes.. I see what you mean, I always try to do the same with Linq to Entities but I always end up in doing something else because I don't like the generated SQL.. anyway I tested it for you, it seems to work. GL – Vland Dec 28 '13 at 23:46