Lambda/Linq with Contains Criteria for Multiple Keywords

Lambda/Linq with Contains criteria for multiple keywords

Answering for my and others' reference.

var newList = MainList.Where(m => keywords.Any(k => m.Comments.Contains(k))).ToList();

LINQ with non-lambda for Any Contains

It's not clear what good you believe the from wor in words select wor is doing - it's really not helping you at all.

It's also not clear why you don't want to use a lambda expression. The obvious approach is:

bool hasBelieve = words.Any(x => x.Contains("believe"));

Note that this isn't checking whether the list of words has the word "believe" in - it's checking whether the list of words has any word containing "believe". So "believer" would be fine. If you just want to check whether the list contains believe you can just use:

bool hasBelieve = words.Contains("believe");

EDIT: If you really want to do it without a lambda expression, you'll need to basically fake the work that the lambda expression (or anonymous method) does for you:

public class ContainsPredicate
{
    private readonly string target;

    public ContainsPredicate(string target)
    {
        this.target = target;
    }

    public bool Apply(string input)
    {
        return input.Contains(target);
    }
}

Then you can use:

Func<string, bool> predicate = new ContainsPredicate("believe");
bool hasBelieve = words.Any(predicate);

Obviously you really don't want to do that though...

EDIT: Of course you could use:

var allBelieve = from word in words
                 where word.Contains("believe")
                 select word;

bool hasBelieve = allBelieve.Any();

But that's pretty ugly too - I'd definitely use the lambda expression.

Lambda or Linq method to search multiple parameters

If I understand correctly, you don't want to filter if the value of the parameters is 0? If so, two solutions:

1. Check if the parameter is equal to 0 in your condition:

   var lstJobs = dx.GetAllJobs().Where(x => 
       (SecLink == 0 || x.SectorLink.Equals(SecLink))
       && (LocLink == 0 || x.LocationLink.Equals(LocLink))
       && (IndLink == 0 || x.IndustryLink.Equals(IndLink))
       && (VacLink == 0 || x.VacancyTypeLink.Equals(VacLink))
       && x.JobName.Contains(keyword)).ToList();
   

2. Linq goodness, dynamically construct your query:

       var query = dx.GetAllJobs().Where(x => x.JobName.Contains(keyword));
   
       if (SecLink != 0)
       {
           query = query.Where(x => x.SectorLink.Equals(SecLink));
       }
   
       if (LocLink != 0)
       {
           query = query.Where(x => x.LocationLink.Equals(LocLink));
       }
   
       if (IndLink != 0)
       {
           query = query.Where(x => x.IndustryLink.Equals(IndLink));
       }
   
       if (VacLink != 0)
       {
           query = query.Where(x => x.VacancyTypeLink.Equals(VacLink));
       }
   
       var lstJobs = query.ToList();
   

Is there any way to optimize this LINQ where clause that searches for multiple keywords on multiple columns?

The real performance hit is that this kind of query is tough to optimize. You want to find substrings, which by default are not indexable.

From a purely L2S perspective there isn't much you can do. But if you can enable Full-text search, you'll have much better tools at your disposal to speed up your query.

See this Stack Overflow post for more info.

Filter query by multiple parameters from the list of objects

I had this issue earlier and here how I fixed it
You have two options in this case

1- Filter based on the ids

var res1 = dbcontext.CustomerPurchases
    .Where(p => filters.Select(c=>c.PurchaseId).Contains(p.PurchaseId))
    .Where(p => filters.Select(c => c.CustomerId).Contains(p.CustomerId));

2- Use contains

var resq = await dbcontext.CustomerPurchases
    .Where(p=> filters.Contains(new Filter { CustomerId = p.CustomerId,PurchaseId = p.PurchaseId }))
    .ToListAsync();

but if you run this one you won't get any result unless you implement IEquatable

So your Filter class will look like this

public class Filter : IEquatable<Filter>
{
    public int CustomerId;
    public int PurchaseId;

    public bool Equals(Filter? other)
    {
        return this.PurchaseId == other.PurchaseId &&
            this.CustomerId == other.CustomerId;
    }
}

Here is the full code of both ways

var options = new DbContextOptionsBuilder<ApplicationDBContext>()
                  .UseInMemoryDatabase("test")
                  .Options;
var dbcontext = new ApplicationDBContext(options);
await dbcontext.CustomerPurchases.AddAsync(new CustomerPurchase { CustomerId = 1,PurchaseId = 1,Cost = 10 });
await dbcontext.CustomerPurchases.AddAsync(new CustomerPurchase { CustomerId = 1, PurchaseId = 2, Cost = 10 });
await dbcontext.CustomerPurchases.AddAsync(new CustomerPurchase { CustomerId = 1, PurchaseId = 3, Cost = 10 });
await dbcontext.CustomerPurchases.AddAsync(new CustomerPurchase { CustomerId = 2, PurchaseId = 2, Cost = 10 });
await dbcontext.SaveChangesAsync();

var filters = new List<Filter>();
filters.Add(new Filter { CustomerId = 1, PurchaseId = 2 });
filters.Add(new Filter { CustomerId = 2, PurchaseId = 2 });

var resq = await dbcontext.CustomerPurchases
    .Where(p=> filters.Contains(new Filter { CustomerId = p.CustomerId,PurchaseId = p.PurchaseId }))
    .ToListAsync();
foreach (var item in resq)
{
    Console.WriteLine($" CustomerId : {item.CustomerId} , PurchaseId : {item.PurchaseId} Cost : {item.Cost}");
}

var res1 = dbcontext.CustomerPurchases
    .Where(p => filters.Select(c=>c.PurchaseId).Contains(p.PurchaseId))
    .Where(p => filters.Select(c => c.CustomerId).Contains(p.CustomerId));

var res = await res1.ToListAsync();
Console.WriteLine("===========================================================");
foreach (var item in res)
{
    Console.WriteLine($" CustomerId : {item.CustomerId} , PurchaseId : {item.PurchaseId} Cost : {item.Cost}");
}

and the running code

Update
After changed to SQL server I still got the error so option 2 is not an option when running on SQL server

But I found another solution where I am able to build the where clause based on the list of filters that I have
I found this PredicateBuilder
and here is the code using predicate builder

var whereclause = PredicateBuilder.False<CustomerPurchase>();
foreach (var filterrow in filters)
{

    whereclause = whereclause.Or(c => c.CustomerId == filterrow.CustomerId && c.PurchaseId == filterrow.PurchaseId);
    
}
var resqq = dbcontext.CustomerPurchases.Where(whereclause);

var resq = await resqq.ToListAsync();
foreach (var item in resq)
{
    Console.WriteLine($" CustomerId : {item.CustomerId} , PurchaseId : {item.PurchaseId} Cost : {item.Cost}");
}

That will build the query which will be translated by sql to the following statement

DECLARE @__filterrow_CustomerId_0 int = 1;
DECLARE @__filterrow_PurchaseId_1 int = 2;
DECLARE @__filterrow_CustomerId_2 int = 2;
DECLARE @__filterrow_PurchaseId_3 int = 2;

SELECT [c].[PurchaseId], [c].[CustomerId]
FROM [dbo].[CustomerPurchase] AS [c]
WHERE (([c].[CustomerId] = @__filterrow_CustomerId_0) AND ([c].[PurchaseId] = @__filterrow_PurchaseId_1)) 
    OR 
    (([c].[CustomerId] = @__filterrow_CustomerId_2) AND ([c].[PurchaseId] = @__filterrow_PurchaseId_3))

Here is the full class of PredicateBuyilder

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 hope that answer your question !!

how use like in sql on linq c#

EF Core cannot use Any with local collections (with small excpetion), only Contains is supported.

I would suggest to use function FilterByItems. And use in query:

string[] DATE_Group = { "2020", "2021" }; //changing values

var query = _context.c10
    .FilterByItems(DATE_Group, (u, s) => u.ON_DATE.Contains(s), true)
    .Select(u => new
    {
        User_NumberID = u.User_NumberID.ToString()
    })
    .ToList();

Adding LINQ Where && condition in a foreach loop

Enumerable.Contains is supported with collections:

string providedpostCode = "A41666";
string[] providedPostCodes = Enumerable.Range(0, providedpostCode.Length)
    .Select(ix => providedpostCode.Substring(0, ix + 1))
    .ToArray();

bool isSupported = !postcodeExclusionArray
    .Any(x => x.CustomerId == 2 && providedPostCodes.Contains(x.Postocde));

Not sure about the logic, if you need Any or !Any.

Query where multiple columns have to match a value set simultaneously in EF Core

I'd be inclined to build a dynamic Expression<Func<MyTableRow, bool>> to represent the filter.

var p = Expression.Parameter(typeof(MyTableRow), "dbItem");

var parts = new List<Expression>();
foreach (var set in typeVersionSets)
{
    var typeIdValue = Expression.Property(p, nameof(MyTableRow.TypeId));
    var typeIdTarget = Expression.Constant(set.Type);
    var typeIdTest = Expression.Equal(typeIdValue, typeIdTarget);
    
    var versionIdValue = Expression.Property(p, nameof(MyTableRow.VersionId));
    var versionIdTarget = Expression.Constant(set.Version);
    var versionIdTest = Expression.Equal(versionIdValue, versionIdTarget);
    
    var part = Expression.AndAlso(typeIdTest, versionIdTest);
    parts.Add(part);
}

var body = parts.Aggregate(Expression.OrElse);
var filter = Expression.Lambda<Func<MyTableRow, bool>>(body, p);

List<MyTableRow> MyResult = await dbContext.MyTable
    .Where(filter)
    .ToListAsync()

Expression Trees (C#) | Microsoft Docs

---

Related Topics