Advanced Search in Linq to SQL possible??

Question

Without the use of "LIKE" in Dynamic Linq it virtually renders it useless to me when trying to create an advanced search query.

How have any of you overcome this Advanced Search problem when using Linq to SQL?

I'd need to search the following field types and they all could be null as well:

• List item
• varchar (column LIKE '%' + myText + '%')
• text (column LIKE '%' + myText + '%')
• DateTime (column >= myDate - if the "myDate" is not null of course)
• integer (column = myInt - if "myInt" is not null of course)
• boolean

I also can't just use ExecuteQuery because then I don't get a true "entity" and all of it's relations/associations.

I just don't see how I can do this with Linq to SQL. I'm currently without Stored Procs so I'd rather not have one stored proc just for this if I can figure this out with Linq.

Answer 1

To make LIKE Statements, you can use the Contains method:

string myText = "test";
var query = dc.Table.Where(r=>r.Column.Contains(myText));

This will generate this kind of SQL Statement:

SELECT [t0].[Column], ... [t0].[ColumnN]
FROM [Table] AS [t0]
WHERE [t0].[Column] LIKE @p0

And the @p0 parameter will have "%test%" as value.

For the Date and int comparison if I understand correctly what do you want, you could do this:

DateTime? myDate = new DateTime(2009, 3, 15);
var query = dc.Table.Where(r=> r.DateColumn > myDate || myDate == null );

So, if myDate is null, the condition DateColumn > myDate wont be evaluated.