EntityFramework.Functions: Code First Functions for Entity Framework

EntityFramework.Functions library implements Entity Framework code first support for:

  • Stored procedures, with:
    • single result type
    • multiple result types
    • output parameter
  • Table-valued functions, returning
    • entity type
    • complex type
  • Scalar-valued functions
    • composable
    • non-composable
  • Aggregate functions
  • Built-in functions
  • Niladic functions
  • Model defined functions

EntityFramework.Functions library works on .NET 4.0, .NET 4.5, .NET 4.6, with Entity Framework 6.1.0 and later. Entity Framework is the only dependency of this library.

It can be installed through Nuget:

Install-Package EntityFramework.Functions -DependencyVersion Highest

See:

Source code

The source can be opened and built in Visual Studio 2015.

Nuget package project is used to build the nuget package from a .nuproj. It is already included in the source.

To view the sample database, or run the unit test against the sample database, please install SQL Server 2014 LocalDB or SQL Server 2016 LocalDB.

APIs

EntityFramework.Functions library provides a few simple APIs, following the pattern of Entity Framework and LINQ to SQL.

[Function]

[Function(FunctionType type, string name)] attribute derives from DbFunctionAttribute provided in Entity Framework. It is also similar to FunctionAttribute in LINQ to SQL. When a method is tagged with [Function], it maps to a database function or stored procedure. The FunctionType parameter is an enumeration, with the following members:

  • StoredProcedure
  • TableValuedFunction
  • ComposableScalarValuedFunction
  • NonComposableScalarValuedFunction
  • AggregateFunction
  • BuiltInFunction,
  • NiladicFunction,
  • ModelDefinedFunction

Examples for each function type can be found below.

The other name parameter specifies the database function/stored procedure that is mapped to. Even when C# method name is exactly the same as the mapped database function/stored procedure, this name string still has to be provided. This is required by Entity Framework.

[Function] has 2 settable properties:

  • Schema: It specifies the schema of the mapped database function/stored procedure, e.g. “dbo”.
  • ParameterTypeSemantics: It is of ParameterTypeSemantics type provided in Entity Framework. It defines the type semantics used to resolve function overloads. ParameterTypeSemantics is an enumeration of 3 members:
    • AllowImplicitConversion (the default)
    • AllowImplicitPromotion
    • ExactMatchOnly

Besides general [Function] attribute, a specific attribute is also provided for each function type:

  • [StoredProcedure]
  • [TableValuedFunction]
  • [ComposableScalarValuedFunction]
  • [NonComposableScalarValuedFunction]
  • [AggregateFunction]
  • [BuiltInFunction]
  • [NiladicFunction]
  • [ModelDefinedFunction]

[Parameter]

[Parameter] tags the function parameter to specify the mapped database function/stored procedure’s parameter name and type. It is similar to ParameterAttribute in LINQ to SQL.

[Parameter] has 3 settable properties:

  • Name: the name of the mapped parameter in database.
  • DbType: the tyoe of the mapped parameter in database, like “money”
  • ClrType: the type of the mapping .NET parameter.
    • In Entity Framework, when a parameter is a output parameter, it has to be of ObjectParameter type. In this case, the mapping CLR type cannot be predicted and has to be provided by [Parameter]’s ClrType property.
    • In other cases, ClrType property can be omitted. At runtime, If ClrType conflicts with CLR parameter’s actual declaration CLR type, an exception will be thrown.

[Parameter] can be omitted. when:

  • the parameter is not an output parameter
  • and its name is the same as the mapped database parameter

[Parameter] can also be used to tag the return value of method, to specify the DbType of the mapped database function return value, which is also the same as LINQ to SQL. Please see examples below.

[ResultType]

[ResultType(Type type)] is exactly the same as ResultTypeAttribute in LINQ to SQL. Its constructor accepts a Type parameter to specify the return type of stored procedure. Typically, when the stored procedure has multiple result types, the mapping method can be tagged with multiple [ResultType]s.

[ResultType] cannot be used for functions.

FunctionConvention and FunctionConvention<TFunctions>

FunctionConvention and FunctionConvention<TFunctions> implements Entity Framework’s IStoreModelConvention<EntityContainer> contract. They must be added to specify in what Type the mapping methods are located.

When the functions are added to entity model, the entity types and complex types used by functions should be added to entity model too. Entity Framework does not take care of types tagged with [ComplexType], so this library provides a AddComplexTypesFromAssembly extension method for this.

For convenience, 2 extension methods AddFunctions/AddFunction<TFunctions> are provided. When they are called:

  • FunctionConvention/FunctionConvention<TFunctions> is added to entity model.
  • AddComplexTypesFromAssembly is automatically called. In the assembly of TFunction, types tagged with [ComplextType] are added to entity model.

Examples

The following examples uses Microsoft’s AdventureWorks sample database for SQL Server 2014. The database can also be found in this library’s source repository on GitHub.

Add functions to entity model

Before calling any code first function, FunctionConvention or FunctionConvention<TFunctions> must be added to DbModelBuilder of the DbContext, so are the complex types used by functions:

public partial class AdventureWorks : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // Add functions on AdventureWorks to entity model.
        modelBuilder.Conventions.Add(new FunctionConvention<AdventureWorks>());

        // Add all complex types used by functions.
        modelBuilder.ComplexType<ContactInformation>();
        modelBuilder.ComplexType<ManagerEmployee>();
        // ...
    }
}

Here new FunctionConvention<T>() is equivalent to new FunctionConvention(typeof(T)). The non-generic version is provided because in C# static class cannot be used as type argument:

public partial class AdventureWorks : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // Add functions on AdventureWorks and StaticClass to entity model.
        modelBuilder.Conventions.Add(new FunctionConvention<AdventureWorks>());
        modelBuilder.Conventions.Add(new FunctionConvention(typeof(StaticClass)));

        // Add all complex types in the assembly of AdventureWorks.
        modelBuilder.AddComplexTypesFromAssembly(typeof(AdventureWorks).Assembly);
    }
}

Also, AddFunctions/AddFunction<TFunctions> extension methods are provided as a shortcut, which automatically add all complex types in the assembly of TFunctions.

public partial class AdventureWorks : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // Add functions and complex types to model.
        modelBuilder.AddFunctions<AdventureWorks>();
        modelBuilder.AddFunctions(typeof(AdventureWorksFunctions));
        modelBuilder.AddFunctions(typeof(BuiltInFunctions));
        modelBuilder.AddFunctions(typeof(NiladicFunctions));
    }
}

Stored procedure, with single result type

The AdventureWorks database has a sample stored procedure uspGetManagerEmployees. Its return type can be viewed with dm_exec_describe_first_result_set:

SELECT *
FROM sys.dm_exec_describe_first_result_set(N'dbo.uspGetManagerEmployees', NULL, 0);

image

An entity type or a complex type can be defined to represent above return type:

[ComplexType]
public class ManagerEmployee
{
    public int? RecursionLevel { get; set; }

    public string OrganizationNode { get; set; }

    public string ManagerFirstName { get; set; }

    public string ManagerLastName { get; set; }

    public int? BusinessEntityID { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }
}

It is tagged with [ComplexType], which is provided in System.ComponentModel.DataAnnotations.dll, and used by Entity Framework. When calling AddFunctions(typeof(TFunctions))/AddFunction<TFunctions>(), types tagged with [ComplexType] in the same assembly are added to entity model too.

Now the mapping method can be defined:

public partial class AdventureWorks
{
    public const string dbo = nameof(dbo);

    // Defines stored procedure returning a single result type: 
    // - a ManagerEmployee sequence.
    [Function(FunctionType.StoredProcedure, nameof(uspGetManagerEmployees), Schema = dbo)]
    public ObjectResult<ManagerEmployee> uspGetManagerEmployees(int? BusinessEntityID)
    {
        ObjectParameter businessEntityIdParameter = BusinessEntityID.HasValue
            ? new ObjectParameter(nameof(BusinessEntityID), BusinessEntityID)
            : new ObjectParameter(nameof(BusinessEntityID), typeof(int));

        return this.ObjectContext().ExecuteFunction<ManagerEmployee>(
            nameof(this.uspGetManagerEmployees), businessEntityIdParameter);
    }
}

In its body, it should call ExecuteFunction on ObjectContext. Here ObjectContext method is an extension method provided by this library.

Then it can be called as following:

[TestMethod]
public void CallStoredProcedureWithSingleResult()
{
    using (AdventureWorks database = new AdventureWorks())
    {
        ObjectResult<ManagerEmployee> employees = database.uspGetManagerEmployees(2);
        Assert.IsTrue(employees.Any());
    }
}

The above call is translated to the following SQL, which can be viewed with SQL Server Profiler:

exec [dbo].[uspGetManagerEmployees] @BusinessEntityID=2

Stored procedure, with output parameter

As fore mentioned, stored procedure’s output parameter is represented by ObjectParameter and must be tagged with [Parameter], with ClrType provided:

private const string uspLogError = nameof(uspLogError);

// Defines stored procedure accepting an output parameter.
// Output parameter must be ObjectParameter, with ParameterAttribute.ClrType provided.
[Function(FunctionType.StoredProcedure, uspLogError, Schema = dbo)]
public int LogError([Parameter(DbType = "int", ClrType = typeof(int))]ObjectParameter ErrorLogID) =>
    this.ObjectContext().ExecuteFunction(uspLogError, ErrorLogID);

Then it can be called as:

[TestMethod]
public void CallStoreProcedureWithOutParameter()
{
    using (AdventureWorks database = new AdventureWorks())
    {
        ObjectParameter errorLogId = new ObjectParameter("ErrorLogID", typeof(int)) { Value = 5 };
        int? rows = database.LogError(errorLogId);
        Assert.AreEqual(0, errorLogId.Value);
        Assert.AreEqual(typeof(int), errorLogId.ParameterType);
        Assert.AreEqual(-1, rows);
    }
}

The call is translated to:

declare @p1 int
set @p1=0
exec [dbo].[uspLogError] @ErrorLogID=@p1 output
select @p1

Stored procedure, with multiple result types

The following stored procedure returns 2 different types of results: a sequence of ProductCategory row(s), and a sequence of ProductSubcategory row(s).

CREATE PROCEDURE [dbo].[uspGetCategoryAndSubCategory]
    @CategoryID int
AS
BEGIN
    SELECT [Category].[ProductCategoryID], [Category].[Name]
        FROM [Production].[ProductCategory] AS [Category] 
        WHERE [Category].[ProductCategoryID] = @CategoryID;

    SELECT [Subcategory].[ProductSubcategoryID], [Subcategory].[Name], [Subcategory].[ProductCategoryID]
        FROM [Production].[ProductSubcategory] As [Subcategory]
        WHERE [Subcategory].[ProductCategoryID] = @CategoryID;
END
GO

The involved ProductCategory table and ProductSubcategory table  can be represented as:

public partial class AdventureWorks
{
    public const string Production = nameof(Production);

    public DbSet<ProductCategory> ProductCategories { get; set; }

    public DbSet<ProductSubcategory> ProductSubcategories { get; set; }
}

[Table(nameof(ProductCategory), Schema = AdventureWorks.Production)]
public partial class ProductCategory
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ProductCategoryID { get; set; }

    [MaxLength(50)]
    public string Name { get; set; }
}

[Table(nameof(ProductSubcategory), Schema = AdventureWorks.Production)]
public partial class ProductSubcategory
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ProductSubcategoryID { get; set; }

    [MaxLength(50)]
    public string Name { get; set; }
}

Above ProductCategory and ProductSubcategory classes are tagged with [Table], so they will be added to entity model automatically by Entity Framework.

Multiple return types can be specified by [ReturnType]. The return type defined on the method will be merged into the return types from [ReturnType]s, and be at the first position:

// Defines stored procedure returning multiple result types: 
// - a ProductCategory sequence.
// - a ProductSubcategory sequence.
[Function(FunctionType.StoredProcedure, nameof(uspGetCategoryAndSubCategory), Schema = dbo)]
[ResultType(typeof(ProductCategory))]
[ResultType(typeof(ProductSubcategory))]
public ObjectResult<ProductCategory> uspGetCategoryAndSubCategory(int CategoryID)
{
    ObjectParameter categoryIdParameter = new ObjectParameter(nameof(CategoryID), CategoryID);
    return this.ObjectContext().ExecuteFunction<ProductCategory>(
        nameof(this.uspGetCategoryAndSubCategory), categoryIdParameter);
}

Then it can be called to retrieve one ProductCategory sequence, and one ProductSubcategory sequence:

[TestMethod]
public void CallStoreProcedureWithMultipleResults()
{
    using (AdventureWorks database = new AdventureWorks())
    {
        // The first type of result type: a sequence of ProductCategory objects.
        ObjectResult<ProductCategory> categories = database.uspGetCategoryAndSubCategory(1);
        Assert.IsNotNull(categories.Single());
        // The second type of result type: a sequence of ProductCategory objects.
        ObjectResult<ProductSubcategory> subcategories = categories.GetNextResult<ProductSubcategory>();
        Assert.IsTrue(subcategories.Any());
    }
}

The SQL translation is normal:

exec [dbo].[uspGetCategoryAndSubCategory] @CategoryID=1

Table-valued function

The AdventureWorks sample database has a table-valued function, dbo.ufnGetContactInformation, its return type can be also represented as another complex type:

[ComplexType]
public class ContactInformation
{
    public int PersonID { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public string JobTitle { get; set; }

    public string BusinessEntityType { get; set; }
}

Then the ufnGetContactInformation function can be mapped by:

// Defines table-valued function, which must return IQueryable<T>.
[Function(FunctionType.TableValuedFunction, nameof(ufnGetContactInformation), Schema = dbo)]
public IQueryable<ContactInformation> ufnGetContactInformation(
    [Parameter(DbType = "int", Name = "PersonID")]int? personId)
{
    ObjectParameter personIdParameter = personId.HasValue
        ? new ObjectParameter("PersonID", personId)
        : new ObjectParameter("PersonID", typeof(int));

    return this.ObjectContext().CreateQuery<ContactInformation>(
        $"[{nameof(this.ufnGetContactInformation)}](@{nameof(personId)})", personIdParameter);
}

Its return type should be IQueryable<T>, so that it is composable in LINQ to Entities. And it can be called:

[TestMethod]
public void CallTableValuedFunction()
{
    using (AdventureWorks database = new AdventureWorks())
    {
        IQueryable<ContactInformation> employees = database.ufnGetContactInformation(1).Take(2);
        Assert.IsNotNull(employees.Single());
    }
}

The above ufnGetContactInformation call and Take call will be translated to one single SQL query:

exec sp_executesql N'SELECT TOP (2) 
    [top].[C1] AS [C1], 
    [top].[PersonID] AS [PersonID], 
    [top].[FirstName] AS [FirstName], 
    [top].[LastName] AS [LastName], 
    [top].[JobTitle] AS [JobTitle], 
    [top].[BusinessEntityType] AS [BusinessEntityType]
    FROM ( SELECT TOP (2) 
        [Extent1].[PersonID] AS [PersonID], 
        [Extent1].[FirstName] AS [FirstName], 
        [Extent1].[LastName] AS [LastName], 
        [Extent1].[JobTitle] AS [JobTitle], 
        [Extent1].[BusinessEntityType] AS [BusinessEntityType], 
        1 AS [C1]
        FROM [dbo].[ufnGetContactInformation](@PersonID) AS [Extent1]
    )  AS [top]',N'@PersonID int',@PersonID=1

Scalar-valued function, non-composable

For scalar-valued function. the return value becomes a primitive non-collection type.

// Defines scalar-valued function (non-composable), 
// which cannot be used in LINQ to Entities queries;
// and can be called directly.
[Function(FunctionType.NonComposableScalarValuedFunction, nameof(ufnGetProductStandardCost), Schema = dbo)]
[return: Parameter(DbType = "money")]
public decimal? ufnGetProductStandardCost(
    [Parameter(DbType = "int")]int ProductID,
    [Parameter(DbType = "datetime")]DateTime OrderDate)
{
    ObjectParameter productIdParameter = new ObjectParameter(nameof(ProductID), ProductID);
    ObjectParameter orderDateParameter = new ObjectParameter(nameof(OrderDate), OrderDate);
    return this.ObjectContext().ExecuteFunction<decimal?>(
        nameof(this.ufnGetProductStandardCost), productIdParameter, orderDateParameter).SingleOrDefault();
}

In this case, [Parameter] can tag its return type.

It can be called directly just like other above methods:

[TestMethod]
public void CallNonComposableScalarValuedFunction()
{
    using (AdventureWorks database = new AdventureWorks())
    {
        decimal? cost = database.ufnGetProductStandardCost(999, DateTime.Now);
        Assert.IsNotNull(cost);
        Assert.IsTrue(cost > 1);
    }
}

And the translated SQL is:

exec sp_executesql N'SELECT [dbo].[ufnGetProductStandardCost](@ProductID, @OrderDate)',N'@ProductID int,@OrderDate datetime2(7)',@ProductID=999,@OrderDate='2015-12-28 02:22:53.0353800'

However, since it is specified to be non-composable, it cannot be translated by Entity Framework in LINQ to Entities queries:

[TestMethod]
public void NonComposableScalarValuedFunctionInLinq()
{
    using (AdventureWorks database = new AdventureWorks())
    {
        try
        {
            database
                .Products
                .Where(product => product.ListPrice >= database.ufnGetProductStandardCost(999, DateTime.Now))
                .ToArray();
            Assert.Fail();
        }
        catch (NotSupportedException)
        {
        }
    }
}

This is by design of Entity Framework.

Scalar-valued function, composable

The composable scalar-valued function is very similar:

// Defines scalar-valued function (composable),
// which can only be used in LINQ to Entities queries, where its body will never be executed;
// and cannot be called directly.
[Function(FunctionType.ComposableScalarValuedFunction, nameof(ufnGetProductListPrice), Schema = dbo)]
[return: Parameter(DbType = "money")]
public decimal? ufnGetProductListPrice(
    [Parameter(DbType = "int")] int ProductID,
    [Parameter(DbType = "datetime")] DateTime OrderDate) => 
        Function.CallNotSupported<decimal?>();

The difference is, it works in LINQ to Entities queries, but cannot be called directly. As a result, its body will never be executed. So in the body, it can just throw an exception. This library provides a Function.,CallNotSupported help methods for convenience, which just throws a NotSupportedException.

[TestMethod]
public void ComposableScalarValuedFunctionInLinq()
{
    using (AdventureWorks database = new AdventureWorks())
    {
        IQueryable<Product> products = database
            .Products
            .Where(product => product.ListPrice <= database.ufnGetProductListPrice(999, DateTime.Now));
        Assert.IsTrue(products.Any());
    }
}

[TestMethod]
public void CallComposableScalarValuedFunction()
{
    using (AdventureWorks database = new AdventureWorks())
    {
        try
        {
            database.ufnGetProductListPrice(999, DateTime.Now);
            Assert.Fail();
        }
        catch (NotSupportedException)
        {
        }
    }
}

The above LINQ query, containing composable scalar-valued function, is translated to:

SELECT 
    CASE WHEN ( EXISTS (SELECT 
        1 AS [C1]
        FROM [Production].[Product] AS [Extent1]
        WHERE [Extent1].[ListPrice] <= ([dbo].[ufnGetProductListPrice](999, SysDateTime()))
    )) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT 
        1 AS [C1]
        FROM [Production].[Product] AS [Extent2]
        WHERE [Extent2].[ListPrice] <= ([dbo].[ufnGetProductListPrice](999, SysDateTime()))
    )) THEN cast(0 as bit) END AS [C1]
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]

Aggregate function

To demonstrate the mapping of user defined aggregate, the following aggregate function can be defined:

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined,
    IsInvariantToNulls = true,
    IsInvariantToDuplicates = false,
    IsInvariantToOrder = false,
    MaxByteSize = 8000)]
public class Concat : IBinarySerialize
{
    private const string Separator = ", ";

    private StringBuilder concat;

    public void Init()
    {
    }

    public void Accumulate(SqlString sqlString) => this.concat = this.concat?
        .Append(Separator).Append(sqlString.IsNull ? null : sqlString.Value)
        ?? new StringBuilder(sqlString.IsNull ? null : sqlString.Value);

    public void Merge(Concat concat) => this.concat.Append(concat.concat);

    public SqlString Terminate() => new SqlString(this.concat?.ToString());

    public void Read(BinaryReader reader) => this.concat = new StringBuilder(reader.ReadString());

    public void Write(BinaryWriter writer) => writer.Write(this.concat?.ToString() ?? string.Empty);
}

Concat takes 1 parameter, just like COUNT(), SUM(), etc. The following ConcatWith aggregate function accepts 2 parameters, a value and a separator:

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined,
    IsInvariantToNulls = true,
    IsInvariantToDuplicates = false,
    IsInvariantToOrder = false,
    MaxByteSize = 8000)]
public class ConcatWith : IBinarySerialize
{
    private StringBuilder concatWith;

    public void Init()
    {
    }

    public void Accumulate(SqlString sqlString, SqlString separator) => this.concatWith = this.concatWith?
        .Append(separator.IsNull ? null : separator.Value)
        .Append(sqlString.IsNull ? null : sqlString.Value)
        ?? new StringBuilder(sqlString.IsNull ? null : sqlString.Value);

    public void Merge(ConcatWith concatWith) => this.concatWith.Append(concatWith.concatWith);

    public SqlString Terminate() => new SqlString(this.concatWith?.ToString());

    public void Read(BinaryReader reader) => this.concatWith = new StringBuilder(reader.ReadString());

    public void Write(BinaryWriter writer) => writer.Write(this.concatWith?.ToString() ?? string.Empty);
}

Build these 2 classes into a .NET assembly, and add to database:

-- Create assembly.
CREATE ASSEMBLY [Dixin.Sql] 
FROM N'D:\OneDrive\Works\Drafts\CodeSnippets\Dixin.Sql\bin\Debug\Dixin.Sql.dll';
GO

-- Create aggregate from assembly.
CREATE AGGREGATE [Concat] (@value nvarchar(4000)) RETURNS nvarchar(max)
EXTERNAL NAME [Dixin.Sql].[Dixin.Sql.Concat];
GO

CREATE AGGREGATE [ConcatWith] (@value nvarchar(4000), @separator nvarchar(40)) RETURNS nvarchar(max)
EXTERNAL NAME [Dixin.Sql].[Dixin.Sql.ConcatWith];
GO

Now Concat and ConcatWith can be used in SQL:

SELECT [Subcategory].[ProductCategoryID], COUNT([Subcategory].[Name]), [dbo].[Concat]([Subcategory].[Name])
FROM [Production].[ProductSubcategory] AS [Subcategory]
GROUP BY [Subcategory].[ProductCategoryID];

SELECT [dbo].[Concat](Name) FROM Production.ProductCategory;

SELECT [Subcategory].[ProductCategoryID], COUNT([Subcategory].[Name]), [dbo].[ConcatWith]([Subcategory].[Name], N' | ')
FROM [Production].[ProductSubcategory] AS [Subcategory]
GROUP BY [Subcategory].[ProductCategoryID];

SELECT [dbo].[ConcatWith](Name, N' | ') FROM Production.ProductCategory;

To map them in C#, the following methods can be defined:

public static class AdventureWorksFunctions
{
    // Defines aggregate function, which must have one singele IEnumerable<T> or IQueryable<T> parameter.
    // It can only be used in LINQ to Entities queries, where its body will never be executed;
    // and cannot be called directly.
    [Function(FunctionType.AggregateFunction, nameof(Concat), Schema = AdventureWorks.dbo)]
    public static string Concat(this IEnumerable<string> value) => Function.CallNotSupported<string>();

    // Aggregate function with more than more parameter is not supported by Entity Framework.
    // The following cannot to translated in LINQ queries.
    // [Function(FunctionType.AggregateFunction, nameof(ConcatWith), Schema = AdventureWorks.dbo)]
    // public static string ConcatWith(this IEnumerable<string> value, string separator) => 
    //    Function.CallNotSupported<string>();
}

Apparently, aggregate functions cannot be called directly, so their bodies just throw exception. Unfortunately, above ConcatWith cannot be translated, because currently Entity Framework does not support aggregate function with more than one parameters.

They are defined as extension methods of IEnumerable<T>, so that they can easily be used in LINQ to :

[TestMethod]
public void AggregateFunctionInLinq()
{
    using (AdventureWorks database = new AdventureWorks())
    {
        var categories = database.ProductSubcategories
            .GroupBy(subcategory => subcategory.ProductCategoryID)
            .Select(category => new
            {
                CategoryId = category.Key,
                SubcategoryNames = category.Select(subcategory => subcategory.Name).Concat()
            })
            .ToArray();
        Assert.IsTrue(categories.Length > 0);
        categories.ForEach(category =>
            {
                Assert.IsTrue(category.CategoryId > 0);
                Assert.IsFalse(string.IsNullOrWhiteSpace(category.SubcategoryNames));
            });
    }
}

Above query will be translated to SQL with Concat call:

SELECT 
    1 AS [C1], 
    [GroupBy1].[K1] AS [ProductCategoryID], 
    [GroupBy1].[A1] AS [C2]
    FROM ( SELECT 
        [Extent1].[ProductCategoryID] AS [K1], 
        [dbo].[Concat]([Extent1].[Name]) AS [A1]
        FROM [Production].[ProductSubcategory] AS [Extent1]
        GROUP BY [Extent1].[ProductCategoryID]
    )  AS [GroupBy1]

The reason is Entity Framework does not support aggregate function with more than one parameters.

Built-in function

SQL Server provides a lot of built-in functions. They can be easily represented with [Function] tag. Take LEFT function as example:

It is a string function, returns the left part of a string with the specified number of characters. So, in C#, just defines a function accepting a string parameter and a int parameter, and returns a string:

public static class BuiltInFunctions
{
    [Function(FunctionType.BuiltInFunction, "LEFT")]
    public static string Left(this string value, int count) => Function.CallNotSupported<string>();
}

Again, it can only be used in LINQ to Entities and cannot be called directly. So in its body, it just simply throw an exception. It is implemented as an extension method of string, for convenience.

[TestMethod]
public void BuitInFunctionInLinq()
{
    using (AdventureWorks database = new AdventureWorks())
    {
        var categories = database.ProductSubcategories
            .GroupBy(subcategory => subcategory.ProductCategoryID)
            .Select(category => new
            {
                CategoryId = category.Key,
                SubcategoryNames = category.Select(subcategory => subcategory.Name.Left(4)).Concat()
            })
            .ToArray();
        Assert.IsTrue(categories.Length > 0);
        categories.ForEach(category =>
        {
            Assert.IsTrue(category.CategoryId > 0);
            Assert.IsFalse(string.IsNullOrWhiteSpace(category.SubcategoryNames));
        });
    }
}

The above query is translated to SQL with LEFT call:

SELECT 
    1 AS [C1], 
    [GroupBy1].[K1] AS [ProductCategoryID], 
    [GroupBy1].[A1] AS [C2]
    FROM ( SELECT 
        [Extent1].[K1] AS [K1], 
        [dbo].[Concat]([Extent1].[A1]) AS [A1]
        FROM ( SELECT 
            [Extent1].[ProductCategoryID] AS [K1], 
            LEFT([Extent1].[Name], 4) AS [A1]
            FROM [Production].[ProductSubcategory] AS [Extent1]
        )  AS [Extent1]
        GROUP BY [K1]
    )  AS [GroupBy1]

Niladic function

Niladic functions are functions called without parentheses, e.g., these SQL-92 niladic functions:

  • CURRENT_TIMESTAMP
  • CURRENT_USER
  • SESSION_USER
  • USER

In C#:

public static class NiladicFunctions
{
    [Function(FunctionType.NiladicFunction, "CURRENT_TIMESTAMP")]
    public static DateTime? CurrentTimestamp() => Function.CallNotSupported<DateTime?>();

    [Function(FunctionType.NiladicFunction, "CURRENT_USER")]
    public static string CurrentUser() => Function.CallNotSupported<string>();

    [Function(FunctionType.NiladicFunction, "SESSION_USER")]
    public static string SessionUser() => Function.CallNotSupported<string>();

    [Function(FunctionType.NiladicFunction, "SYSTEM_USER")]
    public static string SystemUser() => Function.CallNotSupported<string>();

    [Function(FunctionType.NiladicFunction, "USER")]
    public static string User() => Function.CallNotSupported<string>();
}

When they are called:

[TestMethod]
public void NiladicFunctionInLinq()
{
    using (AdventureWorks database = new AdventureWorks())
    {
        var firstCategory = database.ProductSubcategories
            .GroupBy(subcategory => subcategory.ProductCategoryID)
            .Select(category => new
            {
                CategoryId = category.Key,
                SubcategoryNames = category.Select(subcategory => subcategory.Name.Left(4)).Concat(),
                CurrentTimestamp = NiladicFunctions.CurrentTimestamp(),
                CurrentUser = NiladicFunctions.CurrentUser(),
                SessionUser = NiladicFunctions.SessionUser(),
                SystemUser = NiladicFunctions.SystemUser(),
                User = NiladicFunctions.User()
            })
            .First();
        Assert.IsNotNull(firstCategory);
        Assert.IsNotNull(firstCategory.CurrentTimestamp);
        Assert.IsTrue(DateTime.Now >= firstCategory.CurrentTimestamp);
        Assert.AreEqual("dbo", firstCategory.CurrentUser, true, CultureInfo.InvariantCulture);
        Assert.AreEqual("dbo", firstCategory.SessionUser, true, CultureInfo.InvariantCulture);
        Assert.AreEqual($@"{Environment.UserDomainName}\{Environment.UserName}", firstCategory.SystemUser, true, CultureInfo.InvariantCulture);
        Assert.AreEqual("dbo", firstCategory.User, true, CultureInfo.InvariantCulture);
    }
}

They are translated to SQL calls without parentheses:

SELECT 
    [Limit1].[C2] AS [C1], 
    [Limit1].[ProductCategoryID] AS [ProductCategoryID], 
    [Limit1].[C1] AS [C2], 
    [Limit1].[C3] AS [C3], 
    [Limit1].[C4] AS [C4], 
    [Limit1].[C5] AS [C5], 
    [Limit1].[C6] AS [C6], 
    [Limit1].[C7] AS [C7]
    FROM ( SELECT TOP (1) 
        [GroupBy1].[A1] AS [C1], 
        [GroupBy1].[K1] AS [ProductCategoryID], 
        1 AS [C2], 
        CURRENT_TIMESTAMP AS [C3], 
        CURRENT_USER AS [C4], 
        SESSION_USER AS [C5], 
        SYSTEM_USER AS [C6], 
        USER AS [C7]
        FROM ( SELECT 
            [Extent1].[K1] AS [K1], 
            [dbo].[Concat]([Extent1].[A1]) AS [A1]
            FROM ( SELECT 
                [Extent1].[ProductCategoryID] AS [K1], 
                LEFT([Extent1].[Name], 4) AS [A1]
                FROM [Production].[ProductSubcategory] AS [Extent1]
            )  AS [Extent1]
            GROUP BY [K1]
        )  AS [GroupBy1]
    )  AS [Limit1]

Model defined function

The following code defines a FormatName function for the Person model:

public static class ModelDefinedFunctions
{
    [ModelDefinedFunction(nameof(FormatName), "EntityFramework.Functions.Tests.Examples",
        @"(CASE 
            WHEN [Person].[Title] IS NOT NULL
            THEN [Person].[Title] + N' ' 
            ELSE N'' 
        END) + [Person].[FirstName] + N' ' + [Person].[LastName]")]
    public static string FormatName(this Person person) =>
        $"{(person.Title == null ? string.Empty : person.Title + " ")}{person.FirstName} {person.LastName}";

    [ModelDefinedFunction(nameof(ParseDecimal), "EntityFramework.Functions.Tests.Examples", "cast([Person].[BusinessEntityID] as Decimal(20,8))")]
    public static decimal ParseDecimal(this Person person) => Convert.ToDecimal(person.BusinessEntityID);
}

When FormatName is called in LINQ to Entities query:

[TestMethod]
public void ModelDefinedFunctionInLinqTest()
{
    using (AdventureWorks database = new AdventureWorks())
    {
        var employees = from employee in database.Persons
                        where employee.Title != null
                        let formatted = employee.FormatName()
                        select new
                        {
                            formatted,
                            employee
                        };
        var employeeData = employees.Take(1).ToList().FirstOrDefault();
        Assert.IsNotNull(employeeData);
        Assert.IsNotNull(employeeData.formatted);
        Assert.AreEqual(employeeData.employee.FormatName(), employeeData.formatted);
    }

    using (AdventureWorks database = new AdventureWorks())
    {
        var employees = from employee in database.Persons
                        where employee.Title != null
                        select new
                        {
                            Decimal = employee.ParseDecimal(),
                            Int32 = employee.BusinessEntityID
                        };
        var employeeData = employees.Take(1).ToList().FirstOrDefault();
        Assert.IsNotNull(employeeData);
        Assert.AreEqual(employeeData.Decimal, Convert.ToInt32(employeeData.Int32));
    }
}

The queries are translated to:

SELECT 
    [Limit1].[BusinessEntityID] AS [BusinessEntityID], 
    [Limit1].[C1] AS [C1], 
    [Limit1].[Title] AS [Title], 
    [Limit1].[FirstName] AS [FirstName], 
    [Limit1].[LastName] AS [LastName]
    FROM ( SELECT TOP (1) 
        [Extent1].[BusinessEntityID] AS [BusinessEntityID], 
        [Extent1].[Title] AS [Title], 
        [Extent1].[FirstName] AS [FirstName], 
        [Extent1].[LastName] AS [LastName], 
        CASE WHEN ([Extent1].[Title] IS NOT NULL) THEN [Extent1].[Title] + N' ' ELSE N'' END + [Extent1].[FirstName] + N' ' + [Extent1].[LastName] AS [C1]
        FROM [Person].[Person] AS [Extent1]
        WHERE [Extent1].[Title] IS NOT NULL
    )  AS [Limit1]

SELECT 
    [Limit1].[BusinessEntityID] AS [BusinessEntityID], 
    [Limit1].[C1] AS [C1]
    FROM ( SELECT TOP (1) 
        [Extent1].[BusinessEntityID] AS [BusinessEntityID], 
         CAST( [Extent1].[BusinessEntityID] AS decimal(20,8)) AS [C1]
        FROM [Person].[Person] AS [Extent1]
        WHERE [Extent1].[Title] IS NOT NULL
    )  AS [Limit1]

Version history

This library adopts the http://semver.org standard for semantic versioning.

  • 1.0.0: Initial release.
  • 1.0.1: Bug fix.
  • 1.1.0: Bug fix, and shortcut APIs for each function type:
    • [StoredProcedure]
    • [TableValuedFunction]
    • [ComposableScalarValuedFunction]
    • [NonComposableScalarValuedFunction]
    • [AggregateFunction]
    • [BuiltInFunction]
    • [NiladicFunction]
  • 1.2.0: Support model defined function with [ModelDefinedFunction].
  • 1.3.0: Support entity type and complex type defined in different assembly/namespace. Support table-valued function returning entity type or complex type.
  • 1.3.1: Fix a regression causing complex type not working properly with PostgreSQL.
  • 1.4.0: Sign assembly with strong named key. Fix minor issues.

34 Comments

  • Hi there,
    Thanks in advance for this....this has been a feature that should be native to EF, so it will provide great utility for the masses!
    However, I'm having trouble getting things to compile...
    Your FunctionAttribute constructor doesn't seem to match your examples:
    [AttributeUsage(AttributeTargets.Method)]
    public class FunctionAttribute : DbFunctionAttribute
    {
    public FunctionAttribute(string name, FunctionType functionType);
    //example is : [Function(FunctionType.NonComposableScalarValuedFunction, nameof(ufnGetProductStandardCost), Schema = dbo)] //params are switched, and there is no schema param.... am I missing something or did you release an old set of code?

    public bool IsAggregate { get; }
    public bool IsBuiltIn { get; }
    public bool IsComposable { get; }
    public bool IsNiladic { get; }
    public ParameterTypeSemantics ParameterTypeSemantics { get; set; }
    public string Schema { get; set; }
    public FunctionType Type { get; }
    }

  • Thanks for your feedback. The document/nuget package was updated today, but the nuget package showed up much later. Now they are in sync. Please try to install the latest nuget (1.0.1), it should work.

  • That's one I needed for a long time!! Cool, thank you!!

  • Hi Dixin,
    I just want to stop back by and thank you for this library. It has helped me take a mess of a legacy database stored procs at a new job and turn it into a usable EF based set of C# classes. I was unable to get built-in functions working by doing extension methods.
    I was however able to use them in the same manner as any other normal function mapping. i.e.

    [Function(FunctionType.BuiltInFunction, "UPPER")]
    [return: Parameter(DbType = "varchar")]
    public string UPPER([Parameter(DbType = "varchar")]string Expression)
    {
    ObjectParameter ExpressionParameter = new ObjectParameter(nameof(Expression), Expression);
    return this.ObjectContext().ExecuteFunction<string>(nameof(this.UPPER), ExpressionParameter).SingleOrDefault();
    }
    The nice part is that once they are added to your DBContext, you can use them from linqpad in order to make sure everything is working properly before putting it into an application. The trickiest part was getting the SQL to CLR types to map...which makes this page and the chart extremely handy: https://msdn.microsoft.com/en-us/library/Bb386947%28v=vs.100%29.aspx

  • Wow... what a great post! Thanks for the info, super helpful. If you ever need to merge some documents, here is www.altomerge.com a really useful tool. Very easy to navigate and use.

  • Hi Dixin,

    I'm having a little trouble getting this up and runnning. I've added the function convention as:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
    modelBuilder.Conventions.Add(new FunctionConvention<MyEntities>());
    }

    but now all calls to my database context fail with the error "The namespaceName parameter must be set for Table Valued Functions," even when I am not calling a TVF

    For example:

    public void GetCategories()
    {
    var db = new MyCategories();
    var categories = db.Categories; // this call fails
    }

    Where Categories is defined as

    public partial class MyEntities : DbContext
    {
    public DbSet<Category> Categories { get; set; }
    }

    Am I missing something important here?

  • Hi Dixin,

    I'm having a little trouble getting this up and runnning. I've added the function convention as:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
    modelBuilder.Conventions.Add(new FunctionConvention<MyEntities>());
    }

    but now all calls to my database context fail with the error "The namespaceName parameter must be set for Table Valued Functions," even when I am not calling a TVF

    For example:

    public void GetCategories()
    {
    var db = new MyCategories();
    var categories = db.Categories; // this call fails
    }

    Where Categories is defined as

    public partial class MyEntities : DbContext
    {
    public DbSet<Category> Categories { get; set; }
    }

    Am I missing something important here?

  • Yes I think you may miss something :)

    The exception is thrown from https://github.com/Dixin/EntityFramework.Functions/blob/master/EntityFramework.Functions/FunctionAttribute.cs, line 52. It seems you have defined a table-valued function in MyEntities. EF requires a namespace name for table-valued function. So you have to specify it:
    [Function(FunctionType.TableValuedFunction, "functionName", "namespaceName", Schema = "dbo")]
    Or:
    [TableValuedFunction("functionName", "namespaceName", Schema = "dbo")]

    Thanks.

  • Thanks for the quick reply. Is that to say that this error is thrown if there are any TVFs defined that haven't been namespaced? The line I showed you in my example is the first point that my application accesses MyEntities.

  • Hi Dixin,

    First of all thanks for putting this help out there to support the code first approach.

    I am trying to use this module for my code first approach, but I am stuck at a point and hope you can provide some pointers to fix this. I have some storedprocedures which use large varchar2 and number out parameters in it. This is causing me issues for e.g. default varchar2 size is 4000, so when I have scenarios when the stored procedure returns more than that I get buffer size error.

    To resolve this I looked at the code, but could not find a way to update the facetdescriptions for the function parameters. They are readonly :(. As a workaround I am using odp.net calls to the call these stored procedures for the time being. It would be nice if you could point me in the right direction to use an additional parameter in the ParameterAttribute and be able to set MaxLength for stored procedure parameters.

    Thanks,
    Sukesh

  • Hi there, Thanks in advance and congrats on the lib, very nice.
    I'm trying to use your lib with EF 6 + MySQL, here is what a have done,
    First:
    CREATE FUNCTION TESTEFUNCAO(dateValue DATETIME, intervalo INTEGER) RETURNS date
    return date_add(dateValue, interval intervalo month);
    Second:
    [EntityFramework.Functions.Function(EntityFramework.Functions.FunctionType.BuiltInFunction, "TESTEFUNCAO")]
    public static DateTime? TESTEFUNCAO(this DateTime? dateValue, int intervalo) {
    throw new NotSupportedException("Chamar somente de um LINQ");
    }
    Finally:
    var dta = DateTime.Now;
    var lista = new myContext().EntityTest.Where(x => x.date_field.TESTEFUNCAO(2) >= dta).ToList();
    foreach (var item in lista) {
    Console.WriteLine(item.date_field);
    }

    I'm getting the exception:
    The specified method 'System.Nullable`1[System.DateTime] TESTEFUNCAO(System.Nullable`1[System.DateTime], Int32)' on the type 'Helpers.BuiltInFunctions' cannot be translated into a LINQ to Entities store expression.

    Can you give me a hint about the problem? Is that something todo with MySQL?
    Thank you again.

  • Hi Dixin,
    Thanks a lot for your work.
    I'm using table valued functions and with complex types everyting is fine. But whe I try entity types like Person in your example an error is thrown:
    Schema specified is not valid. Errors:$metadata(0,0) : error 0005: Concurrency mode invalid
    Thanks for any help

  • Hi

    I have a Stored Procedure returns a nullable long value. But I get an error like this:

    System.Nullable`1[[System.Int64, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] for method AddRecord_SP is not supported in conceptual model as a structural type.

    The functions is declared in my DataContext class:

    [Function(FunctionType.StoredProcedure, "Sp_name", Schema = "acnt")]
    public virtual ObjectResult<Nullable<long>> AddRecord_SP(string i_Params)
    {
    var i_ParamsParameter = i_Params != null ?
    new ObjectParameter("I_Params", i_Params) :
    new ObjectParameter("I_Params", typeof(string));

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Nullable<long>>("Sp_name", i_ParamsParameter);
    }

  • I have the same issue:

    System.Nullable`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] for method {methodName} is not supported in conceptual model as a structural type.

  • Is it possible to provide methods that use DBContext and not ObjectContext?

  • Please help me for BuildIn Functions:
    Cast , Convert

  • Hi I am using Oracle 12c Unmanged Driver for DotNet
    Seems to have a problem with functions that are not in the same Schema

    Oracle Built in function name that i want to map is: UTL_MATCH.EDIT_DISTANCE

    Function Definition is:
    [BuiltInFunction("EDIT_DISTANCE",Schema= "UTL_MATCH")]
    public static int EditDistance(string value1, string value2) => Function.CallNotSupported<int>();

    Generated SQL:

    SELECT
    "Limit1"."C1" AS "C1"
    FROM ( SELECT
    EDIT_DISTANCE('shackleford', 'shackelford') AS "C1"

    The Schema is not used when calling the function, and it thus fail.
    It Should be " UTL_MATCH.EDIT_DISTANCE('shackleford', 'shackelford')" but instead it calls just "EDIT_DISTANCE('shackleford', 'shackelford')"

    Any help will be greatly appreciated.
    Thanks!

  • Great library, Dixin!

    There is an issue with EF whereby it cant translate Guid[].Max(), ie

    from item in items
    group new { item.GuidProperty1, item.GuidProperty2 } by item.Id into g
    select new { id = g.Key, maxGuidValue1 = g.Max(a => a.GuidProperty1), maxGuidValue2 = g.Max(a => a.GuidProperty2) }

    Is there a way to create an aggregate function using EntityFramework.Functions and register it against the SQL server built-in Max aggregate function, to overcome this EF shortcoming?

    Thank you

  • Boolean built-in functions (like CONTAINS or FREETEXT) are translated into invalid SQL:

    SELECT *
    FROM Content
    WHERE (CONTAINS(Title, N'abc')) = 1

  • Hi, Thanks for the good work.
    I am trying to implement your feature.
    I am struggling with the docs... I am not sure if what I am asking is possible.
    Can I use UDF with EF without mapping it to result entity?
    I am trying to use a function query after wrapping it

    [Function(FunctionType.TableValuedFunction, nameof(GetChildsByUserName), "Forex.Contracts.CheetahCrm", Schema = "dbo")]
    public virtual IQueryable<GetChildIds_Result> GetChildsByUserName([Parameter(DbType = "varchar", Name = "userName")]string userName)
    {
    if (string.IsNullOrEmpty(userName))
    {
    throw new ArgumentNullException(nameof(userName));
    }
    var userNameParameter = new ObjectParameter("userName", userName);

    return (this as IObjectContextAdapter).ObjectContext
    .CreateQuery<GetChildIds_Result>(
    $"[{nameof(this.GetChildsByUserName)}](@{nameof(userName)})", userNameParameter);
    //"[Context].[GetChildsByUserName](@userName)", userNameParameter);
    }
    [ComplexType]
    public class GetChildIds_Result
    {
    public int Id { get; set; }
    }

    Is it possible ?
    If yes- how is it possible ?

  • Hi Dixin,

    Thank you for this extremely helpful and well written blog.

    I was trying to call a stored procedure spDescriptiveStatistics as shown below. I have to invoke the stored procedure from within the select statement. As shown below:
    new StudentQuartiles() { Median = from sg in myStudentGroup select dbContext.spDescriptiveStatistics(sg.MarksObtained, 0).Single).Median }
    I’m getting a compliation error saying “Cannot implicitly convert type system.IEnumerable<float> to float”

    The SP returns me a single row containing details of Min, Median, Max, Q1,Q3, Average.

    Below are my queries:
    1. I would like to obtain the values for the Median, Min etc from within the select statement so that I can avoid performance issues.
    2. However I do not wish to perform the implementation as shown below since it will add a performance overhead.
    ObjectResult<StudentQuartiles> quartiles = dbcontext.spDescriptiveStatisticsOne(marksList, 0);

    public class MyTestClass
    {

    public void MyTestMethod()
    {
    using (MyDbContext database = new MyDbContext())
    {
    select new TestGroup1()
    {
    TestKey = newGroup.Key,
    ByOption =
    from student in StudentGroup
    group new StudentSubGroup() { FName = student.FirstName, LName = student.LastName, MarksObtained = student.Marks}
    by new StudentGroupBy() { JoiningDate = student.JoiningDate }
    into myStudentGroup

    select new StudentQuartiles() { Median = from sg in myStudentGroup select dbContext.spDescriptiveStatistics(sg.MarksObtained, 0).Single).Median }

    };
    }
    }
    }

    public class MyDbContext
    {

    [ComplexType]
    public class StudentQuartiles
    {
    public Single Min { get; set; }
    public Single Max { get; set; }
    public Single Median { get; set; }
    public Single Average { get; set; }
    public Single FirstQuartile { get; set; }
    public Single ThirdQuartile { get; set; }
    }

    public const string dbo = nameof(dbo);
    [Function(FunctionType.StoredProcedure, nameof(spDescriptiveStatisticsOne), Schema = dbo)]
    public ObjectResult<Quartiles> spDescriptiveStatisticsOne(List<int> @marks, int @QuartileMethodType)
    {

    ObjectParameter inputParameter1 = new ObjectParameter(nameof(@marks), marks);
    ObjectParameter inputParameter2 = new ObjectParameter(nameof(@QuartileMethodType), @QuartileMethodType);
    return this.ObjectContext().ExecuteFunction<Quartiles>(
    nameof(this.spDescriptiveStatistics), inputParameter1, inputParameter2);
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {

    modelBuilder.Conventions.Add(new FunctionConvention<MyDbContext>());
    modelBuilder.ComplexType<StudentQuartiles>();
    }

    }

    Thanks,
    Shweta

  • After adding aggregate clr function Add-Migration fails:

    System.Data.Entity.Core.MetadataException: Schema specified is not valid. Errors:
    (0,0) : error 0034: Unknown namespace or alias (collection[SqlServer).

  • I have run into the same issue as Alexander Kovtik. Trying to use Oracle's REGEXP_LIKE function and it is generating invalid SQL.

    It generates a query like so
    SELECT *
    FROM table
    WHERE REGEXP_LIKE(field, 'expression string') = 1

    when it should generate a query like
    SELECT *
    FROM table
    WHERE REGEXP_LIKE(field, 'expression string')

    I have defined the function like so
    [Function(FunctionType.BuiltInFunction, "REGEXP_LIKE")]
    public static bool REGEXP_LIKE(string columnValue, string regularExpression)
    {
    return Function.CallNotSupported<bool>();
    }

    This library is awesome, and supporting these types of functions will make it even better.

  • I have a Split table-valued function, which takes a string and a delimiter and returns the split values.

    SELECT * FROM Split('a|b|c', '|')
    returns
    a
    b
    c

    IF EXISTS (SELECT * FROM sys.all_objects WHERE name = 'Split' AND Type = 'TF')
    DROP FUNCTION dbo.Split
    GO
    CREATE FUNCTION dbo.Split
    (
    @String varchar(MAX)
    ,@Delimiter char(1)
    )
    RETURNS @TempTable TABLE (Value varchar(MAX))
    AS
    BEGIN
    DECLARE @Idx int
    DECLARE @Slice varchar(8000)

    SELECT @Idx = 1

    IF len(@String) < 1 OR @String IS NULL
    RETURN

    WHILE @Idx != 0
    BEGIN
    SET @Idx = charindex(@Delimiter, @String)

    IF @Idx != 0
    SET @Slice = left(@String, @Idx - 1)
    ELSE
    SET @Slice = @String

    if (len(@slice) > 0)
    INSERT INTO @TempTable (Value) VALUES (@Slice)

    SET @String = right(@String, len(@String) - @Idx)

    IF len(@String) = 0
    BREAK
    END
    RETURN
    END
    GO

    I made the following:

    [ComplexType]
    public class ValueVarChar
    {
    public string Value { get; set; }
    }

    And I'm trying to set up the function with:

    [Function(FunctionType.TableValuedFunction, "Split", "DataModel", Schema = "dbo")]
    public IQueryable<ValueVarChar> Split([Parameter(DbType = "varchar", Name = "String" )]string str,
    [Parameter(DbType = "char", Name = "Delimiter")]char delimiter)
    {
    return this.ObjectContext().CreateQuery<ValueVarChar>("dbo.Split(@String, @Delimiter)",
    new ObjectParameter("String", str),
    new ObjectParameter("Delimiter", delimiter));
    }

    But, I'm getting the following error:

    Type FullName in method Split is not supported in conceptual model.

  • Hi Dixin, this very usefull package.
    Nevertheless i can not still figure out some situation.
    For instance with CAST:

    I have written functin

    [ModelDefinedFunction(nameof(StringToInt), "NewJdsProvider.Context", @"Cast(str1 as int)")]
    public static int? StringToInt(this string str1)
    {
    int result = 0;
    return int.TryParse(str1, out result) ? result : (int?)null;
    }

    which gives me error:System.String for method StringToInt is not supported in conceptual model as a structural type. This can be caused by a failure to register this type as complex. For more information, see https://msdn.microsoft.com/en-us/library/gg679474.aspx

    Which means that ModelDefinedFunction can be used only with ComplexType. However why it should?

    To use CAST as builtin function also not clear,. What should be used for second parameter (as a type)? Do you have full documentation for your package?
    Eager to hear from you,
    Thank you in advance.


  • Hi Dixin,

    Your blog is really very helpful.

    Coming to Multiple Result sets returning from stored procedure,
    I am having an entity with enum property which is Integer column in Database.
    If i try to return this entity values from database by using ResultType annotation,
    On screen logging in itself am getting below exception.

    MyEntityName for method MyMethodName is not supported in conceptual model as a structural type.

    Could you tell me why it is happening?

  • Hi Dixin, thanks for your blog. It's really helpful. I successfully tested it on MS SQL database.
    I want to ask if this library is suitable for PostgreSQL database too?

    Regards
    Richard

  • Add-Migration stop working after set "modelBuilder.AddFunctions<DbContext>();"

  • Hi Dixin,
    Thanks - this has been a lifesaver. I'm trying to convert a legacy edmx solution to code first - we ran into a limit on the number of tables in the edmx.
    I'm running into the same problem as a couple of the other posters:
    System.Nullable`1[[System.Int64, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] for method AddRecord_SP is not supported in conceptual model as a structural type.

    I'm using the method that was generated from the EDMX and have added the StoedProcedure attribute:
    [StoredProcedure(nameof(IdentifierInvoiceGet), Schema = "dbo")]
    public virtual ObjectResult<long> IdentifierInvoiceGet()
    {
    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<long>("IdentifierInvoiceGet");
    }

    Is there a solution?

  • For anyone looking for a solution/workaround to the "not supported in conceptual model as a structural type." problem...
    I was able to change my calls to use ExecuteStoreQuery instead of ExecuteFunction.
    This StackOverflow question has a tt that will generate ExecuteStoreQuery statements from an EDMX:
    https://stackoverflow.com/questions/8462161/entity-framework-ef4-1-stored-procedure-could-not-be-found-in-the-container

    So, the original version:
    public virtual ObjectResult<long> IdentifierInvoiceGet()
    {
    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<long>("IdentifierInvoiceGet");
    }

    Converts to:
    public virtual ObjectResult<Nullable<long>> IdentifierInvoiceGet()
    {
    return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<Nullable<long>>("IdentifierInvoiceGet");
    }

    Or a method with parameters:
    public virtual ObjectResult<Nullable<long>> LookupVisualIndicatorsUpdate(string config)
    {
    var configParameter = config != null ?
    new SqlParameter("config", config) :
    new SqlParameter("config", typeof(string));

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteStoreQuery<Nullable<long>>("LookupVisualIndicatorsUpdate @config", configParameter);
    }

  • Hi Dixin, thanks a lot for this page. A question: Sql server 2005 is supported from this Library?

  • Same problem as Bruno Melo, Add-Migration is broken after modelBuilder.AddFunctions<DbContext>(); Any fix or workaround ?
    Altogether great package ! Shame that it is not supported natively by EF.

  • Hi Dixin,

    Very appreciated job and thanks for sharing it.
    One question:
    Do you think is possible to AddFunction to the model without having to use the DbFunction Entity Framework attribute.
    I try to explain better...
    The Function attribute you defined is inherited from EF DbFunction attribute. I tried to abstract it defining a Function attribute inherited from generic Attribute (adding the missing namespacename and functionname properties). I changed a little bit your code and extension methods according to the new defined Function attribute. Of course it doesn't work :-) even if the Functions are correctly defined into the storage model so... again the question:
    Is it possible to avoid the usage of the EF DbFunction attribute?

    Thanks in advance
    Luigi

  • thank you, now my problem is finished after reading your article, thank you

Add a Comment

As it will appear on the website

Not displayed

Your website