CODEWITHSUNDEEP
c#sqlef-core-3.1
el peregrino
el peregrino

Reputation: 791

Self JOIN query using EF-Core

I have an old stored procedure selecting data from a self-referencing table.

exists (select * 
        from MyTable t1
        join MyTable t2 on t1.model = t2.model
        where t1.name = 'Name1'
          and t1.value = 'Value1'
          and t2.name = 'Name2'
          and t2.value = 'Value2'
          and t1.model = @model)

We move the code to EF Core. Is there a more efficient way of building the above query?

I can imagine something like this

SELECT COUNT(*)
FROM MyTable 
WHERE 
    (model= @model) AND (
    (name = 'Name1' AND value = 'Value1') OR
    (name = 'Name2' AND value = 'Value2'))

and checking the returned value, so in EF

var v = await repository
    .AsNoTracking()
    .Where(t => t.model == model && 
               (t.name == 'Name1' && t.value='Value1' || 
                t.name == 'Name2' && t.value == 'Value2'))
    .CountAsync();

Is this a more efficient approach comparing to the JOIN? I can see there is a difference, however, the result should be the same based on the nature of our data.

Upvotes: 0

Views: 199

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

This is too long for a comment.

Which is faster depends on your data, your data model and the system you are running it on. For this reason, you should test the two versions on your system.

In general, though, the exists can stop at the first row that it encounters that matches the conditions. Assuming that you have appropriate indexes, then the overhead of the join should be minimal. Because it can stop quicker, it is reasonable to assume that that version would be faster than an approach that uses aggregation. Aggregation requires finding all matches, so even if there is only one, it needs to keep looking for all of them to return a count of 1.

Upvotes: 1

Related Questions