Entity Framework Core 2 – Sql generation improvements

Entity Framework Core 2 was released on August 14th. It brought new features.

On this article I will explain : Sql generation improvements and Sql generation new features

Sql generation improvements

• Unneeded nested sub-queries are not created
• Select only requested columns (projections)
• No more creating multiple SQL queries for a single LINQ query

Sql generation new features – string interpolation support

Now, the FromSql and ExecuteSqlCommand methods support interpolated strings, and will happily produce parameters as needed. You do not have to worry about those nasty SQL injection attacks and performance issues due to query plan creation!

Examples:

public List<WorkOrder> GetWorkOrdersByScrapReasonID(int scrapReasonId)
{
   var query = _context.WorkOrders.FromSql($"SELECT * FROM Production.WorkOrder WHERE ScrapReasonID = {scrapReasonId}");
   return query.ToList();
}

public void UpdateWorkOrdersByScrapReasonID(int scrapReasonId,int qty)
{
   _context.Database.ExecuteSqlCommand($"UPDATE Production.WorkOrder SET OrderQty = {qty} WHERE ScrapReasonID = {scrapReasonId}");
}

Sql generation new features – SQL’s LIKE function support

Now we have the support of SQL’s LIKE function

• Syntax:  EF.Functions.Like(FieldName, “%SearchText%”)
• Support string interpolation

Example:

public List<Product> GetProductsByName(string name)
{
   var query = _context.Products.Where(x => EF.Functions.Like(x.Name, $"%{name}%"));
   return query.ToList();
}

Generated SQL:

Nice improvements ? 😉