Using contains on an integer in LINQ to SQL

Question

I'm trying the following query:

Return _context.companies _
        .Where(Function(c) c.id.StartsWith(filterCompanyId)) _
    .ToList()

but because id is an integer, it doesn't work. I found this answer which suggests I can use

c.id.ToString().StartsWith ...

but that gives me the following error:

LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.

When trying to use

.Where(Function(c) SqlFunctions.StringConvert(CDbl(c.id)).StartsWith("1"))

it doesn't work.

Any tips?

So if `filterCompanyId` is `3`, you want to return all companies `3`, `31`, `302`, etc. Is that really correct? Does that make sense? — Heinzi, Nov 27 '13 at 16:26
Yes that's correct. The users are familiar with the IDs and find it easy to type the company id into a box to quickly get there. — Sam Selikoff, Nov 27 '13 at 16:29
This should help for your conversion to string: http://stackoverflow.com/q/1066760/87698 — Heinzi, Nov 27 '13 at 16:36
I found that post but couldn't get it working with VB.net/my version of EF. — Sam Selikoff, Nov 27 '13 at 16:38
What problems did you encounter? What happens if you just use `SqlFunctions.StringConvert(CDbl(c.id)).StartsWith`? — Heinzi, Nov 27 '13 at 16:40
possible duplicate http://stackoverflow.com/questions/7717253/how-do-i-query-an-integer-column-for-starts-with-in-entity-framework — DevelopmentIsMyPassion, Nov 27 '13 at 16:42

score 1 · Answer 1 · answered Nov 27 '13 at 17:15

1

The relevant part is hidden in the error message:

'SqlFunctions' is not declared. It may be inaccessible due to its protection level

SqlFunctions is in the System.Data.Objects.SqlClient namespace (you can discover this by going to the "SqlFunctions Class" MSDN page and then checking the "Namespace:" entry). Thus, you need to import that namespace at the top of your code file:

Imports System.Data.Objects.SqlClient

answered Nov 27 '13 at 17:15

Heinzi

167,459
57
363
519

@SamSelikoff: Are you using SQL Server? – Heinzi Nov 27 '13 at 17:32
Nope, I'm using MySQL – Sam Selikoff Nov 27 '13 at 17:56
@SamSelikoff: Oh, that explains it. Unfortunately, StringConvert is not supported by the MySQL-EF-Provider, so you might have to [use good old SQL instead](http://stackoverflow.com/q/4275700/87698). – Heinzi Nov 27 '13 at 18:01

score 0 · Accepted Answer · answered Dec 11 '13 at 21:14

Per @Heinzi's comment, StringConvert is not supported by the MySQL-EF-Provider. I ended up going with a query string:

Dim isExpiredInt As Integer = If(isExpired, 1, 0)

Dim query As String = "Select * FROM company WHERE (expired = 0 OR expired = {0}) AND name like {1} AND id like {2}"

Dim companies = _context.ExecuteStoreQuery(Of company)(
    query,
    "companies",
    System.Data.Objects.MergeOption.AppendOnly,
    isExpiredInt, "%" & filterCompanyName & "%", filterCompanyId & "%"
).ToList()

Return companies

Note the System.Data.Objects.MergeOption.AppendOnly is something I added so the objects from this query would be attached to my context (i.e. calling SaveChanges on this context would write to the database).

Hope that helps!

Using contains on an integer in LINQ to SQL

2 Answers2