How can I replicate "select someinteger from foo where someinteger like '%50%' in Linq to entities?

I have an ASP.NET MVC application that displays data in a table format. I want to give my users the ability to search the table, so I take a text string and pass it into my service layer to construct a query using Linq to Entities.

I want to search a number of columns using the string. Some of the columns are integers (order ids), but the user doesn't care about integers and strings. They want to type '1200' and get any order with '1200' in the order number, or '1200' in the address.

The problem is that I can't find a way to construct a Linq-to-Entities query that results in SQL that looks like this:

select orderid, address from orders where orderid like '%1200%' or address like '%1200%'

Database context:

    public DbSet<Person> Persons { get; set; }
    public DbSet<Worker> Workers { get; set; }
    public DbSet<WorkerSignin> WorkerSignins { get; set; }

The Persons and Workers tables are in a 1 to 0..1 relationship. If a worker record exists, a person record must also exist. They share the same ID. A worker record doesn't have to exist, however.

The Workers and WorkerSignins tables are related, but it's not enforced because of a client requirement. The Worker has an id-card with a barcode number on it (dwccardnum), but there may be discrepancies between cards issued and records in the DB, so I record all cards scanned in WorkerSignins, regardless of whether there is a matching record in the Workers table.

Here is the code I am working with:

            allWSI = signinRepo.GetAllQ()
                .Where(jj => jj.dateforsignin == date)
                .Select(a => a);

            if (!string.IsNullOrEmpty(search))
            {
                allWSI = allWSI
                    .Join(workerRepo.GetAllQ(), s => s.dwccardnum, w => w.dwccardnum, (s, w) => new { s, w })
                    .DefaultIfEmpty()
                    .Join(personRepo.GetAllQ(), oj => oj.w.ID, p => p.ID, (o开发者_运维技巧j, p) => new { oj, p }).DefaultIfEmpty()
                    .DefaultIfEmpty()
                    .Where(jj => Convert.ToString(jj.oj.w.dwccardnum).Contains(search) ||
                                jj.p.firstname1.Contains(search) ||
                                jj.p.firstname2.Contains(search) ||
                                jj.p.lastname1.Contains(search) ||
                                jj.p.lastname2.Contains(search))
                    .Select(a => a.oj.s);
            }

The GetAllQ() methods return an IQueryable() object.

The problem is on this line: .Where(jj => Convert.ToString(jj.oj.w.dwccardnum).Contains(search) ||

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

If I take out the convert, and try this: .Where(jj => jj.oj.w.dwccardnum.Contains(search) ||

I get this error: 'int' does not contain a definition for 'Contains' and the best extension method overload 'System.Linq.ParallelEnumerable.Contains(System.Linq.ParallelQuery, TSource)' has some invalid arguments

So the question is... How do I construct a Where clause to generate a like '%string%' and execute it against a integer column using Linq to Entities? (e.g. without using LINQ to SQL)

---

One option is to replace ...

jj => Convert.ToString(jj.oj.w.dwccardnum).Contains(search)

... by:

jj => SqlFunctions.StringConvert((decimal)jj.oj.w.dwccardnum).Contains(search)

SqlFunctions is a static class in namespace System.Data.Objects.SqlClient and I believe it only works with SQL Server. The weird cast to decimal is necessary because StringConvert doesn't have an overload for an int and without the cast the compiler complains that it cannot select the right overload unambiguously. (It has one for decimal? and one for double?.) But I just tested that the code above works indeed (with SQL Server and assuming dwccardnum is an int).

---

Try this

            if (!string.IsNullOrEmpty(search))
            {
                int cardnum;
                bool searchIsInt = int.TryParse(search, out cardnum);

                allWSI = allWSI
                    .Join(workerRepo.GetAllQ(), s => s.dwccardnum, w => w.dwccardnum, (s, w) => new { s, w })
                    .DefaultIfEmpty()
                    .Join(personRepo.GetAllQ(), oj => oj.w.ID, p => p.ID, (oj, p) => new { oj, p }).DefaultIfEmpty()
                    .DefaultIfEmpty()
                    .Where(jj => (searchIsInt ? jj.oj.w.dwccardnum == cardnum : true) ||
                                jj.p.firstname1.Contains(search) ||
                                jj.p.firstname2.Contains(search) ||
                                jj.p.lastname1.Contains(search) ||
                                jj.p.lastname2.Contains(search))
                    .Select(a => a.oj.s);
            }

Basically, you're first checking to see if the search is an int and then use it in your linq if it is.