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 ? 😉

Micro ORMs, alternatives to Entity Framework ? Part 9

 

<< Previous article (Part 8)

What conclusion(s) can we make ?

Let’s summarize first principals features we described for each Micro ORM before in a user friendly table here:

Legends :

 Disappointing

 Good

 Excellent

 Yes

 No

 Yes, but not verified

Note that they :

  • All support the execution of stored procedures, views, functions
  • Are all protected from SQL injection (queries parameterization)

What we liked ?

  • NPoco for simplicity of its syntax and performance
  • Dapper for its outstanding performance and its amazing community
  • OrmLite for its double LINQ-like and SQL syntax, and for its rich functionalities and performance

Finally….

Even if we love some Micro ORMs and they are more performant than Entity Framework, they don’t provide all Entity Framework’s functionalities, and they don’t support unit tests as Entity Framework does (by mocking its DbContext).

In fact it depends on what you are looking for 🙂

I hope this suite of articles helped you to make a choice 😉

If you need to check the source code, you can find it here : https://github.com/AnthonyGiretti/MicroOrmDemo.net

Micro ORMs, alternatives to Entity Framework ? Part 8

 

<< Previous article (Part 7)

Performance comparisons

I did some benchmarks, only with Select query.

First benchmark scenario

I used single query that bring back from database 500 rows, here the query:

 SELECT TOP 500 [WorkOrderID] AS Id, P.Name AS ProductName, [OrderQty] AS Quantity, [DueDate] AS Date
 FROM [AdventureWorks2014].[Production].[WorkOrder] AS WO 
 INNER JOIN[Production].[Product] AS P ON P.ProductID = WO.ProductID

Second benchmark scenario

I used a serie of 500 queries that returns 1 row:

SELECT [WorkOrderID] AS Id, P.Name AS ProductName, [OrderQty] AS Quantity, [DueDate] AS Date
FROM [AdventureWorks2014].[Production].[WorkOrder] AS WO 
INNER JOIN[Production].[Product] AS P ON P.ProductID = WO.ProductID
WHERE WorkOrderID = @Id

Let’s see what happened….

 

Most of time Micro Orms are faster than Entity Framework, but less than ADO.NET

 

What conclusions can we make ?