EF CORE 6

 Enumerating through the results

3. Everytime we enumerate thorugh the DBset inside a foreach loop, the connection to the database remains open. So doing a lot of work inside the loop will result in the database connection to stay open. So, it's better to run a ToList() and get the 

results in memory before we start iterating through the results.

SQL Injection

If we hardcode the value for a parameter in the LINQ query then it will be hardcoded in the query as well, so try to use variables. Variables are converted to parameters and hence prevent SQL injection.  

Partial Filtering

We can have more control on the LIKE statement when using the Ef.Functions.Like method

 var authorsNameEndWith = await context.Authors.Where(a => EF.Functions.Like(a.FirstName, "%a")).ToListAsync();

Find 

Order By

If we have multiple order by statements, then LINQ will ignore all but the last one. So, use the thenBy operator to add more orderby statements.

Triggering the query execution

No Tracking

Not all scenarios need the entity to be tracked. For Ex: When we try to display all the authors in the database. We just need to query and return the data. Change tracking is expensive. 

We can configure the entire Dbcontext to be non-tracking and we can maintain 2 contexts separately in the solution and use them accordingly.  

DBContext and Tracking

DbContext represents a session with the database.

Everytme, we interact with the database, it goes the the cycle shown in the image above. Finally when the query is executed it also needs to reset the state information to unchanged. 

SaveChanges with an update, will return the rowcount. This count will be equal to the number of rows updated. It doesnt do the same with Add, because Add will always insert a single object.