Entity Framework Core 2 – Pluralization and Singularization

 

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

On this article I will explain one of them : Pluralization and Singularization

There is a new IPluralizer interface. It can be used to pluralize table names when EF is generating the database (dotnet ef database update) or entities when generating classes from it (Scaffold-DbContext). The way to use it is somewhat tricky, as we need to have a class implementing IDesignTimeServices, and this class will be discovered automatically by these tools.

In this example I provide a custom implementation with the Nuget package Inflector

Example:

public class CustomPluralizer : IPluralizer
{
   public string Pluralize(string name)
   {
      return Inflector.Inflector.Pluralize(name) ?? name;
   }
   public string Singularize(string name)
   {
      return Inflector.Inflector.Singularize(name) ?? name;
   }
}
public class CustomDesignTimeServices : IDesignTimeServices
{
   public void ConfigureDesignTimeServices(IServiceCollection services)
   {
     services.AddSingleton<IPluralizer, CustomPluralizer>();
   }
}

That’s it 🙂

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! 🙂