Search code examples
linqentity

LINQ to Entity VB - Convert Long to String for Contains or Like

I read many entries, but I found no solution for my (special?) problem. Here we go.

I have a search String, which is a number and a table column which is of type (bigint, Null).

I tried:

1.

Dim suche = "2012"
Dim nummern = (From t In entity.Table Where SqlFunctions.StringConvert(CDbl(t.TicketNr)).Trim.Contains(suche)
                          Select t.TicketNr Order By TicketNr Descending).ToList

The Problem here is, even without "trim" that the double conversion didn't find the long numbers 20120000000000 e.g. so the result is not complete. So as it seems that there is no solution with LINQ to Entity I tried E-SQL with LIKE.

2.

Dim suche = "%2012%"
Dim nummern = entity.Table.Where("it.longnumber LIKE @suche", New ObjectParameter("suche", suche))

Results in a similiar cast-error. If the table "longnumber" would be a string, then the solution given above, works fine!

:).

The solution is sometimes so Simple!:

(From t In entity.Table Where SqlFunctions.StringConvert(CDbl(t.TicketNr), 15, 0).Contains(suche)
                           Select t.TicketNr Order By TicketNr Descending).ToList

You have to use the StringConvert Overload ;).

A solution in e-SQL, like in (2) would be nice though, if someone will come along this thread and know how to do it!

Solution

  • The solution is sometimes so simple!!!:

    (From t In entity.Table Where SqlFunctions.StringConvert(CDbl(t.TicketNr), 15, 0).Contains(suche)
                           Select t.TicketNr Order By TicketNr Descending).ToList

    You have to use the StringConvert Overload ;).

    A solution in e-sql, like in (2) would be nice though, if someone will come along this thread and know how to do it!