Best way to query the database using LINQ and Entity Framework

Question

Hello! I need to make a query in the database, the scenario is as follows: I receive email and from it I seek the id user. About the bank, there is the table Contact, which has Nxn relationship with the table user. So far I’ve done it this way:

var result = _context
             .Clients
             .Where(x => x.Contacts.First().Email.Equals(email))
             .AsNoTracking()
             .ToList();
            
var id = result
         .Select(x => x.Id)
         .First()
         .ToString();

However, I think that in this consultation that I did could be being long-winded and could be done in a better way, even in a single query to the bank (I did 2 in this example). Could someone make a suggestion?

Answer 1

The part where you take the Email from the first item of the contact table I can’t say if it is right or wrong, because it depends a lot on the business of your system, that is, only you could say if it is correct.

I will assume that this is correct, that the email is always the first of the contact table (although it does not make sense in my head) and I will change to be just a LINQ.

var result = _context.Clients.Where(w => w.Contacts.First().Email.Equals(email))
                             .Select(s => s.Id)
                             .FirstOrDefault();

string id = result == null ? string.Empty : result.ToString();

You weren’t doing two bank appointments like you said, because in part one, where you did the .ToList(), has already brought all data from the database that meet its criteria defined in .Where() memory. But you have all the fields in this client table in memory, and you only need the Id, not to mention that you brought more than 1 record, and in the end you only need 1 (because you use First).

That way I added the code, you only search for the Id for memory and only 1 record.

I hope I’ve helped!