Entity Framework Core 2 – Scalar function mapping

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

On this article I will explain one of them : Scalar function mapping
At last! we can use SQL SERVER‘s Scalar Function in LINQ to Entities !

How does it work ?

They must be statically declared, and must exactly respect the incoming / outgoing parameters.
You must also declare on the static method an attribute named DbFunction which takes in parameter the name of the scalar function and its schema to which it belongs.

Example :

[DbFunction("ufnGetStock", "dbo")]
 public static int GetProductStock(int productId)
 {
    throw new NotImplementedException();
 }

Microsoft in its examples implemented this method in the DbContext which gives for example:

public class AdventureWorksContext : DbContext
{
   public virtual DbSet<Product> Products { get; set; }

   [DbFunction("ufnGetStock", "dbo")]
   public static int GetProductStock(int productId)
   {
      throw new NotImplementedException();
   }

   protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
   {
      optionsBuilder.UseSqlServer(@const.connectionString);

      var lf = new LoggerFactory();
      lf.AddProvider(new MyLoggerProvider());
      optionsBuilder.UseLoggerFactory(lf);
    }

   protected override void OnModelCreating(ModelBuilder modelBuilder)
   {
      modelBuilder.HasDefaultSchema("Production");
      modelBuilder.ApplyConfiguration(new ProductConfiguration());
      base.OnModelCreating(modelBuilder);
   }
}

You can definitly implement this static method elsewhere like :

• A static method within a classical static class
• A extension method on the targeted Entity that the Scalar Function work with

Examples :

public static class ScalarFunctionsHelpers
{
   [DbFunction("ufnGetStock", "dbo")]
    public static int GetProductStock(int productId)
    {
       throw new NotImplementedException();
    }
}

public static class ScalarFunctionsExtentions
{
   [DbFunction("ufnGetStock", "dbo")]
    public static int GetProductStock(this Product product, int productId)
    {
       throw new NotImplementedException();
    }
}

Usage of these 3 scenarios:

public int GetProductStock(int productId)
{
   // DbContext example
   var query = _context.Products
   .Where(x => x.ProductID == productId)
   .Select(d => AdventureWorksContextDI.GetProductStock(d.ProductID));

   // Exemple of externalized in static class as static function
   query = _context.Products
   .Where(x => x.ProductID == productId)
   .Select(d=> ScalarFunctionsHelpers.GetProductStock(d.ProductID));

   // Exemple of externalized in static class as extension method
   query = _context.Products
   .Where(x => x.ProductID == productId)
   .Select(d => d.GetProductStock(d.ProductID));

   return query.FirstOrDefault();
}

Nice feature right? 😉