LINQ to SQL - select where text like string array
Solution 1
Check this article out to do what you want:
http://www.albahari.com/nutshell/predicatebuilder.aspx
This works like a dream. I essentially cut and pasted their code and got this back (with my own data-scheme of course):
SELECT [t0].[Id], [t0].[DateCreated], [t0].[Name] ...
FROM [dbo].[Companies] AS [t0]
WHERE ([t0].[Name] LIKE @p0) OR ([t0].[Name] LIKE @p1)
Here is the code I ran for the proof of concept:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
namespace PredicateTest
{
class Program
{
static void Main(string[] args)
{
DataClasses1DataContext dataContext = new DataClasses1DataContext();
Program p = new Program();
Program.SearchCompanies("test", "test2");
var pr = from pi in dataContext.Companies.Where(Program.SearchCompanies("test", "test2")) select pi;
}
DataClasses1DataContext dataContext = new DataClasses1DataContext();
public static Expression<Func<Company, bool>> SearchCompanies(
params string[] keywords)
{
var predicate = PredicateBuilder.False<Company>();
foreach (string keyword in keywords)
{
string temp = keyword;
predicate = predicate.Or(p => p.Name.Contains(temp));
}
return predicate;
}
}
public static class PredicateBuilder
{
public static Expression<Func<T, bool>> True<T>() { return f => true; }
public static Expression<Func<T, bool>> False<T>() { return f => false; }
public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1,
Expression<Func<T, bool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>
(Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
}
public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1,
Expression<Func<T, bool>> expr2)
{
var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
return Expression.Lambda<Func<T, bool>>
(Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
}
}
}
I'd suggest going to the site for the code and explanation.
(I am leaving the first answer because it works well if you need an IN statement)
Solution 2
kind of new to the whole LINQ to SQL game, but does this syntax help?
string[] items = new string[] { "a", "b", "c", "d" };
var items = from i in db.Items
where items.Contains(p.text)
select i;
Got it from:
http://blog.wekeroad.com/2008/02/27/creating-in-queries-with-linq-to-sql/
Solution 3
After reading this post, looking for the same solution as you, I found a solution using the .Any
and .All
methods for Linq are a nice simple and elegant way to get matching results for arrays.
In this instance I'm using a search input, separated by commas as an example. I don't care if the match is not in the same case.
var qry = Query.Split(',').Select(c => c.Trim().ToLower());
First Get some data to query, from Linq to SQL or wherever
var search = db.tablename;
Using the lambda syntax for nice tight code, and result in matches to .Any
string in the query to either the name or description in the table.
search = search.Where(
record =>
qry.Any(q => record.Name.ToLower().Contains(q)) ||
qry.Any(q => record.Description.ToLower().Contains(q)));
If you want only results where all strings are matched within any field you can replace .Any
with .All
:
search = search.Where(
record =>
qry.All(q => record.Name.ToLower().Contains(q)) ||
qry.All(q => record.Description.ToLower().Contains(q)));
Admin
Updated on July 09, 2022Comments
-
Admin almost 2 years
I have a List
<string
> of variable count, and I want to query (via LINQ) a table to find any items that contain any of those strings in the Text column.Tried this (doesn't work):
items = from dbt in database.Items where (stringList.FindAll(s => dbt.Text.Contains(s)).Count > 0) select dbt;
Query would be something like:
select * from items where text like '%string1%' or text like '%string2%'
Is this possible?
-
Admin over 15 yearsThanks for the idea Matthew, but that generates the following SQL: SELECT [t0].[Text] FROM [dbo].[Table] AS [t0] WHERE [t0].[Text] IN (@p0) It's looking for the text column in each item of the array rather than looking for each item of the array in the text column.