LINQ Query to match a part (fraction) of a string

Question

I want to dynamically update a DataGridView based on a user entering in search terms in a text box, using LINQ.

In order to do this, I want what they enter to not have to perfectly match the string in the database, but for example if the company is 'Telexperts' and they type in 'Tele' in the text box, then it will return all companies that has 'Tele' in their name.

Here is some rough psuedocode:

Dim CompaniesSortedByTextBox = (From c As Company In db.Companies 
                                Where c.CompanyName Contains SortByNameTextBox.Value 
                                Select c).ToList

Edit: Glad to see this question took off with a good response. I've been following the answers provided, and right now I am using the Contains method, but am finding it to be very slow. I am changing the datasource based on when the textbox is changed, as follows:

Private Sub FilterDataGridView(sender As System.Object, e As System.EventArgs) 
                                          Handles FilterByCityBox.TextChanged
DataGridViewElements = (From c As Company In db.Companies Select c).ToList 'Reset it.
If (FilterByCityBox.Text <> "") Then
    CompanyInfos = (From c As Company in db.Companies Where c.City.Contains(FilterByCityBox.Text)).ToList
End If
PUCOCompanyRegistry.DataSource = CompanyInfos

It works, but is very slow. It requires the user waiting a good amount of time (about a half a second) after every keystroke in the textbox.

Contains is case sensitive (in C# anyway, not sure on VB.NET), so you may want to look into IndexOf >= 0 — Jason Down, Sep 26 '14 at 18:14
Wait... which language are you asking this in? C# or VB.NET? — Clever Neologism, Sep 26 '14 at 18:18
VB.Net is case-insensitive, so 'c.cOmPaNyNaMe.cOnTains' is perfectly valid. However, it should be avoided. — Xiaoy312, Sep 26 '14 at 18:19
from the `Dim` and lack of `()` after ToList, it seems to be VB.Net — Xiaoy312, Sep 26 '14 at 18:21
@Xiaoy312: That's true for the identifiers, but the `Contains` function checks for case when comparing strings, no? — Colin DeClue, Sep 26 '14 at 18:43
@Xiaoy312, they are the same method in both languages.. you're checking the value of the string aka char array.. "C" is not "c" in any format for obvious reasons.. — Brett Caswell, Sep 26 '14 at 19:16
The `Contains` method from the .net framework certainly functions in a case-sensitive way. I was commenting on the language itself. Sorry, I totally missed the real question. — Xiaoy312, Sep 26 '14 at 19:43
@Musicode Your edit is totally unrelated to your first problem. You should post a different question. — Lucas Trzesniewski, Sep 26 '14 at 20:16

Lucas Trzesniewski · Accepted Answer · 2014-09-26T18:24:35.877

4

You have a couple alternatives:

String.StartsWith is translatable to SQL:

From c As Company In db.Companies
Where c.CompanyName.StartsWith(SortByNameTextBox.Value)
Select c

So is String.Contains:

From c As Company In db.Companies
Where c.CompanyName.Contains(SortByNameTextBox.Value)
Select c

SqlMethods.Like implements SQL's LIKE construct:

From c As Company In db.Companies
Where SqlMethods.Like(c.CompanyName, "%" + SortByNameTextBox.Value + "%")
Select c

And since you're doing VB, I believe it has a Like operator:

From c As Company In db.Companies
Where c.CompanyName Like ("*" + SortByNameTextBox.Value + "*")
Select c

Here are some docs about string mapping.

edited Sep 26 '14 at 18:24

answered Sep 26 '14 at 18:18

Lucas Trzesniewski

50,214
11
107
158

1

Where does the `SqlMethods` come from? – OneFineDay Sep 26 '14 at 18:21
1

@OneFineDay It's a helper class made specifically for this purpose. [Here are the docs](http://msdn.microsoft.com/en-us/library/system.data.linq.sqlclient.sqlmethods.aspx). – Lucas Trzesniewski Sep 26 '14 at 18:25
Very nice @Lucas Trzesniewski! +1 – OneFineDay Sep 26 '14 at 18:29
This is the correct answer, although you should give the full namespace in this snippets: System.Data.Linq.SqlMethods.Like .. also.. I would avoid using the VB.NET Like operator in a linq expression, as it doesn't derive from the linq assembly, so it will probably just encapsulate the same functionality, but have to make the determination that it is being used in a linq expression first.. – Brett Caswell Sep 26 '14 at 19:14
@Brett the `Like` operator won't ever be executed on a linq-to-sql expression, as it's not executable code but an expression tree. This is evidenced by the [`SqlMethods.Like` implementation](http://referencesource.microsoft.com/#System.Data.Linq/DLinq/Dlinq/SqlClient/SqlMethods.cs#567). And [here's the relevant translation code](http://referencesource.microsoft.com/#System.Data.Linq/DLinq/Dlinq/SqlClient/Query/SqlMethodCallConverter.cs#936) for the `Like` operator ([second part](http://referencesource.microsoft.com/#System.Data.Linq/DLinq/Dlinq/SqlClient/Query/SqlMethodCallConverter.cs#2467)). – Lucas Trzesniewski Sep 26 '14 at 19:21
haha.. exactly.. you see that part at the end: `return sql.Like(mc.Arguments[0], pattern, escape, source)` it means everything above the line that calls on the helper is unneccessary if you just call on the helper in the first place – Brett Caswell Sep 26 '14 at 19:37
but even before that point.. there was an evaluation made on the part of the framework to say, we want to return an expression here.. the question then becomes, is it done at compile time, or runtime.. I'm guessing.. runtime – Brett Caswell Sep 26 '14 at 19:40
@Brett I don't get your point. This is an `Expression>`, and your guess is wrong, expressions in this case are immutable objects and are constructed at compile time as static fields, then referenced at runtime. It it the compiler that creates the expression tree. [See this answer](http://stackoverflow.com/a/793584/3764814). Besides, the `sql` variable you're talking about is of type `SqlFactory`, which is internal to the `System.Data.Linq.SqlClient` assembly, so you can't call it directly. Just don't say `PostBindDotNetConverter` is unnecessary :) – Lucas Trzesniewski Sep 26 '14 at 20:14
interesting.. I was working with a misconception there.. thanks for the reference and clarification.. Although it doesn't seem to be a particular issue now, my point was simple and it's actually still valid.. it's an unneccessary translation. – Brett Caswell Sep 26 '14 at 21:44

score 1 · Answer 2 · answered Sep 26 '14 at 18:16

1

//should be Filtered not Sorted 
Dim CompaniesSortedByTextBox = (From c As Company In db.Companies 
                                Where c.CompanyName.Contains(SortByNameTextBox.Value) 
                                Select c).ToList

answered Sep 26 '14 at 18:16

Xiaoy312

14,292
1
32
44

score 1 · Answer 3 · answered Sep 26 '14 at 19:16

1

I have one more suggestion, If you have a list of keywords and expecting that, CompanyName can be one of the value from list, you can do it like this.

 Dim lst As New List(Of String) ' assuming c.CompanyName can be = comp1 or comp2 or comp 3
 lst.Add("comp1") : lst.Add("comp3") : lst.Add("comp2")
 Dim CompaniesSortedByTextBox = (From c As Company In db.Companies
                                        Where lst.Contains(c.CompanyName)
                                        Select c).ToList

answered Sep 26 '14 at 19:16

Arindam Nayak

7,346
4
32
48

1

what is the point in the colons? `list.AddRange({"comp1","comp2","comp3"})` – Brett Caswell Sep 26 '14 at 19:19
Thanks for info, since i am acquainted with the earlier one, i used that, ":" is used in vb.net to make in multi statement to single, This is basically a helper method/statements , for example. – Arindam Nayak Sep 26 '14 at 19:21

LINQ Query to match a part (fraction) of a string

3 Answers3