The final version of the Store Functions for EntityFramework 6.1.1+ Code First convention released

.NET Framework, Entity Framework 2 Minutes

Today I posted the final version of the Store Functions for Entity Framework Code First convention to NuGet. The instructions for downloading and installing the latest version of the package to your project are as described in my earlier blog post only you no longer have to select the “Include Pre-release” option when using UI or use the –Pre option when installing the package with the Package Manager Console. If you installed a pre-release version of this package to your project and would like to update to this version just run the Update-Package EntityFramework.CodeFirstStoreFunctions command from the Package Manager Console.

What’s new in this version?

This new version contains only one addition comparing to the beta-2 version – the ability to specify the name of the store type for parameters. This is needed in cases where a CLR type can be mapped to more than one store type. In case of the Sql Server provider there is only one type like this – the xml type. If you look at the Sql Server provider code (SqlProviderManifest.cs ln. 409) you will see that the store xml type is mapped to the EDM String type. This mapping is unambiguous when going from the store side. However the type inference in the Code First Functions convention works from the other end. First we have a CLR type (e.g. string) which maps to the EDM String type which is then used to find the corresponding store type by asking the provider. For the EDM String type the Sql Server the provider will return (depending on the facets) one of the nchar, nvarchar, nvarchar(max), char, varchar, varchar(max) types but it will never return the xml type. This makes it basically impossible to use the xml type when mapping store functions using the Code First Functions convention even though this is possible when using Database First EDMX based models.
Because, in general case, the type inference will not always work if multiple store types are mapped to one EDM Type I made it possible to specify the store type of a parameter using the new StoreType property of the ParameterTypeAttribute. For instance if you had a stored procedure called GetXmlInfo that takes an xml typed in/out parameter and returns some data (kind of a more advanced (spaghetti?) scenario but came from a real world application where the customer wanted to replace EDMX with Code First so they decided to use Code First Functions to map store functions and this was the only stored procedure they had problems with) you would use the following method to invoke this stored procedure:

[DbFunctionDetails(ResultColumnName = "Number")]
[DbFunction("MyContext", "GetXmlInfo")]
public virtual ObjectResult<int> GetXmlInfo(
    [ParameterType(typeof(string), StoreType = "XML")] ObjectParameter xml)
{
    return ((IObjectContextAdapter)this).ObjectContext
        .ExecuteFunction("GetXmlInfo", xml);
}

Because the parameter is in/out I had to use the ObjectParameter to pass the value and to read the value returned by the stored procedure. Because I used ObjectParameter I had to use the ParameterTypeAttribute to tell the convention what is the Clr type of the parameter. Finally, I also used the StoreType parameter which results in skipping asking the provider for the store type and using the type I passed.

That would be it. See my other blog posts here and here if you would like to see other supported scenarios. The code and issue tracking is on codeplex. Use and enjoy.

Published by moozzyk

Published

12 thoughts on “The final version of the Store Functions for EntityFramework 6.1.1+ Code First convention released”

  1. Great work!

    Is it also possible to support IEnumerable as an input parameter where T is a primitive type?

    For example I am trying to implement the “GROUP_CONCAT” function for MSSQL (To simulate the functionality of GROUP_CONCAT in MYSQL) as a custom stored procedure. (See: http://goo.gl/UEDel4)

    I have the stored procedure up and running but I cannot call it via a DbFunction because IEnumreable is currently not supported as a paramter.

    The signature for this method would look look like this:


    [DbFunction("MyContext", "GROUP_CONCAT")]
    public static string GroupConcat(IEnumerable<string> collection, string separator = ", ")
    {}

    An the usage scenario would be this.


    from blog context.Set()
    select new BlogDto
    {
    Name = blog.Title,
    Tags = CustomDbFunctions.GroupConcat(blog .Tags.Select(x => x.Name))
    }

    This should return the following:

    Name, | Tags
    My first blog, | Csharp, Linq, Ef
    My second blog | PHP, Ruby

    This would basically be the same like DBFunctions.StandardDeviation(IEnumerable ..) where I also have a sequence of inputs and the functions returns an aggregated value.

    Like

    Reply

    1. I don’t think the convention allows using collections as input parameters at the moment. As you pointed out it is possible in case of StandardDeviation so it might also be possible for users’ stored procedures. I would have to investigate to see how this can be done.

      I created a workitem for this https://codefirstfunctions.codeplex.com/workitem/8 but I am not sure when I will be able to get to it. I do accept contributions though.

      Thanks,
      Pawel

      Like

      Reply

      1. Thanks Pawel for your quick response!

        Good to hear you also think it should be possible. Would be great If you could find a solution to enable this scenario.

        If I find some time, I will probably also dig into it – but I am not sure when I am able to get to it either 😉

        Thanks,
        David

        Like

        Reply

  2. Thank you for all your work on this project. It’s been very useful in getting TVFs into my project using CodeFirst.

    One correction to your post: your NuGet command needs to be updated to
    Update-Package EntityFramework.CodeFirstStoreFunctions (you left out Store).

    Like

    Reply

  4. I have tried the Code First Store function dll to support oracle stored procedure in my application which developed using EF6. But while calling the stored procedure i m getting the below error. how can i solve this

    using CodeFirstStoreFunctions;

    public partial class OneVisionDBContext : DbContext
    {
    public OneVisionDBContext()
    : base(“name=OneVisionDBContext”)
    {
    }

    public virtual DbSet Countries { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
    modelBuilder.Conventions.Add(new FunctionsConvention(“C##TEST”));
    }

    [DbFunction(“OneVisionDBContext”, “ibp_country_getlist”)]
    public virtual ObjectResult GetCountryList(decimal countryId)
    {
    var countryIdParameter = new ObjectParameter(“P_CNTRY_PID”, countryId);
    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction(“ibp_country_getlist”, countryIdParameter);
    }
    }
    }

    Regards Dharma

    Like

    Reply

  5. error is

    {“ORA-06550: line 1, column 8:\nPLS-00201: identifier ‘C##TEST.ibp_country_getlist’ must be declared\nORA-06550: line 1, column 8:\nPL/SQL: Statement ignored”}

    Like

    Reply

    1. This error comes from Oracle. Unfortunately, I don’t know Oracle and have no idea what could cause this error. On the other hand a couple of folks confirmed that they were able to make the convention work on Oracle. Maybe you could ask them? I am not sure how helpful it is but I found this thread on Oracle’s forums where someone hit an issue that looks very similar. Not that I understand the discussion but apparently there is a difference between local and remote stored procedures.

      Thanks,
      Pawel

      Like

      Reply

Leave a comment