VB.net can't find by string

Question

Using VB.net, the following snippet gives the error below.

Dim _account = Account.Find(Function(x As Account) x.AccountName = txtFilterAccountName.Text)

or similarly if I do

.SingleOrDefault (Function(x As Account) x.AccountName = txtFilterAccountName.Text)

will both give the error "The method 'CompareString' is not supported". If I make the same call searching for an integer (ID field) it works fine.

.SingleOrDefault (Function(x As Account) x.Id = 12)

So interger matching is fine but strings don't work Is this a problem with the VB.net templates?

Answer 1

No this is not a problem with Vb.Net templates.

The problem is that you are not using a normal LINQ provider. Based on your tag (subsonic) I'm guessing you're using a LINQ to SQL query.

The problem is that under the hood, this is trying to turn your code into an expression tree which is then translated into an SQL like query. Your project settings are turning your string comparison into a call in the VB runtime. Specifically, Microsoft.VisualBasic.CompilerServices.Operators.CompareString.

The LINQ2SQL generater in question or VB compiler (can't remember where this check is done off the top of my head) does not understand how to translate this to an equivalent bit of SQL. Hence it generates an error. You need to use a string comparison function which is supported by LINQ2SQL.

EDIT Update

It looks like the CompareString operator should be supported in the Linq2SQL case. Does subsonic have a different provider which does not support this translation?

• http://msdn.microsoft.com/en-us/library/bb399342.aspx

Answer 2

The problem is with SubSonic3's SQL generator and the expression tree generated from VB.NET.

VB.NET generates a different expression tree as noted by JaredPar and SubSonic3 doesn't account for it - see Issue 66.

I have implemented the fix as described but it has yet to merge into the main branch of SubSonic3.

Answer 3

BlackMael's fix has been committed:

http://github.com/subsonic/SubSonic-3.0/commit/d25c8a730a9971656e6d3c3d17ce9ca393655f50

The fix solved my issue which was similar to John Granade's above.

Thanks to all involved.