Linq How to use where correctly

Question

What's the difference between this 2 queries?

Aren't they same and which one is speedy? Will be any problem using where after where? Writing where after where makes my code my readable for me. Am i doing this wrong?

First query:

   Model= (model.Where(p => p.Date.Year == yilim)
                        .Where(p => p.Id== 2 || p.Id== 3)
                        .OrderBy(m => m.Date.Month))
                        .ToList();

Second Query:

  Model= (model.Where(p => p.Date.Year == yilim && (p.Id== 2 || p.Id== 3))
                            .OrderBy(m => m.Date.Month))
                            .ToList();

Answer 1

There is no functional difference because daisy-chaining Where calls is a logical AND operation, and you are currently ANDing stuff the two separate conditions together.

However, it will likely be slightly less efficient in terms of removing ability for the compiler to optimise the condition checking (such as short-circuiting) and the additional enumerator required for the second Where as opposed to just one enumerator. (apologies, that part was only for Linq to Objects.)

If your code was to OR the conditions, only the second query would provide what you want:

Model= (model.Where(p => p.Date.Year == yilim || p.Id== 2 || p.Id== 3)
                          .OrderBy(m => m.Date.Month))
                          .ToList();

// Cannot express the above query in simple daisy-chained Where calls.

If the logic in a Where is becoming difficult to read, try "commentless coding" and stuff the condition into a method with a very readable name, then you can do .Where(x => TheCustomerNameIsValid(x)), or method grouping it .Where(TheCustomerNameIsValid) (when you can). This also helps with debugging, because placing breakpoints in a method is a little less finicky than doing so in a lambda.

Answer 2

Others' answers give the answer very clear, but I've got a different recommendation. Why don't you check out the resulting SQL queries by debugging. You'll see if there's a difference between them. To get the actual SQL queries generated and run by the system you can write as follows:

using (EntityConnection con = new EntityConnection("Name = testEntities"))
{
    con.Open();

    using (testEntities db = new testEntities())
    {
        int yilim = 2013;

        IQueryable<Model> models = (db.Model.Where(p => p.Date.Year == yilim)
                                            .Where(p => p.ID == 2 || p.ID == 3)
                                            .OrderBy(m => m.Date.Month))
                                            .AsQueryable();

        string modelsQuery = ((System.Data.Objects.ObjectQuery)models).ToTraceString();

        IQueryable<Model> models2 = (db.Model.Where(p => p.Date.Year == yilim && 
                                                        (p.ID == 2 || p.ID == 3))
                                             .OrderBy(m => m.Date.Month))
                                             .AsQueryable();

        string modelsQuery2 = ((System.Data.Objects.ObjectQuery)models2).ToTraceString();

        System.IO.File.WriteAllText(@"C:\Users\username\Desktop\queries.txt", 
            "Query 1:\r\n" + modelsQuery + "\r\n" + 
            "Query 2:\r\n" + modelsQuery2);

    }

And the results are:

Query 1:
SELECT 
[Project1].[ID] AS [ID], 
[Project1].[Date] AS [Date]
FROM ( SELECT 
    DATEPART (month, [Extent1].[Date]) AS [C1], 
    [Extent1].[ID] AS [ID], 
    [Extent1].[Date] AS [Date]
    FROM [dbo].[Model] AS [Extent1]
    WHERE ((DATEPART (year, [Extent1].[Date])) = @p__linq__0) AND ([Extent1].[ID] IN (2,3))
)  AS [Project1]
ORDER BY [Project1].[C1] ASC


Query 2: 
SELECT 
[Project1].[ID] AS [ID], 
[Project1].[Date] AS [Date]
FROM ( SELECT 
    DATEPART (month, [Extent1].[Date]) AS [C1], 
    [Extent1].[ID] AS [ID], 
    [Extent1].[Date] AS [Date]
    FROM [dbo].[Model] AS [Extent1]
    WHERE ((DATEPART (year, [Extent1].[Date])) = @p__linq__0) AND ([Extent1].[ID] IN (2,3))
)  AS [Project1]
ORDER BY [Project1].[C1] ASC

And they are identical. So a difference in the performance isn't expected here.

Answer 3

Writing where after where makes my code my readable for me. Am i doing this wrong?

Others have commented on the performance/actual compiled benefit, I am only suggesting readability of code (which is of a religious nature, so take it with a grain of salt).

Reformatted First query:

   Model= (model.Where(p => p.Date.Year == yilim)
                .Where(p => p.Id== 2 || p.Id== 3)
                .OrderBy(m => m.Date.Month))
                .ToList();

My preference is to line up the methods with the class they are applied to. A multidimensional statement might look like:

   Model= (model.Where(p => p.Date.Where(d => d.Year == 2014)
                                  .Where(d => d.Month == 11))
                .Where(p => p.Id== 2 || p.Id== 3)
                .OrderBy(m => m.Date.Month))
                .ToList();

Reformattted Second Query:

  Model= (model.Where(p => p.Date.Year == yilim 
                           && (p.Id== 2 || p.Id== 3))
               .OrderBy(m => m.Date.Month))
               .ToList();

For readability in if like operators, I choose to keep all the ORs inline, and all the ANDs on separate lines.

 Model = model.Where(m => (m.Name == "jon" || m.Name == "joe")
                          && (m.Color == "red" || m.Color == "blue"));

Lastly, I typically shorten the lambda expression parameter to a letter that matches the first letter of the variable.

So:

   Model= (model.Where(p => p.Date.Year == yilim)

becomes:

   Model= (model.Where(m => m.Date.Year == yilim)

For more complicated queries, I've also used:

   query = persons.Where(person => person == ...
                  .Where(person => person.Friends.Where(friend => 

As sometimes a single letter becomes difficult to associate with the expressions without having to read what the entire expression tree is doing.

Answer 4

The difference is that in the first query, the first Where will applied to the sequence called model and in the resulting sequence will be applied the second where method. While in the second query the Where method is applied to the sequence called model. That being said, the second query is more efficient from the first one, since you run through the model sequence only once.

However, stating that the second would be more efficient steps from the theory. I don't think that in practice you will get any significant, if at all, measurable difference.

Answer 5

You are unlikely to notice any significant performance difference between the two. Calling Where() twice does add some overhead: you create an extra intermediate object, and of course during enumeration there is the extra method call to deal with.

But in most code, these differences won't be noticeable. If you find that the code is easier to read and maintain by splitting your filter into two separate calls to Where(), then that is the better approach.

At least, that is, until you do have a real performance problem. Then you can go back and revisit whether this is part of your problem and is worth changing.