LINQ Query FirstOrDefault speed

Question

When having a query like this

var id = 1;
var query = (from c in cTable.Where(x => x.Id == id)
             from a in aTable.Where(x => x.Code == c.Code).DefaultIfEmpty()  
             select new Object()
                 {
                     Id = c.Id,
                     Code = c.Code,
                 }).FirstOrDefault();

Does the query search first all the c where Id is equal to id and then retrieve the first one with FirstOrDefault(), or does the query already stop at the first c found?

If it doesn't stop searching until the end (and then apply the FirstOrDefault), is there a way to let the query stop at the first found?

Have you looked at the generated sql query? For entity framework you can use: https://stackoverflow.com/a/1412902/284240 — Tim Schmelter, May 08 '23 at 08:22
This is going to depend a lot on how exactly cTable and aTable are defined and obtained (although it should still short-circuit when a match is found, even in the worst outcomes) — Marc Gravell, May 08 '23 at 08:26
It depend on How U defind Ur Table U can Check ur query by MSSM - > XEvent profiler -> Tsql — sep7696, May 08 '23 at 08:30

score 3 · Accepted Answer · answered May 08 '23 at 10:00

It hugely depends on what cTable and aTable are. Since your question is tagged with SQL also I would assume that both are some IQueryables and you are using some ORM, so in this case the LINQ query is "just" translated into SQL and executed by the database engine and it is up to the DB how it executes the query. For example the latest EF Core generates something like the following query:

SELECT a."Id", a."Code"
FROM "cTable" AS a
LEFT JOIN "aTable" AS p ON a."Code" = p."Code"
WHERE a."Id" = @__id_0
LIMIT 1

To analyze the execution plan you will need to use the database specific tools and see how the query is processed.

As for LINQ-to-Objects (i.e. cTable and aTable for example are in-memory collections like Lists or arrays) just insert a side effect:

var query = (from c in cTable.Where(x =>
    {
        Console.WriteLine($"Filter cTable: {x}");
        return x.Id == id;
    })
    from a in aTable.Where(x =>
    {
        Console.WriteLine($"Filter aTable: {x}");
        return x.Code == c.Code;
    }).DefaultIfEmpty()
    select new
    {
        Id = c.Id,
        Code = c.Code,
    })
   .FirstOrDefault();

Note that this is an implementation detail but currently "on my machine" it searches for the first matching element of 1st collection (cTable) and then scans through the second one (aTable).

LINQ Query FirstOrDefault speed

1 Answers1