Multiple Where Clauses with Linq Extension Methods

Multiple WHERE Clauses with LINQ extension methods

Two ways:

results = results.Where(o => (o.OrderStatus == OrderStatus.Open) &&
                             (o.CustomerID == customerID));

or:

results = results.Where(o => (o.OrderStatus == OrderStatus.Open))
                 .Where(o => (o.CustomerID == customerID));

I usually prefer the latter. But it's worth profiling the SQL server to check the query execution and see which one performs better for your data (if there's any difference at all).

A note about chaining the .Where() methods: You can chain together all the LINQ methods you want. Methods like .Where() don't actually execute against the database (yet). They defer execution until the actual results are calculated (such as with a .Count() or a .ToList()). So, as you chain together multiple methods (more calls to .Where(), maybe an .OrderBy() or something to that effect, etc.) they build up what's called an expression tree. This entire tree is what gets executed against the data source when the time comes to evaluate it.

Extension method in where clause in linq to Entities

No, you cannot educate EF to process your custom extension methods, even though you have code that builds expressions that would be usable to EF.

Either:

• use the SqlFunctions methods directly in your EF Where expression
• use an ExpressionVisitor to combine multiple expressions into a composite (OrElse / AndAlso) expression (note this won't help you have code like you want, but it will let you use your two methods and perform a || on them - it will look complex, though)

The first is simpler and clearer.

Reusable Linq expression (to be passed to multiple .Where clauses in different parts of project)

You could create an extension method for IQueryable<Event> like this:

public static class EventExtension
{
  public static IQueryable<Event> GetFiltered(this IQueryable<Event> query, Int32 ownerId, Int32 quarter)
  {
    return query.Where(x =>
      x.OwnerId == ownerId && (
        x.DateType == 1 && x.EndDateTimeUtc >= DateTime.UtcNow ||
        x.DateType == 2 && x.Month >= DateTime.UtcNow.Month && x.Year >= DateTime.UtcNow.Year ||
        x.Year >= DateTime.UtcNow.Year ||
        x.DateType == 3 && x.Quarter >= quarter && x.Year >= DateTime.UtcNow.Year ||
        x.Year >= DateTime.UtcNow.Year
      ));
  }
}

Then simply call it from everywhere with

var entities = await _context.Events
     .Include(x => x.Category)
     .Include(x => x.Owner)
     .GetFiltered(request.ownerId, request.quarter)

Should I use two where clauses or && in my LINQ query?

I personally would always go with the && vs. two where clauses whenever it doesn't make the statement unintelligible.

In your case, it probably won't be noticeble at all, but having 2 where clauses definitely will have a performance impact if you have a large collection, and if you use all of the results from this query. For example, if you call .Count() on the results, or iterate through the entire list, the first where clause will run, creating a new IEnumerable<T> that will be completely enumerated again, with a second delegate.

Chaining the 2 clauses together causes the query to form a single delegate that gets run as the collection is enumerated. This results in one enumeration through the collection and one call to the delegate each time a result is returned.

If you split them, things change. As your first where clause enumerates through the original collection, the second where clause enumerates its results. This causes, potentially (worst case), 2 full enumerations through your collection and 2 delegates called per member, which could mean this statement (theoretically) could take 2x the runtime speed.

If you do decide to use 2 where clauses, placing the more restrictive clause first will help quite a bit, since the second where clause is only run on the elements that pass the first one.

Now, in your case, this won't matter. On a large collection, it could. As a general rule of thumb, I go for:

1. Readability and maintainability

2. Performance

In this case, I think both options are equally maintainable, so I'd go for the more performant option.

Multiple from clauses in LINQ

Enumerable.Range(1, 3).SelectMany(
    i => Enumerable.Range(4, 3),
    (i, j) => new Tuple<int, int>(i, j)
).ToList();

Multiple Group By LINQ extension methods

Try this:

var result = testq
            .Where(p => p.SelectedAnswerId.HasValue || testq.Count(x => x.QuestionId == p.QuestionId) == 1)
            .GroupBy(p => p.QuestionId)
            .Select(p => p.FirstOrDefault())
            .Distinct()
            .ToList();

C# Fiddle

C# LINQ performance when extension method called inside where clause

I re-generated the scenarios you talked about in your question. I tried following code and got this output.

But this is how you can debug this.

static List<string> itemCollection = new List<string>();

static void Main(string[] args)
{

    for (int i = 0; i < 10000000; i++)
    {
        itemCollection.Add(i.ToString());
    }

    var watch = new Stopwatch();
    watch.Start();

    Console.WriteLine(CheckIdExists(580748));
    watch.Stop();
    Console.WriteLine($"Took {watch.ElapsedMilliseconds}");

    var watch1 = new Stopwatch();
    watch1.Start();

    Console.WriteLine(CheckIdExists1(580748));
    watch1.Stop();
    Console.WriteLine($"Took {watch1.ElapsedMilliseconds}");

    Console.ReadLine();
}

public static bool CheckIdExists(int searchId)
{
    return itemCollection.Any(item => item.Equals(ConvertToString(searchId)));
}

public static bool CheckIdExists1(int searchId)
{
    string sId =ConvertToString(searchId);

    return itemCollection.Any(item => item.Equals(sId));
}

public static string ConvertToString(int input)
{
    return Convert.ToString(input, CultureInfo.InvariantCulture);
}

OUTPUT:

True
Took 170
True
Took 11

---

Related Topics