EF Core generated SQL is quite inefficient in this case, any workaround?

Question

Take such simple entities:

public class Person
{
    public int Id { get; set; }
    public string MainEmail { get; set; }
}

public class Person_Email
{
    public int Id { get; set; }
    public int Id_Person { get; set; }
    public string Email { get; set; }
}

This query:

db.Person.Where(p => p.Person_Email.Any(c => c.Email == "myemail@email.com") || p.MainEmail == "myemail@email.com").FirstOrDefault();

gets translated as:

SELECT TOP(1) [p].[Id]
FROM [Person] AS [p]

WHERE (EXISTS (
    SELECT 1
    FROM [Person_Email] AS [p1]
    WHERE ([p].[Id] = [p1].[Id_Person]) AND ([p1].[Email] = N'myemail@email.com')) 
    OR ([p].[MainEmail] = N'myemail@email.com')
    )

and this is very slow, on my db the profiler says 4347316 reads and a duration of 2568).

I would write the sql as:

SELECT TOP(1) [p].[Id]
FROM [Person] AS [p]

join [Person_Email] as pe on p.Id = pe.Id_Person
where p.MainEmail = N'myemail@email.com' or pe.Email = N'myemail@email.com'

In this case the profiler says 17448 reads and a duration of 300.

I wonder if there's a way to optimize this writing the LINQ query in a different way or we can but wait for the ef core team to improve it (I tried ef core 5.0 preview 8 and nothing changed).

user_12734914 · Answer

How about this?

var person = (from a in db.Person
              from b in db.Person_Email.Where(c => c.Id_Person == a.Id).DefaultIfEmpty()
              where a.MainEmail == "myemail@email.com" || b.Email == "myemail@email.com"
              select a).FirstOrDefault();

or

var personemail = db.Person_Email.Where(c => c.Email == "myemail@email.com").FirstOrDefault();

var person = db.Person.Where(c => (personemail == null || c.Id == personemail.Id_Person) || c.MainEmail == "myemail@email.com").FirstOrDefault();

EF Core generated SQL is quite inefficient in this case, any workaround?

1 Answers