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

Entity Framework Core 2 – Table splitting

 

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

On this article I will explain one of them : Table splitting

Table carving looks like owned types, but it’s not the same thing.
But an SQL table can be projected in several entities (and not a simple class like the possessed types).
Each entity must have the same primary key, and each entity must be configured and configured separately.

Example:

 

We will project this table in 2 entities, Product and ProductDetails:

public class Product
{
   public int ProductID { get; set; }
   public string Name { get; set; }
   public decimal Cost { get; set; }

   public ProductDetail Details { get; set; }
}

public class ProductDetail
{
   public int ProductID { get; set; }
   public decimal ListPrice { get; set; }
   public string Size { get; set; }
   public decimal? Weight { get; set; }

   public Product Product { get; set; }
}

Then let’s configure these 2 entities

  • A navigation property from Product to ProductDetails
  • A navigation property from ProductDetails to Product

It will we configured as a one to one relationship :

public class ProductConfiguration : IEntityTypeConfiguration<Product>
{
   public void Configure(EntityTypeBuilder<Product> builder)
   {
      builder.HasKey(x => x.ProductID);
      builder.HasOne(e => e.Details).WithOne( o=> o.Product).HasForeignKey<ProductDetail>(e => e.ProductID);
      builder.ToTable("Product");
   }
}
public class ProductDetailConfiguration : IEntityTypeConfiguration<ProductDetail>
{
   public void Configure(EntityTypeBuilder<ProductDetail> builder)
   {
      builder.HasKey(x=> x.ProductID);
      builder.HasOne(e => e.Product).WithOne(o => o.Details).HasForeignKey<Product>(e => e.ProductID);
      builder.ToTable("Product");
   }
}

It’s identical to two sql tables linked by a one to one relationship.

If you want to get ProductDetails when you quert a Product you must add .Include(“ProductDetails”) extension method to the query, like two sql tables linked by a one to one relationship as well.

Example:

public List<Product> GetProductsWithDetails()
{
   var query = _context.Products.Include(p=> p.Details).ToList();
}

This feature would be commonly used to get from database data you only want to query, it avoids developers to make multiple versions of a same entity, each one used depending the data you want, especially for performance reasons.

So it’s a great feature ?I love it! 🙂

Entity Framework Core 2 – Owned types

 

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

On this article I will explain one of them : Owned types

They existed in previous versions of Entity Framework under the name of “complex types” and then disappeared from Entity Framework Core 1
It is a grouping of fields of the same SQL table in a type belonging to the entity corresponding to the same SQL table.

Example, we want to group in table Person properties : FirstName, MiddleName, LastName under a subtype named Name:

 

 

This table will be mapped like this:

public class Person
{
   public int BusinessEntityID { get; set; }
   public Name Name { get; set; }
}

public class Name
{
   public string FirstName { get; set; }
   public string MiddleName { get; set; }
   public string LastName { get; set; }
}

Configuration required:

You must declared in the primary entity Person a key, then you have to map your properties FirstName, MiddleName, LastName to the subtype Name.

Example:

public class PersonConfiguration : IEntityTypeConfiguration<Person>
{
   public void Configure(EntityTypeBuilder<Person> builder)
   {
      builder.HasKey(x => x.BusinessEntityID);
      builder.OwnsOne(x => x.Name).Property(c=> c.FirstName).HasColumnName("FirstName");
      builder.OwnsOne(x => x.Name).Property(c => c.MiddleName).HasColumnName("MiddleName");
      builder.OwnsOne(x => x.Name).Property(c => c.LastName).HasColumnName("LastName");
      builder.ToTable("Person", "Person");
   }
}

So Person owns Name

Usage:

 

 

Glad to see this feature come back? 🙂