The Beta Version of Store Functions for EntityFramework 6.1.1+ Code First Available

.NET Framework, Entity Framework, Visual Studio 10 Minutes

This is very exciting! Finally after some travelling and getting the beta-2 version of the Second Level Cache for EF 6.1+ out the door I was able to focus on store functions for EF Code First. I pushed quite hard for the past two weeks and here it is – the beta version of the convention that enables using store functions (i.e. stored procedures, table valued functions etc.) in applications that use Code First approach and Entity Framework 6.1.1 (or newer). I am more than happy with the fixes and new features that are included in this release. Here is the full list:

  • Support for .NET Framework 4 – the alpha NuGet package contained only assemblies built against .NET Framework 4.5 and the convention could not be used when the project targeted .NET Framework 4. Now the package contains assemblies for both .NET Framework 4 and .NET Framework 4.5.
  • Nullable scalar parameters and result types are now supported
  • Support for type hierarchies – previously when you tried using a derived type the convention would fail because it was not able to find a corresponding entity set. This was fixed by Martin Klemsa in his contribution
  • Support for stored procedures returning multiple resultsets
  • Enabling using a different name for the method than the name of the stored procedure/function itself – a contribution from Angel Yordanov
  • Enabling using non-DbContext derived types (including static classes) as containers for store function method stubs and methods used to invoke store functions – another contribution from Angel Yordanov
  • Support for store scalar functions (scalar user defined functions)
  • Support for output (input/output really) parameters for stored procedures

This is a pretty impressive list. Let’s take a closer look at some of the items from the list.

Support for stored procedure returning multiple resultsets

Starting with version 5 Entity Framework runtime has a built-in support for stored procedures returning multiple resultsets (only when targeting .NET Framework 4.5). This is not a very well-known feature which is not very surprising given that up to now it was practically unusable. Neither Code First nor EF Tooling supports creating models with store functions returning multiple resultsets. There are some workarounds like dropping to ADO.NET in case of Code First (http://msdn.microsoft.com/en-us/data/JJ691402.aspx) or editing the Edmx file manually (and losing the changes each time the model is re-generated) for Database First but they do not really change the status of the native support for stored procedures returning multiple resultsets as being de facto an unfeature. This is changing now – it is now possible to decorate the method that invokes the stored procedure with the DbFunctionDetails attribute and specify return types for subsequent resultsets and the convention will pick it up and create metadata EF requires to execute such a stored procedure.

Using a different name for the method than the name of the stored procedure

When the alpha version shipped the name of method used to invoke a store function had to match the name of the store function. This was quite unfortunate since most of the time naming conventions used for database objects are different from naming conventions used in the code. This is now fixed. Now, the function name passed to the DbFunction attribute will be used as the name of the store function.

Support for output parameters

The convention now supports stored procedures with output parameters. I have to admit it ended a bit rough because of how the value of the output parameter is being set (at least in case of Sql Server) but if you are in a situation where you have a stored procedure with an output parameter it is better than nothing. The convention will treat a parameter as an output parameter (in fact it will be an input/output parameter) if the type of the parameter is ObjectParameter. This means you will have to create and initialize the parameter yourself before passing it to the method. This is because the output value (at least for Sql Server) is set after all the results returned by the stored procedure have been consumed. Therefore you need to keep a reference to the parameter to be able to read the output value after you have consumed the results of the query. In addition because the actual type of the parameter will be only known at runtime and not during model discovery all ObjectParameter parameters have to be decorated with the ParameterTypeAttribute which specifies the type that will be used to build the model. Finally the name of the parameter in the method must match the name of the parameter in the database (yeah, I had to debug EF code to figure out why things did not work) – fortunately casing does not matter. As I said – it’s quite rough but should work once you align all the moving pieces correctly.

Exmple 1

The following example illustrates how to use the functionality described above. It uses a stored procedure with an output parameter and returning multiple resultsets. In addition the name of the method used to invoke the store procedure (MultipleResultSets) is different from the name of the stored procedure itself (CustomersOrdersAndAnswer). 

internal class MultupleResultSetsContextInitializer : DropCreateDatabaseAlways<MultipleResultSetsContext>
{
    public override void InitializeDatabase(MultipleResultSetsContext context)
    {
        base.InitializeDatabase(context);

        context.Database.ExecuteSqlCommand(
        "CREATE PROCEDURE [dbo].[CustomersOrdersAndAnswer] @Answer int OUT AS " +
        "SET @Answer = 42 " +
        "SELECT [Id], [Name] FROM [dbo].[Customers] " +
        "SELECT [Id], [Customer_Id], [Description] FROM [dbo].[Orders] " +
        "SELECT -42 AS [Answer]");
    }

    protected override void Seed(MultipleResultSetsContext ctx)
    {
        ctx.Customers.Add(new Customer
        {
            Name = "ALFKI",
            Orders = new List<Order>
                {
                    new Order {Description = "Pens"},
                    new Order {Description = "Folders"}
                }
        });

        ctx.Customers.Add(new Customer
        {
            Name = "WOLZA",
            Orders = new List<Order> { new Order { Description = "Tofu" } }
        });
    }
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Order> Orders { get; set; }
}

public class Order
{
    public int Id { get; set; }
    public string Description { get; set; }
    public virtual Customer Customer { get; set; }
}

public class MultipleResultSetsContext : DbContext
{
    static MultipleResultSetsContext()
    {
        Database.SetInitializer(new MultupleResultSetsContextInitializer());
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Add(
            new FunctionsConvention<MultipleResultSetsContext>("dbo"));
    }

    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }

    [DbFunction("MultipleResultSetsContext", "CustomersOrdersAndAnswer")]
    [DbFunctionDetails(ResultTypes = 
        new[] { typeof(Customer), typeof(Order), typeof(int) })]
    public virtual ObjectResult<Customer> MultipleResultSets(
        [ParameterType(typeof(int))] ObjectParameter answer)
    {
        return ((IObjectContextAdapter)this).ObjectContext
            .ExecuteFunction<Customer>("CustomersOrdersAndAnswer", answer);
    }
}

class MultipleResultSetsSample
{
    public void Run()
    {
        using (var ctx = new MultipleResultSetsContext())
        {
            var answerParam = new ObjectParameter("Answer", typeof (int));

            var result1 = ctx.MultipleResultSets(answerParam);

            Console.WriteLine("Customers:");
            foreach (var c in result1)
            {
                Console.WriteLine("Id: {0}, Name: {1}", c.Id, c.Name);
            }

            var result2 = result1.GetNextResult<Order>();

            Console.WriteLine("Orders:");
            foreach (var e in result2)
            {
                Console.WriteLine("Id: {0}, Description: {1}, Customer Name {2}", 
                    e.Id, e.Description, e.Customer.Name);
            }

            var result3 = result2.GetNextResult<int>();
            Console.WriteLine("Wrong Answer: {0}", result3.Single());

            Console.WriteLine("Correct answer from output parameter: {0}", 
               answerParam.Value);
        }
    }
}

The first half of the sample is just setting up the context and is rather boring. The interesting part starts at the MultipleResultSets method. The method is decorated with two attributes – the DbFunctionAttribute and the DbFunctionDetailsAttribute. The DbFunctionAttribute tells EF how the function will be mapped in the model. The first parameter is the namespace which in case of Code First is typically the name of the context type. The second parameter is the name of the store function in the model. The convention treats it also as the name of the store function. Note that this name has to match the name of the stored procedure (or function) in the database and also the name used in the ExecuteFunction call. The DbFunctionDetailsAttribute is what makes it possible to invoke a stored procedure returning multiple resultsets. The ResultTypes parameter allows specifying multiple types each of which defines the type of items returned in subsequent resultsets. The types have to be types that are part of the model or, in case of primitive types, they have to have an Edm primitive type counterpart. In our sample the stored procedure returns Customer entities in the first resultset, Order entities in the second resultset and int values in the third resultset. One important thing to mention is that the first type in the ResultTypes array must match the generic type of the returned ObjectResult. To invoke the procedure (let’s ignore the parameter for a moment) you just call the method and enumerate the results. Once the results are consumed you can move to the next resultset. You do it by calling the GetNextResult<T> method where T is the element type of the next resultset. Note that you call the GetNextResult<T> on the previously returned ObjectResult<> instance. Finally let’s take a look at the parameter. As described above it is of the ObjectParameter type to indicate an output parameter. It is decorated with the ParameterTypeAttribute which tells what is the type of the attribute. Its name is the same as the name of the parameter in the stored procedure (less the casing). We create an instance of this parameter before invoking the stored procedure, enumerate all the results and only then read the value. If you tried reading the value before enumerating all the resultsets it would be null.
Running the sample code produces the following output:

Customers:
Id: 1, Name: ALFKI
Id: 2, Name: WOLZA
Orders:
Id: 1, Description: Pens, Customer Name ALFKI
Id: 2, Description: Folders, Customer Name ALFKI
Id: 3, Description: Tofu, Customer Name WOLZA
Wrong Answer: -42
Correct answer from output parameter: 42
Press any key to continue . . .

Enabling using non-DbContext derived types (including static classes) as containers for store function method stubs and methods used to invoke store functions

In the alpha version all the methods that were needed to handle store functions had to live inside a DbContext derived class (the type was a generic argument to the FunctionsConvention<T> where T was constrained to be a DbContext derived type). While this is a convention used by EF Tooling when generating code for Database First approach it is not a requirement. Since it blocked some scenarios (e.g. using extension methods which have to live in a static class) the requirement has been lifted by adding a non-generic version of the FunctionsConvention which takes the type where the methods live as a constructor parameter.

Support for store scalar functions

This is another a not very well-known EF feature. EF actually knows how to invoke user defined scalar functions. To use a scalar function you need to create a method stub. Method stubs don’t have implementation but when used inside a query they are recognized by the EF Linq translator and translated to a udf call.

Example 2
This example shows how to use non-DbContext derived classes for methods/method stubs and how to use scalar UDFs.

internal class ScalarFunctionContextInitializer : DropCreateDatabaseAlways<ScalarFunctionContext>
{
    public override void InitializeDatabase(ScalarFunctionContext context)
    {
        base.InitializeDatabase(context);

        context.Database.ExecuteSqlCommand(
            "CREATE FUNCTION [dbo].[DateTimeToString] (@value datetime) " + 
            "RETURNS nvarchar(26) AS " +
            "BEGIN RETURN CONVERT(nvarchar(26), @value, 109) END");
    }

    protected override void Seed(ScalarFunctionContext ctx)
    {
        ctx.People.AddRange(new[]
        {
            new Person {Name = "John", DateOfBirth = new DateTime(1954, 12, 15, 23, 37, 0)},
            new Person {Name = "Madison", DateOfBirth = new DateTime(1994, 7, 3, 11, 42, 0)},
            new Person {Name = "Bronek", DateOfBirth = new DateTime(1923, 1, 26, 17, 11, 0)}
        });
    }
}

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime DateOfBirth { get; set; }
}

internal class ScalarFunctionContext : DbContext
{
    static ScalarFunctionContext()
    {
        Database.SetInitializer(new ScalarFunctionContextInitializer());
    }

    public DbSet<Person> People { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Add(
            new FunctionsConvention("dbo", typeof (Functions)));
    }
}

internal static class Functions
{
    [DbFunction("CodeFirstDatabaseSchema", "DateTimeToString")]
    public static string DateTimeToString(DateTime date)
    {
        throw new NotSupportedException();
    }
}

internal class ScalarFunctionSample
{
    public void Run()
    {
        using (var ctx = new ScalarFunctionContext())
        {
            Console.WriteLine("Query:");

            var bornAfterNoon =
               ctx.People.Where(
                 p => Functions.DateTimeToString(p.DateOfBirth).EndsWith("PM"));

            Console.WriteLine(bornAfterNoon.ToString());

            Console.WriteLine("People born after noon:");

            foreach (var person in bornAfterNoon)
            {
                Console.WriteLine("Name {0}, Date of birth: {1}",
                    person.Name, person.DateOfBirth);
            }
        }
    }
}

In the above sample the method stub for the scalar store function lives in the Functions class. Since this class is static it cannot be a generic argument to the FunctionsConvention<T> type. Therefore we use the non-generic version of the convention to register the convention (in the OnModelCreating method).
The method stub is decorated with the DbFunctionAttribute which tells the EF Linq translator what function should be invoked. The important thing is that scalar store functions operate on a lower level (they exist only in the S-Space and don’t have a corresponding FunctionImport in the C-Space) and therefore the namespace used in the DbFunctionAttribute is no longer the name of the context but has always to be CodeFirstDatabaseSchema. Another consequence is that the return and parameter types must be of a type that can be mapped to a primitive Edm type. Once all this conditions are met you can use the method stub in Linq queries. In the sample the function is converting the date of birth to a string in a format which ends with “AM” or “PM”. This makes it possible to easily find people who were born before or after noon just by checking the suffix. All this happens on the database side – you can tell this by looking at the results produced when running this code which contain the SQL query the linq query was translated to:

Query:
SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[DateOfBirth] AS [DateOfBirth]
    FROM [dbo].[People] AS [Extent1]
    WHERE [dbo].[DateTimeToString]([Extent1].[DateOfBirth]) LIKE N'%PM'
People born after noon:
Name John, Date of birth: 12/15/1954 11:37:00 PM
Name Bronek, Date of birth: 1/26/1923 5:11:00 PM
Press any key to continue . . .

That’s pretty much it. The convention ships on NuGet – the process of installing the package is the same as it was for the alpha version and can be found here. If you are already using the alpha version in your project you can upgrade the package to the latest version with the Update-Package command. The code (including the samples) is on codeplex.
I would like to thank again Martin and Angel for their contributions.
Play with the beta version and report bugs before I ship the final version.

Published by moozzyk

Published

25 thoughts on “The Beta Version of Store Functions for EntityFramework 6.1.1+ Code First Available”

  1. Pingback: Support for Store Functions (TVFs and Stored Procs) in Code First (Entity Framework 6.1) | Code, the Universe and everything...

  2. Hi Pawel, I just wanted to thank you for all your hard work regarding this.
    It is really a huge feature and timesaver (or missing component from the EF team, depending on how to look at it). 🙂
    Almost all our projects will benefit from this, so I really look forward to use it.
    Thanks again!

    Liked by 1 person

    Reply
  4. Pingback: The final version of the Store Functions for EntityFramework 6.1.1+ Code First convention released | Code, the Universe and everything...

  6. Hi,

    Thanks for the plugin.
    I wonder if your plugin support Scalar Functions that returns *structs* like DateTime/int/long/bool… and string class?

    Thanks

    Like

    Reply

  7. I think I’m pretty close to getting this working, but I get this error:
    Cannot find either column “dbo” or the user-defined function or aggregate “dbo.PlayerPositionShort”, or the name is ambiguous.

    I have the scalar function in my DB as “PlayerPositionShort” and it is on the dbo schema.

    I actually can do this from TOAD, and it works:
    SELECT dbo.PlayerPositionShort(Id)
    FROM Players

    Here’s how I have my LINQ query:

    players = from p in db.NhlPlayers
    select new PivmNhlPlayer
    {
    NhlPlayerId = p.NhlPlayerId,
    FirstName = p.FirstName,
    LastName = p.LastName,
    LwFlag = p.LwFlag,
    CFlag = p.CFlag,
    RwFlag = p.RwFlag,
    DFlag = p.DFlag,
    GFlag = p.GFlag,
    PositionShort = FhlContext.Functions.PlayerPositionShort(p.Id)
    };

    (PivmNhlPlayer is just a view model I have with the appropriate columns I want in it, and PositionShort is declared in this view model as a string, which is the same return type as the Scalar Function I created.. well.. that’s Varchar(4)).

    I’ve simplified the scalar database function down for testing to this:

    CREATE FUNCTION [dbo].[PlayerPositionShort]
    (@NhlPlayerId INT)
    RETURNS VARCHAR(4)
    AS
    BEGIN

    RETURN ‘TEST’;
    END;

    In my FhlContext (db context) class I have this:
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
    modelBuilder.Conventions.Add(new FunctionsConvention(“dbo”, typeof(Functions)));
    }

    public static class Functions
    {
    [DbFunction(“CodeFirstDatabaseSchema”, “PlayerPositionShort”)]
    public static string PlayerPositionShort(int NhlPlayerId)
    {
    throw new NotSupportedException();
    }
    }

    If I remove “PositionShort” portion from my view model, index page and also the LINQ query, everything runs fine. But as soon as I try and reference this scalary function, it errors with the above error (Cannot find either column “dbo” or the user-defined function or aggregate “dbo.PlayerPositionShort”, or the name is ambiguous.). Any ideas?

    Like

    Reply

  8. Oh my god, I’m an idiot. I apparently was pointing to the wrong database in TOAD, and created the function on the wrong database. So in TOAD, I was looking at one database, in MVC, another. I’ve fixed this and it works now. While I’m here, thanks for this great article explaining on how to do this. If not for my tired brain making goofy mistakes this would have worked quite smoothly.

    Like

    Reply

  9. hi friend.
    i am troubling when call oracle stroed procedure.
    because i want a same way to call sqlserver mysql orace etc.
    is it support about oracle ? or in plan?
    thank you !

    Like

    Reply

    1. Hi, Unfortunately I have not tried this on Oracle but because it sets the EF up the same way you would set it up when using EDMX and AFAIK you can call Oracle stored procedures when using EDMX I think it should work.

      Thanks,
      Pawel

      Like

      Reply

      1. thanks the reply
        Forgot to say ,current i use code first to access database
        i don’t like using EDMX way ,because the EDMX xml file has many mapping detial with specify database,it is hard to change database, is it right?
        if i fork you project and implement oracle support, is it possible in technology ? i am not every clear know about code first.
        thank you.

        Like

        Reply

      2. Hi Pawel,
        thank you for all your hard work .you lib is already support oracle .
        i find the right way to call oracle procedure . 😀
        thanks

        Like

        Reply

  10. Hi Jiangzhen,

    Good day!

    I m trying to access oracle stored procedure using code first -existing db approach. but getting the below error can you pls help me that how to access oracle stored procedure using ef code first approach.

    {“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”}

    and my db context code is

    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);
    }
    }
    }

    Thanks and Regards,
    Dharma

    Like

    Reply

    1. hi Dharma

      i need you oracle definition ,so i can test it.
      one usefull way to debug is look what exactly database run ,
      like db.Database.Log =Console.WriteLine

      Like

      Reply

  11. I am trying to make a stored procedure call generic. Am I right that the name of the function has to be the same as the procedure? Is it possible to make something like this:

    public IQueryable GetTenantData(int tenantId)
    {
    var tenantParameter = new ObjectParameter(“t”, tenantId);
    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction(“Get” + typeof(TEntity).Name, tenantParameter).AsQueryable();
    }

    Like

    Reply

    1. No, the function name can be different than the name of the stored procedure as described in the “Using a different name for the method than the name of the stored procedure” section. However to achieve this you use attributes whose values need to be constant and known at compile time. On top of that the convention uses reflection to build the EDM model and the model has to be built before you can use it and cannot change after you start using it. In general don’t think there is an easy way of doing what you want. I am not even sure if it’s possible.

      Thanks,
      Pawel

      Like

      Reply

  12. Hi Pawel, first of all, I would like to thank you for the excellent article. I’m using your solution in my project but I can’t solve the error bellow:

    juncao.ToList()
    ‘juncao.ToList()’ threw an exception of type ‘System.NotSupportedException’
    Data: {System.Collections.ListDictionaryInternal}
    HResult: -2146233067
    HelpLink: null
    InnerException: null
    Message: “The specified LINQ expression contains references to queries that are associated with different contexts.”
    Source: “EntityFramework”
    StackTrace: ” at System.Data.Entity.Core.Objects.ELinq.Funcletizer.FuncletizingVisitor.InlineObjectQuery(ObjectQuery inlineQuery, Type expressionType)
    at System.Data.Entity.Core.Objects.ELinq.Funcletizer.FuncletizingVisitor.InlineValue(Expression expression, Boolean recompileOnChange)
    at System.Data.Entity.Core.Objects.ELinq.Funcletizer.FuncletizingVisitor.Visit(Expression exp)
    at System.Linq.Expressions.EntityExpressionVisitor.VisitMethodCall(MethodCallExpression m)
    at System.Data.Entity.Core.Objects.ELinq.Funcletizer.FuncletizingVisitor.Visit(Expression exp)
    at System.Linq.Expressions.EntityExpressionVisitor.VisitExpressionList(ReadOnlyCollection`1 original)
    at System.Linq.Expressions.EntityExpressionVisitor.VisitMethodCall(MethodCallExpression m)
    at System.Data.Entity.Core.Objects.ELinq.Funcletizer.FuncletizingVisitor.Visit(Expression exp)
    at System.Linq.Expressions.EntityExpressionVisitor.VisitExpressionList(ReadOnlyCollection`1 original)
    at Syste
    m.Linq.Expressions.EntityExpressionVisitor.VisitMethodCall(MethodCallExpression m)
    at System.Data.Entity.Core.Objects.ELinq.Funcletizer.FuncletizingVisitor.Visit(Expression exp)
    at System.Linq.Expressions.EntityExpressionVisitor.VisitExpressionList(ReadOnlyCollection`1 original)
    at System.Linq.Expressions.EntityExpressionVisitor.VisitMethodCall(MethodCallExpression m)
    at System.Data.Entity.Core.Objects.ELinq.Funcletizer.FuncletizingVisitor.Visit(Expression exp)
    at System.Data.Entity.Core.Objects.ELinq.Funcletizer.Funcletize(Expression expression, Func`1& recompileRequired)
    at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter..ctor(Funcletizer funcletizer, Expression expression)
    at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.CreateExpressionConverter()
    at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
    at System.Data.Entity.Core.Objects.ObjectQuery`1.c__DisplayClass7.b__6()

    at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
    at System.Data.Entity.Core.Objects.ObjectQuery`1.c__DisplayClass7.b__5()
    at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
    at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
    at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable.GetEnumerator>b__0()
    at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
    at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
    at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)”
    TargetSite: {System.Linq.Expressions.Expression InlineObjectQuery(System.Data.Entity.Core.Objects.ObjectQuery, System.Type)}

    This error occures when I try to make a join between an entity returned by the DbSet and my function which was created using CreateQuery.

    public void test()
    {
    //function
    IQueryable saldos = _consultaDeFunctionSaldoConta.ConsultaDeSaldo(true, 1, 101010, 201501, 201512, true, true, false, false, 1, true, false, true, false);

    //normal querys from my system
    IQueryable contas = _consultaDeContaContabil.Queryable;

    //join entre as duas entidades
    var juncao = (from saldo in saldos
    join conta in contas
    on saldo.CodigoContaContabil equals conta.CodigoContaContabil

    where conta.Descricao.StartsWith(“despesa”)
    select new
    {
    codigoContaContabil = saldo.CodigoContaContabil,
    descricao = conta.Descricao,
    debitos = saldo.Debitos,
    creditos = saldo.Creditos,
    saldoInicial = saldo.SaldoInicial
    });
    }

    I have a proof of concept project which was created to check if I could make this joins with others entities and it works fine, but this same approach doesn’t work on my real project.

    bellow my mvc query class

    [DbFunction(“ConsultaDeFunctionSaldoConta”, “FC_CTB_CONSULTASALDO”)]
    public IQueryable ConsultaDeSaldo(bool todosEstabelecimentos, int codigoUsuario, long identificador, int dataInicio, int dataFim, bool todasContas, bool todosCentrosDeResultado, bool abrirPorUnidades, bool saldoAntesDaTransferencia, int codigoPlanoContas, bool usaCentroResultados, bool planoGerencial, bool mostraContasZeradas, bool filtraGraus)
    {
    var context = ServiceLocator.Get();

    var pTodosEstabelecimentos = todosEstabelecimentos != null ? new ObjectParameter(“TODOSESTABELECIMENTOS”, todosEstabelecimentos) : new ObjectParameter(“TODOSESTABELECIMENTOS”, typeof(bool));
    var pCodigoUsuario = codigoUsuario != null ? new ObjectParameter(“CODIGOUSUARIO”, codigoUsuario) : new ObjectParameter(“CODIGOUSUARIO”, typeof(int));
    var pIdentificador = identificador != null ? new ObjectParameter(“IDENTIFICADOR”, identificador) : new ObjectParameter(“IDENTIFICADOR”, typeof(long));
    var pDataInicio = dataInicio != null ? new ObjectParameter(“DT_INICIO”, dataInicio) : new ObjectParameter(“DT_INICIO”, typeof(int));
    var pDataFim = dataFim != null ? new ObjectParameter(“DT_FIM”, dataFim) : new ObjectParameter(“DT_FIM”, typeof(int));
    var pTodasContas = todasContas != null ? new ObjectParameter(“TODASCONTAS”, todasContas) : new ObjectParameter(“TODASCONTAS”, typeof(bool));
    var pTodosCentrosResultado = todosCentrosDeResultado != null ? new ObjectParameter(“TODOSCR”, todosCentrosDeResultado) : new ObjectParameter(“TODOSCR”, typeof(bool));
    var pAbrirPorUnidades = abrirPorUnidades != null ? new ObjectParameter(“ABRIR_ESTAB”, abrirPorUnidades) : new ObjectParameter(“ABRIR_ESTAB”, typeof(bool));
    var pSaldoAntesDaTransferencia = saldoAntesDaTransferencia != null ? new ObjectParameter(“SALDOANTESTRANSFERENCIA”, saldoAntesDaTransferencia) : new ObjectParameter(“SALDOANTESTRANSFERENCIA”, typeof(bool));
    var pCodigoPlanoContas = codigoPlanoContas != null ? new ObjectParameter(“CODIGOPLANOCONTA”, codigoPlanoContas) : new ObjectParameter(“CODIGOPLANOCONTA”, typeof(int));
    var pUsaCentroResultados = usaCentroResultados != null ? new ObjectParameter(“USACR”, usaCentroResultados) : new ObjectParameter(“USACR”, typeof(bool));
    var pPlanoGerencial = planoGerencial != null ? new ObjectParameter(“PLANOGERENCIAL”, planoGerencial) : new ObjectParameter(“PLANOGERENCIAL”, typeof(bool));
    var pMostraContasZeradas = mostraContasZeradas != null ? new ObjectParameter(“MOSTRARCONTASZERADAS”, mostraContasZeradas) : new ObjectParameter(“MOSTRARCONTASZERADAS”, typeof(bool));
    var pFiltraGraus = filtraGraus != null ? new ObjectParameter(“FILTRARGRAUS”, filtraGraus) : new ObjectParameter(“FILTRARGRAUS”, typeof(bool));

    ObjectContext objectContext = context.GetType().GetProperty(“_contextInterno”, System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).GetValue(context) as ObjectContext;
    string sql = string.Format(“[{0}].{1}”, context.GetType().Name, “[FC_CTB_CONSULTASALDO](@TODOSESTABELECIMENTOS,@CODIGOUSUARIO,@IDENTIFICADOR,@DT_INICIO,@DT_FIM,@TODASCONTAS,@TODOSCR,@ABRIR_ESTAB,@SALDOANTESTRANSFERENCIA,@CODIGOPLANOCONTA,@USACR,@PLANOGERENCIAL,@MOSTRARCONTASZERADAS,@FILTRARGRAUS)”);

    var retorno = ((IObjectContextAdapter)context)
    .ObjectContext
    .CreateQuery(sql, pTodosEstabelecimentos, pCodigoUsuario, pIdentificador, pDataInicio, pDataFim,
    pTodasContas, pTodosCentrosResultado, pAbrirPorUnidades, pSaldoAntesDaTransferencia, pCodigoPlanoContas,
    pUsaCentroResultados, pPlanoGerencial, pMostraContasZeradas, pFiltraGraus);

    return retorno;
    }

    Like

    Reply

    1. I think the exception message is quite clear: “The specified LINQ expression contains references to queries that are associated with different contexts”. You get one query from _consultaDeFunctionSaldoConta (IQueryable saldos = _consultaDeFunctionSaldoConta.ConsultaDeSaldo(t…) and the other from _consultaDeContaContabil (IQueryable contas = _consultaDeContaContabil.Queryable). If you want to join the results on your local machine then you need to materialize results first (e.g. by doing .ToList()). Otherwise – as per the exception message – both queries have to be created using the same context.

      Like

      Reply

Leave a reply to The final version of the Store Functions for EntityFramework 6.1.1+ Code First convention released | Code, the Universe and everything... Cancel reply