In this article I wrote some tips of how to write queries for database in C# with higher performance.
When we write a query with many conditions in where the query can be slower according with how we write the code in C#. For example, to search the restaurants inside a limited by a rectangle area, we pass the upper west coordinate and the lower east coordinate as parameters to search the restaurants. A search using LINQ with query syntax is in the following code:
public static List<Place> GetRestaurantsInRectangleBounds(DbContext context, double northwestLon, double northwestLat, double southeastLon, double southeastLat) { return (from place in context.Place where place.type == "restaurant" && place.location.x >= northwestLon && place.location.y <= northwestLat && place.location.x <= southeastLon && place.location.y >= southeastLat select place).ToList(); }
We can write the same search using LINQ with method syntax and lambda expressions. The search is faster when we have higher data volume:
public static List<Place> GetRestaurantsInRectangleBounds(DbContext context, double northwestLon, double northwestLat, double southeastLon, double southeastLat) { return context.Place.Where(place => place.type == "restaurant" && place.location.x >= northwestLon && place.location.y <= northwestLat && place.location.x <= southeastLon && place.location.y >= southeastLat).ToList(); }
The way of update a database record influences the time to update. A way to update the record is changing the entity state, as the following code:
public static List<Place> UpdateRestaurant(DbContext context, Place place) { place.type = "restaurant"; context.Entry(place).State = EntityState.Modified; context.SaveChanges(); }
There's two more efficient ways to update the records. One of them is to use the same context to search the entities and save the changes. This way, we do not have to use explicit commands to update, we have only to change the object, as the following code:
List<Place> myPlaces = context.Place.ToList(); foreach(Place p in myPlaces) { p.type = "restaurant"; } context.SaveChanges();
Another option is update only the change property(ies):
List<Place> myPlaces; using(DbContext context = new AdaLovelaceContext()){ myPlaces = context.Place.ToList(); } foreach(Place p in myPlaces) { p.type = "restaurant"; context.Place.Attach(p); context.Entry(p).Property("type").IsModified = true; } context.SaveChanges();