Dynamic Linq OR with int and string

Question

I have a very simple query that would look like this

select      *
from        job
where       jobId like '%23%'
or title    like '%23%'

I need to be able to replicate this using dynamic Linq

The closest i've come to is this, but it doesn't work

.Where("@0.Contains(jobId) or title.Contains(@0)", "23");

Has anyone got a solution to this, ideally I would like it to do a like on both int's and strings

addendum based on comments

The error is:

An exception of type 'System.Linq.Dynamic.ParseException' occurred in System.Linq.Dynamic.dll but was not handled in user code Additional information: No applicable method 'Contains' exists in type 'String'

The jobId field is an int, while title is a varchar.

why you use `@0.Contains(jobId)`? in this case instead `@0` placed "23", so it look like `"23".Contains(jobld)` — Grundy, May 21 '15 at 08:26
Have you tried `.Where(x=>x.jobId.ToString().Contains("23") || title.Contains("23");`? — Hintham, May 21 '15 at 08:28
@Hintham used dynamic linq, so passed string instead expression — Grundy, May 21 '15 at 08:30
@Grundy You are correct, overlooked OP's requirement on using dynamic Linq — Hintham, May 21 '15 at 08:34

xanatos · Accepted Answer · 2015-05-21T16:45:37.093

6

Your query is nearly right:

.Where("@0.Contains(jobId.ToString()) or title.Contains(@0)", "23")

Entity Framework (I hope you are using it) correctly changes jobId.ToString() to CAST( [Extent1].[Id] AS nvarchar(max))... It then uses a CHARINDEX instead of a LIKE, but this isn't a problem.

The query I get, with Entity Framework 6.1.3 on SQL Server is:

SELECT 
    [Extent1].[jobId] AS [jobId], 
    [Extent1].[title] AS [title]
    FROM [dbo].[job] AS [Extent1]
    WHERE (( CAST(CHARINDEX( CAST( [Extent1].[jobId] AS nvarchar(max)), N'23') AS int)) > 0) OR ([Extent1].[title] LIKE N'%23%')

edited May 21 '15 at 16:45

answered May 21 '15 at 08:11

xanatos

109,618
12
197
280

My example was typed, I am passing a string as the paramater. – Dale Fraser May 21 '15 at 08:12
An exception of type 'System.Linq.Dynamic.ParseException' occurred in System.Linq.Dynamic.dll but was not handled in user code Additional information: No applicable method 'Contains' exists in type 'String' – Dale Fraser May 21 '15 at 08:13
actual code is filterQuery = filterQuery.Where(where, filter); where = "@0.Contains(jobId) or title.Contains(@0)" and filter = "118" – Dale Fraser May 21 '15 at 08:15
No jobId is int and title is varchar, thus my title "Dynamic Linq OR with int and string" – Dale Fraser May 21 '15 at 08:17
There is no difference between "23" and int id = 23; id.ToString(); – Tonci May 21 '15 at 08:21
@DaleFraser If you are using Entity Framework, it should work. Untested on LINQ-to-SQL – xanatos May 21 '15 at 08:24
@Tonci It was an example showing that if it had as a parameter an `int`, he could simply transform it to a `string` – xanatos May 21 '15 at 08:24

Dynamic Linq OR with int and string

1 Answers1

Linked