Until Entity Framework 6.1 was released store functions (i.e. Table Valued Functions and Stored Procedures) could be used in EF only when doing Database First. There were some workarounds which made it possible to invoke store functions in Code First apps but you still could not use TVFs in Linq queries which was one of the biggest limitations. In EF 6.1 the mapping API was made public which (along with some additional tweaks) made it possible to use store functions in your Code First apps. Note, that it does not mean that things will start working automagically once you upgrade to EF6.1. Rather, it means that it is now possible to help EF realize that it actually is capable of handling store functions even when Code First approach is being used. Sounds exciting doesn’t it? So, probably the question you have is:
How do I do that?
To understand how store functions could be enabled for Code First in EF 6.1 let’s take a look first at how they work in the Database First scenario. In Database First you define methods that are driving the execution of store functions in your context class (typically these methods are generated for you when you create a model from the database). You use these methods in your app by calling them directly or, in case of TVFs, in LINQ queries. One thing that is worth mentioning is that these methods need to follow certain conventions otherwise EF won’t be able to use them. Apart from methods defined in your context class store functions must also be specified in the artifacts describing the model – SSDL, CSDL and MSL (think: edmx). At runtime these artifacts are loaded to MetadataWorkspace object which contains all the information about the model.
In Code First the model is being built from the code when the application starts. Types are discovered using reflection and are configured with fluent API in the OnModelCreating method, attributes and/or conventions. The model is then loaded to the MetadataWorkspace (similarly to what happens in the Database First approach) and once this is done both – Code First and Database First operate in the same way. Note that the model becomes read-only after it has been loaded the MetadataWorkspace.
Because Database First and Code First converge at the MetadataWorkspace level enabling discovery of store functions in Code First along with additional model configuration should suffice to add general support for store functions in Code First. Model configuration (and therefore store function discovery) has to happen before the model is loaded to the MetadataWorkspace otherwise the metadata will be sealed and it will be impossible to tweak the model. There are three ways we can configure the model in Code First – configuration attributes, fluent API and conventions. Attributes are not rich enough to configure store functions. Fluent API does not have access to mapping. This leaves conventions. Indeed a custom model convention seems ideal – it gives you access to the model which in EF 6.1 not only contains conceptual and store models but also modifiable mapping information. So, we could create a convention which discovers methods using reflection, then configures store and conceptual models accordingly and defines the mapping. Methods mapped to store functions will have to meet some specific requirements imposed by Entity Framework. The requirements for methods mapped to table valued functions are the following:
- return type must be an
IQueryable<T>whereTis a type for which a corresponding EDM type exists – i.e. is either a primitive type that is supported by EF (for instanceintis fine whileuintwon’t work) or a non-primitive type (enum/complex type/entity type) that has been configured (either implicitly or explicitly) in your model - method parameters must be of scalar (i.e. primitive or enum) types mappable to EF types
- methods must have the
DbFunctionAttributewhose the first argument is the conceptual container name and the second argument is the function name. The container name is typically the name of theDbContextderived class however if you are unsure you can use the following code snippet to get the name:Console.WriteLine( ((IObjectContextAdapter) ctx).ObjectContext.MetadataWorkspace .GetItemCollection(DataSpace.CSpace) .GetItems<EntityContainer>() .Single() .Name); - the name of the method, the value of the
DbFunction.FunctionNameand thequeryStringname passed to theCreateQuerycall must all be the same - in some cases TVF mapping may require additional details – a column name and/or the name of the database schema. You can specify them using the
DbFunctionDetailsAttribute. The column name is required if the method is mapped to a TVF that returns a collection of primitive values. This is needed because EF requires providing the name of the column containing the values and there is no way of inferring this information from the code and therefore it has to be provided externally by setting theResultColumnNameproperty of theDbFunctionDetailsattribute to the name of the column returned by the function. The database schema name needs to be specified if the schema of the TVF being mapped is different from the default schema name passed to the convention constructor and can be done by setting theDatabaseSchemaproperty of theDbFunctionDetailsAttribute.
The requirements for methods mapped to stored procedures are less demanding and are the following:
- the return type has to be
ObjectResult<T>whereT, similarly to TVFs, is a type that can be mapped to an EDM type - you can also specify the name of the database schema if it is different from the default name by setting the
DatabaseSchemaproperty of theDbFunctionDetailsAttribute. (Because of how the result mapping works for stored procedures setting theResultColumnNameproperty has no effect)
The above requirements were mostly about method signatures but the bodies of the methods are important too. For TVFs you create a query using the ObjectContext.CreateQuery method while stored procedures just use ObjectContext.ExecuteFunction method. Below you can find examples for both TVFs and stored procedures (also notice how parameters passed to store functions are created). In addition the methods need to be members of the DbContext derived type which itself is the generic argument of the convention.
Currently only the simplest result mapping where names of the columns returned from the database match the names of the names of the properties of the target type (except for mapping to scalar results) is supported. This is actually a limitation in the EF Code First where more complicated mappings would currently be ignored in most cases even though they are valid from the MSL perspective. There is a chance of having more complicated mappings enabled in EF 6.1.1 if appropriate changes are checked in by the time EF 6.1.1 ships. From here there should be just one step to enabling stored procedures returning multiple resultsets in Code First.
Now you probably are a bit tired of all this EF mumbo-jumbo and would like to see
The Code
To see the custom convention in action create a new (Console Application) project. Once the project has been created add the EntityFramework.CodeFirstStoreFunctions NuGet package. You can add it either from the Package Manager Console by executing
Install-Package EntityFramework.CodeFirstStoreFunctions -Pre
command or using the UI – right click the References in the solution explorer and select “Manage NuGet Packages”, then when the dialog opens make sure that the “Include Prerelease” option in the dropdown at the top of the dialog is selected and use “storefunctions” in the search box to find the package. Finally click the “Install” button to install the package.
Installing EntityFramework.CodeFirstStoreFunctions from UI
After the package has been installed copy and paste the code snippet from below to your project. This code demonstrates how to enable store functions in Code First.
public class Customer
{
public int Id { get; set; }
public string Name { get; set; }
public string ZipCode { get; set; }
}
public class MyContext : DbContext
{
static MyContext()
{
Database.SetInitializer(new MyContextInitializer());
}
public DbSet<Customer> Customers { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Add(new FunctionsConvention<MyContext>("dbo"));
}
[DbFunction("MyContext", "CustomersByZipCode")]
public IQueryable<Customer> CustomersByZipCode(string zipCode)
{
var zipCodeParameter = zipCode != null ?
new ObjectParameter("ZipCode", zipCode) :
new ObjectParameter("ZipCode", typeof(string));
return ((IObjectContextAdapter)this).ObjectContext
.CreateQuery<Customer>(
string.Format("[{0}].{1}", GetType().Name,
"[CustomersByZipCode](@ZipCode)"), zipCodeParameter);
}
public ObjectResult<Customer> GetCustomersByName(string name)
{
var nameParameter = name != null ?
new ObjectParameter("Name", name) :
new ObjectParameter("Name", typeof(string));
return ((IObjectContextAdapter)this).ObjectContext.
ExecuteFunction<Customer>("GetCustomersByName", nameParameter);
}
}
public class MyContextInitializer : DropCreateDatabaseAlways<MyContext>
{
public override void InitializeDatabase(MyContext context)
{
base.InitializeDatabase(context);
context.Database.ExecuteSqlCommand(
"CREATE PROCEDURE [dbo].[GetCustomersByName] @Name nvarchar(max) AS " +
"SELECT [Id], [Name], [ZipCode] " +
"FROM [dbo].[Customers] " +
"WHERE [Name] LIKE (@Name)");
context.Database.ExecuteSqlCommand(
"CREATE FUNCTION [dbo].[CustomersByZipCode](@ZipCode nchar(5)) " +
"RETURNS TABLE " +
"RETURN " +
"SELECT [Id], [Name], [ZipCode] " +
"FROM [dbo].[Customers] " +
"WHERE [ZipCode] = @ZipCode");
}
protected override void Seed(MyContext context)
{
context.Customers.Add(new Customer {Name = "John", ZipCode = "98052"});
context.Customers.Add(new Customer { Name = "Natasha", ZipCode = "98210" });
context.Customers.Add(new Customer { Name = "Lin", ZipCode = "98052" });
context.Customers.Add(new Customer { Name = "Josh", ZipCode = "90210" });
context.Customers.Add(new Customer { Name = "Maria", ZipCode = "98074" });
context.SaveChanges();
}
}
class Program
{
static void Main()
{
using (var ctx = new MyContext())
{
const string zipCode = "98052";
var q = ctx.CustomersByZipCode(zipCode)
.Where(c => c.Name.Length > 3);
//Console.WriteLine(((ObjectQuery)q).ToTraceString());
Console.WriteLine("TVF: CustomersByZipCode('{0}')", zipCode);
foreach (var customer in q)
{
Console.WriteLine("Id: {0}, Name: {1}, ZipCode: {2}",
customer.Id, customer.Name, customer.ZipCode);
}
const string name = "Jo%";
Console.WriteLine("\nStored procedure: GetCustomersByName '{0}'", name);
foreach (var customer in ctx.GetCustomersByName(name))
{
Console.WriteLine("Id: {0}, Name: {1}, ZipCode: {2}",
customer.Id, customer.Name, customer.ZipCode);
}
}
}
}
In the code above I use a custom initializer to initialize the database and create a table-valued function and a stored procedure (in a real application you would probably use Code First Migrations for this). The initializer also populates the database with some data in the Seed method. The MyContext class is a class derived from the DbContext class and contains two methods that are mapped to store functions created in the initializer. The context class contains also the OnModelCreating method where we register the convention which will do all the hard work related to setting up our store functions. The Main method contains code that invokes store functions created when initializing the database. First, we use the TVF. Note, that we compose the query on the function which means that the whole query will be translated to SQL and executed on the database side. If you would like to see this you can uncomment the line which prints the SQL query in the above snippet and you will see the exact query that will be sent to the database:
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent1].[ZipCode] AS [ZipCode]
FROM [dbo].[CustomersByZipCode](@ZipCode) AS [Extent1]
WHERE ( CAST(LEN([Extent1].[Name]) AS int)) > 3
(Back to the code) Next we execute the query and display results. Once we are done with the TVF we invoke the stored procedure. This is just an invocation because you cannot build queries on top of results returned by stored procedures. If you need any query-like (or other) logic it must be inside the stored procedure itself and otherwise you end up having a Linq query that is being run against materialized results. That’s pretty much the whole app. Just in case I am pasting the output the app produces below:
TVF: CustomersByZipCode('98052')
Id: 1, Name: John, ZipCode: 98052
Stored procedure: GetCustomersByName 'Jo%'
Id: 1, Name: John, ZipCode: 98052
Id: 4, Name: Josh, ZipCode: 90210
Press any key to continue . . .
Note that in both examples the return types are based on entity types. As I hinted above you can also use complex and scalar types for your results. Take a look at the End-to-End tests in the project itself – all scenarios are tested there.
That’s about what’s in alpha, so you may ask:
what’s next?
If you look at the code there are a few TODOs in the code. One of the most important is the support for nullable parameters. I am also thinking of removing the limitation where the method name in your DbContext derived class must ultimately match the name of the TVF in the database. If the workitem 2192 is resolved for the next version of EF I will be able to add support for non-default mapping. In addition I think it is very close from workitem 2192 to supporting stored procedures returning multiple resultsets. Not sure how useful it would be but it would be cool to see support for this feature which currently is kind of a dead feature because it is supported neither by CodeFirst nor by EF tooling.
Anything else?
The project is open source and is hosted on codeplex. You can get the sources from here. Try it and let me know what you think.
Code, the Universe and Everything... 
Hello, Thank you so much for the article. This really helps. One issue I am running into is: I am developing a class library using .Net Framework 4.0 and when I install your package using Nuget, I am not able to build anymore as the error says that “CodeFirstStoreFunctions, Version=0.1.0.0, Culture=neutral, PublicKeyToken=46c4868af4307d2c, processorArchitecture=MSIL” could not be resolved because it was built against the “.NETFramework,Version=v4.5” framework. This is a higher version than the currently targeted framework “.NETFramework,Version=v4.0”. I cannot install 4.5 framework yet, because there are several applications that need 4.0 framework now. Anyway to get around this issue?
LikeLike
I am thinking about updating the package to contain versions targeting both platforms (or maybe even one just for .NET Framework 4 since it will work on .NET Framework 4.5 because I don’t think there is any code in there that would require .NET Framework 4.5). I am planning to do this for Beta release (in a couple weeks or so). For now you can just get the source from https://codefirstfunctions.codeplex.com/ retarget the project to .NET Framework 4 and build your private version.
LikeLike
When trying to use this in the context of my entities I get…
A first chance exception of type ‘System.InvalidOperationException’ occurred in EntityFramework.dll
05/05/14 11:08:20 [Information] [WARNING: Unprocessed category type(s) ‘Error’] System.InvalidOperationException: No EdmType found for type ‘.’.
at CodeFirstStoreFunctions.FunctionDiscovery.GetReturnEdmItemType(Type type)
at CodeFirstStoreFunctions.FunctionDiscovery.CreateFunctionImport(MethodInfo method)
at CodeFirstStoreFunctions.FunctionDiscovery.d__0.MoveNext()
at CodeFirstStoreFunctions.FunctionsConvention`1.Apply(EntityContainer item, DbModel model)
at System.Data.Entity.ModelConfiguration.Configuration.ConventionsConfiguration.ModelConventionDispatcher.Dispatch[T](T item)
at System.Data.Entity.ModelConfiguration.Configuration.ConventionsConfiguration.ModelConventionDispatcher.VisitEdmEntityContainer(EntityContainer item)
at System.Data.Entity.Edm.EdmModelVisitor.VisitCollection[T](IEnumerable`1 collection, Action`1 visitMethod)
at System.Data.Entity.Edm.EdmModelVisitor.VisitEntityContainers(IEnumerable`1 entityContainers)
at System.Data.Entity.Edm.EdmModelVisitor.VisitEdmModel(EdmModel item)
at System.Data.Entity.ModelConfiguration.Configuration.ConventionsConfiguration.ModelConventionDispatcher.VisitEdmModel(EdmModel item)
at System.Data.Entity.ModelConfiguration.Configuration.ConventionsConfiguration.ModelConventionDispatcher.Dispatch()
at System.Data.Entity.ModelConfiguration.Configuration.ConventionsConfiguration.ApplyStoreModel(DbModel model)
at System.Data.Entity.DbModelBuilder.Build(DbProviderManifest providerManifest, DbProviderInfo providerInfo)
at System.Data.Entity.DbModelBuilder.Build(DbConnection providerConnection)
at System.Data.Entity.Internal.LazyInternalContext.CreateModel(LazyInternalContext internalContext)
at System.Data.Entity.Internal.RetryLazy`2.GetValue(TInput input)
at System.Data.Entity.Internal.LazyInternalContext.InitializeContext()
at System.Data.Entity.Internal.InternalContext.Initialize()
at System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType)
at System.Data.Entity.Internal.Linq.InternalSet`1.Initialize()
at System.Data.Entity.Internal.Linq.InternalSet`1.get_InternalContext()
at System.Data.Entity.Infrastructure.DbQuery`1.System.Linq.IQueryable.get_Provider()
at System.Linq.Queryable.Cast[TResult](IQueryable source)
As this stuff is reasonably new I have been unable to search for a solution and wondered if you could give me a couple of pointers?
LikeLike
Looks like the type your method returns is not a type that exists in the EF model (i.e. it is not a supported primitive type nor an entity or complex type that was imported to the model). You can add a complex/entity type to the model in the OnModelCreating method using the
DbModelBuilder.EntityType()orDbModelBuilder.ComplexType()method (take a looke how it is done in the E2ETests.cs). One thing that stands out to me is that in the error message the type name is missing – there is only ‘.’ instead of the full type name but looking at the code I don’t see how this can happen. What type do you return?LikeLike
I realised subsequently I was missing a public DbSet statement on the dbcontext. However, this is because my function allows for a number of computations and the returned table is not a simple filtering of an existing table. Therefore I am not querying and mapping to but rather querying any number of entities and returning a computed dataset and it is this I am trying to map onto a POCO.
I am thinking that .ExecuteStoreQuery(“select * from function(…)”,paramters[]) may be the way to go with this?
LikeLike
It feels to me that the result of the function in your case should be actually a complex type and not an entity. I think that in that case using ExecuteStoreQuery should be just fine (the advantage of using the convention over ExecuteStoreQuery is that you don’t need to write SQL on your own but can use Linq to create queries). Note that ExecuteStoreQuery does not do tracking so if you wanted to use it with entities you would have to use the Translate method (or attach entities) if you needed tracking.
LikeLike
for stored procedures on EF6.1
public ObjectResult GetCustomersByName(string name)
….
return ((IObjectContextAdapter)this).ObjectContext.
ExecuteFunction(“GetCustomersByName”, nameParameter);
}
I added ExecuteFunction and now the project builds.
Whereas non-Generic System.Data.Entity.Core.Objects.ExecuteFunction’s definition for returning transaction wrapped scalar results returns int:
public virtual int ExecuteFunction(string functionName, params ObjectParameter[] parameters);
LikeLike
Thanks for pointing this out. When editing the post I copied the code from VS and angle brackets were gone. I tried to fix all the places where they were needed but must have missed this one. I will fixe the post.
Thanks,
Pawel
LikeLike
Is there any way to get this working in .Anys or inside of a Where etc.?
Right now it fails saying that it’s not supported because the method cannot be translated into a linq to entities store expression.
This is by far the most useful reason for TVFs to be used (inline TVFs that filter things based on parameters to simplify code)
I would think with conventions that this would be possible.
This would, for example, enable creating TVFs that do full text searches and inline the results and join them and sort by the relevance which is not possible right now with Code First (and was why I’ve been banging the drum for native TVFs in EF code first for so long!)
LikeLike
Show me your query. If it was possible with edmx it should be possible now with Code First and the convention. If it was not possible with the edmx approach before it won’t be possible with this convention either as the convention is just creating the same metadata you would have created manually (or with the designer) when using edmx.
Thanks,
Pawel
LikeLike
from s from db.Something where SomeTVF(SomeParam).All() select s
This will fail in Code First.
The same thing works in EDMX.
LikeLike
If you map your TVF as described in the blog post I think the following should work (note the
db.in front of the TVF – this is how you tell EF this is something it should know and be able translate to SQL):from s from db.Something where db.SomeTVF(SomeParam).All() select s
Thanks,
Pawel
LikeLike
Here’s my mapping:
[DbFunction(“DataContext”, “AclDataMartsFiltered”)]
public IQueryable FilteredDataMartAcls(Guid userID, Guid permissionID, Guid dataMartID)
{
return
((IObjectContextAdapter) this).ObjectContext.CreateQuery(
“dbo.AclDataMartsFiltered(@UserID, @PermissionID, @DataMartID)”, new ObjectParameter(“UserID”, userID), new ObjectParameter(“PermissionID”, permissionID), new ObjectParameter(“DataMartID”, dataMartID));
}
And in the modelCreating:
modelBuilder.Conventions.Add(new FunctionsConvention(“dbo”));
What am I missing?
LikeLike
I am afraid it’s not enough to answer your question. I don’t know where the code you posted lives, I don’t know how you use the method in the query, I don’t know the query looks like, I don’t even know what the error is (if any). Post a full simplified but complete repro I could just copy/paste to a VS console app and tell me what you see and what you expect to see. Honestly, I’d rather spend time on actually finishing this project and driving it to the final version rather than trying to figure out how to repro the problem you are seeing which may or may not be a real issue. Sorry.
(Stolen from EF bug reports)
For a guide on submitting good bug reports, read Painless Bug Tracking. The most important elements to help us act on a bug report are example code/steps that reproduce the issue, the outcome/results you are expecting, and the results or error message that you are receiving.
Thanks,
Pawel
LikeLike
I’m at a loss. I implemented the dbfunction in the dbcontext exactly as you have above. I implemented your convention in the modelcreate method on the dbcontext which is definitely being called. (as I noted above)
The query doesn’t matter where it’s being run in code. It’s calling the db context. As I said, it works fine if you write “from stvf in TVFCall(something, somethingelse) select stvf” but fails with “not implemented exception” if the TVF is put in the where clause directly and not in the join or “in”
This is the same thing that happens if you call a standard .net function inside a where clause because EF can’t decompile or reflect into the .net function.
You can easily reproduce this if you simply put a TVF in a where clause with your code. It will fail with a notsupportederror when you’re query is materialized. The same thing won’t happen if you use an EDMX.
LikeLike
Show me the code. Show me what works with EDMX and what is not working with the convention. I need to be able to copy your code and run it to be able to investigate.
LikeLike
As I said:
1. Create a TVF.
2. Implement it your way.
3. Implement it in EDMX
Write the following 2 queries against whatever your model is. (this is symantic)
1. from s in db.TVF(SomeParams…) select s
2. from b in db.DbSet() where db.TVF(b.SomeProperty).Any() select b
#1 query works in both Code First and EDMX.
#2 Works in EDMX and fails in Code First with “not supported” because it can’t reflect out the function.
#2 needs to work.
LikeLike
As I said. Show me the code. The above is not code.
LikeLike
Or in other words: “I want to be lazy and not follow the instructions and have you do all of the work for my bug in my code.”
I can’t show you the code because it’s proprietary 100% secret code. You cannot have it and I’ve given you exactly what you need to do to reproduce the bug in your code.
If you choose not to fix it, then your implementation is largely useless because Stored procedures are already supported, and TVFs used as you’re example are a pointless waste of time because you can already do the same with stored procedures so there is no point in using your implementation for TVFs.
So your choice. I don’t care, I have an (ugly) work around and I don’t have time to waste writing you sample code that you could easily do (and create yourself a unit test in the process) based on the steps to reproduce that I’ve provided.
Good luck.
LikeLike
I work on this (and a few other projects) in my free time. On top of that I have a family, a lawn to mow and – surprise – a full time job. You have a problem you want me to solve then make it easy for me. Providing a repro which I can use to get to the problem instead of spending my time on instructions which may or may not reproduce the problem is one of the best ways to support the project. Note, I don’t want your proprietary code. I want code that reproes the issue and I want it to be as simple as possible (proprietary code typically doesn’t fall to this category).
On a different note – this project is not only free but is also open source. If you have an issue you can just clone the repo and fix the bug yourself. Then you can contribute the fix back so that other people could benefit from it.
Thanks for your support.
LikeLiked by 2 people
Hi moozzyk,
Great work!
I was just thinking. Is there any particular reason to restrict the type parameter of FunctionsConvention to DbContext. It seems like any type will do. It would be more flexible to be able to place the TVF or StoredProcedures in any class as Extension methods of the DbContext.
Thanks
LikeLike
I don’t remember from the top of my head but I think EF might require methods mapped to function imports on the context class. It’s also a convention used when doing DatabaseFirst (EDMX). On the other hand I don’t understand the reason to implement TVFs/sprocs methods as extension methods. To me it feels conceptually wrong to want to do this (unless you don’t control your context type). If you just don’t want to pollute code in your DbContext derived class then make the class partial and move TVFs/sprocs methods to a separate file.
Thanks,
Pawel
LikeLike
It may be convention for DatabaseFirst but its also a restriction and EF certainly doesn’t care where the method is. In fact those methods are configuration only.
The reason we use CodeFirst in the first place is to be able to construct our model runtime with the help of DI. The models are in multiple assemblies and depending on what assemblies are loaded those models are available in the context. And it is the DbContext class, we are not inheriting from it.
I made a few changes to your project you can have a look at my fork – https://codefirstfunctions.codeplex.com/SourceControl/network/forks/angelyordanov/codefirstfunctions.
1) Made it possible to specify the SQL function name explicitly.
2) Made it possible to specify the model(EDM) function name explicitly.
3) Added support for extension methods like:
public static IQueryable CustomersByZipCode(this DbContext context, string zipCode)
I’m also considering the use of the DbFunctionAttribute from System.Data.Entity a mistake. As it requires that you specify the namespace even though it is irrelevant for the FunctionsConvention. The function is always placed in the ‘ConceptualModel.Container.Name’ namespace. Which by the way is different from the type name of the context in some cases. So ‘objectContext.DefaultContainerName’ is the correct way to find it, not ‘context.GetType().Name’.
Have a look and let me know what you think. I can make a pull request to discuss what you would like changed.
Thanks,
Angel
LikeLike
If the methods work even if they are not part of the
DbContextclass then type constraint can be definitely removed (or changed toclass). I believe EF Linq translator uses theDbFunctionattribute to recognize TVFs in queries. I will definitely take a look at your code but I will be travelling so I probably won’t be able to get to it before late next week. I will start a thread on codeplex where we will be able to discuss the change. Thanks a lot for your contribution!Pawel
LikeLike
Hi Angel,
I finally was able to take a look at your changes and they seem reasonable to me. One thing that I am missing are tests. I also found a few minor things but we can discuss them when you create a PR. In general, I will be glad to accept your changes.
Thanks a lot!
Pawel
LikeLike
Hi Pavel,
Yes, tests is something that’s missing. I will add a few, testing all the new features.
I’ll open up a PR.
Thanks
LikeLike
Hi,
I could not make it work, I receive an error: “Method not found: ‘System.Data.Entity.Core.Metadata.Edm.EdmModel System.Data.Entity.Infrastructure.DbModel.get_ConceptualModel()’.”
I even compared the DbContext methods that map to the TVF to the one generated by a EF designer and the only change would be the attribute:
[DbFunction(“MyContext”, “GetMeetingsByCentre”)]
public IQueryable GetMeetingsByCentre(string centre)
{
var centreParameter = centre != null ?
new ObjectParameter(“Centre”, centre) :
new ObjectParameter(“Centre”, typeof(string));
return ((IObjectContextAdapter)this).ObjectContext.CreateQuery(
string.Format(“[{0}].{1}”, GetType().Name,”[MyContext].[GetMeetingsByCentre](@Centre)”), centreParameter);
}
vs
[EdmFunction(“MyContext”, “GetMeetingsByCentre”)]
public virtual IQueryable GetMeetingsByCentre(string centre)
{
var centreParameter = centre != null ?
new ObjectParameter(“Centre”, centre) :
new ObjectParameter(“Centre”, typeof(string));
return ((IObjectContextAdapter)this).ObjectContext.CreateQuery(“[MyContext].[GetMeetingsByCentre](@Centre)”, centreParameter);
}
Do you have any idea of what I could be doing wrong? Can you provide the sources for the example you made and which works?
LikeLike
The
DbModel.ConceptualModelproperty was added in EF 6.1. MethodMissingException indicates that you are using an earlier version of EF (i.e. EF6.0.0, EF6.0.1 or EF6.0.2). Upgrade to the latest version of EF and things should work.Thanks,
Pawel
LikeLike
Look at the string you are passing as a query – string.Format(“[{0}].{1}”, GetType().Name,”[MyContext].[GetMeetingsByCentre](@Centre)”).
It should be string.Format(“[{0}].{1}”, GetType().Name,”[GetMeetingsByCentre](@Centre)”)
or even better just “[MyContext].[GetMeetingsByCentre](@Centre)”
LikeLike
Good catch!
LikeLike
Yes, good catch, thx!
Now I have:
string.Format(“[{0}].{1}”, GetType().Name,”[GetMeetingsByCentre](@Centre)”)
, but I still get the same exception.
The EF vs is 6.1:
LikeLike
Can you provide with a full sample, where I only have to create the db and tables?
LikeLike
Here is a working sample – https://github.com/angelyordanov/codefirstfunctions-sample
LikeLike
Thanks for the sample project. I will take a look at it. One more question – are you sure that you don’t have EF in the GAC? If you do then the project will be build against the version you referenced but would bind against the version that is in the GAC which would explain the MissingMethodException.
LikeLike
It does bind to the referenced nuget version.
And I don’t get the exception. @Tudor does.
LikeLike
This why I asked if he does not have EntityFramework.dll in the GAC. GAC always takes precedence and the assembly version is the same (6.0.0.0) for all EF6.x versions.
LikeLike
I found out another thing: if I only install the EF 6.1 nugget, the following code works:
var listings = this.Database.SqlQuery(sql, term).ToList();
If I also install the EntityFramework.CodeFirstStoreFunctions nugget, the code doesn’t work anymore, exception “Method not found…” being thrown.
LikeLike
Hi. Can I with CodeFirstStoreFunctions do that:
1. FUNCTION [dbo].[DateTimeToString] IN @value as DateTime return CONVERT(nvarchar(10), @value, 104)
2. Map DateTimeToString to EF
3. Use it in query
db.Orders.Where(x => x.OrderDateTime.DateTimeToString().Contains(07))
OR
db.Orders.Where(x => DateTimeToString(x.OrderDateTime).Contains(07))
?
LikeLike
As of EF6.1
.ToString()is supported so you may be able to do what you want to do without using functions. To answer your question – currently scalar functions are not supported by the convention. If EF supports scalar functions I will add the support for them to the convention. (Created a work item for this https://codefirstfunctions.codeplex.com/workitem/4)Thanks,
Pawel
LikeLike
DateTime.ToString() return CAST(Filed AS nvarchar(10)
and return 13 Jul, 2014
I need custom format string 13 Jul, 2014 = 13.07.2014
LikeLike
I see – I was actually afraid it 🙂 As I said, I need to check how scalar functions work in EF and then I should be able to add support for them to the convention.
Thanks,
Pawel
LikeLike
Hi,
great work. Thank you for this. That is really cool and I’m sure it was not easy to do this in view of the fact, that there isn’t much info about this around.
Allow me to ask two questions, regarding the futher development of this:
You mentioned the workitem-2192-issue above. What’s the current status on this?
Is there some kind of timeline, that tells us, which features is planned for when?
Wishes,
Manfred
Btw: I’ve noticed, that the implementation available via nuget can not deal with nullables (eg. int? or Nullable).
LikeLike
2192 was fixed on the EF side and was included in EF 6.1.1. Implementing custom column mapping is at the moment low pri for me. I would like to fix bugs and TODOs in the code first so that I can release the first version. After this, depending on the feedback, I will consider adding custom mapping. I think getting the API right is tricky in this case – especially when taking sprocs returning multiple result sets into account. I created a codeplex work item this.
Yes, in the alpha version parameters of nullable scalar types don’t work. There is a TODO in the code for this and also a bug tracking this issue. Fixing this is very high on my list and I plan to do this for beta.
Thanks,
Pawel
LikeLike
I have implemented Custom Mappings in my fork – https://codefirstfunctions.codeplex.com/SourceControl/network/forks/angelyordanov/codefirstfunctions/changeset/10289d8079297299a4c35885f12012ce0739f28b.
The problem is to get it to work for NonComposable functions a change that moozzyk made in EF Code some time ago needs to be applied for the “else” part of an “if” as well – for NonCoposable functions with result mapings.
https://entityframework.codeplex.com/SourceControl/changeset/b4cb2b17ac24a611719b18cb3c890b59d076a290#file_diff_src/EntityFramework/Core/Mapping/FunctionImportMappingNonComposable.cs
I couldn’t run EF tests and decided not to make the PR yet.
I would like to see the changes I made merged into moozzyk’s code but I’m very busy at the moment and can’t say when I’ll have time to clean up things and fix the tests.
LikeLike
I believe writing explicit mappings for both composable (i.e. TVFs) and non-composable (i.e. sprocs, including sprocs returning multiple resultsets) has been done – see commits f45d464 and 269f4fd in the EF repo. Now I would have to come up with an API in the convention to allow to specify the mapping.
Thanks,
Pawel
LikeLike
Yes, those commits were a prerequisite for custom mappings, thanks for authoring them.
The code missing is for NonComposable functions with ResultMappings, that creates a new EdmItemCollection when StorageMappingItemCollection is null.
Look at the changes you made in file Core/Mapping/FunctionImportMappingNonComposable.cs in commit b4cb2b1. The consturctor of FunctionImportMappingNonComposable crashes if resultMappings.Any() is true.
I’m currently using the code from my fork in production and it works great with custom mappings, but we do not use non composable functions.
LikeLike
Interesting. I did not know about this. When I processed the PR I already was on the SignalR team and I did not have time to verify that the changes are everything needed to enable custom mapping in the convention. I will add your comment to the work item I created for this and will take a look at your changes.
LikeLike
You write, “There is a chance of having more complicated mappings enabled in EF 6.1.1 if appropriate changes are checked in by the time EF 6.1.1 ships. From here there should be just one step to enabling stored procedures returning multiple resultsets in Code First.” Clicking on the link and looking at the comments suggests to me that this is supported now. Three quick questions: (1) Is that correct? (2) Can you give a simple example of this (say we want to load all Blogs and Posts)? (3) When we do this, will the object graph be set up correctly to access related items via their navigation properties — for example, if we do myContext.Blogs.First().Posts, will that work?
Thanks so much for your work on this!
LikeLike
“More complicated mappings” for store functions in EF merely means that the name of the column returned by a TVF or sproc does not have to match the name of the property on the entity the column is mapped to. The issue here is that currently EF internally does not support materializing graphs of entities coming from store functions. Adding support for this requires changes to EF codebase and cannot be “patched” just with a convention. Sorry.
As for the commits they were supposed to enable mapping columns to properties. However as per Angel Yordanov there are still some missing pieces I was not aware of. Take a look at this thread: https://blog.3d-logic.com/2014/04/09/support-for-store-functions-tvfs-and-stored-procs-in-entity-framework-6-1/#comment-3587
Thanks,
Pawel
LikeLike
My temporary solution
http://stackoverflow.com/questions/25180382/entity-framework-6-tostring-formating-datetime-format-query-intercept
LikeLike
It’s interesting but feels a bit hacky (or I would even say – it’s interesting because it is hacky). Accidentaly this is what I have been working on in the past two days. Try this out and let me know what you think because I am pushing hard to ship this on NuGet soon (btw. here is a minimal convention that does what you are looking for (or it’s what my convention includes now)).
Thanks,
Pawel
LikeLike
The type or namespace name ‘FunctionsConvention’ could not be found (are you missing a using directive or an assembly reference?)
LikeLike
Do you have the reference to the CodeFirstFunctions.dll in your project? The way to install the convention is to use NuGet as shown in the post. If you followed the steps – are you targeting .NET Framework 4 by any chance? The alpha NuGet package has only assemblies compiled against .NET Framework 4.5 and they will be ignored if you try using it on .NET Framework 4 (if you compile with diagnostic verbosity you would find a message saying that the referenced assembly was compiled for newer version of .NET Framework and therefore will be ignored – this is buried pretty deeply and hard to find). I fixed this in beta which I hope to publish soon.
Thanks,
Pawel
LikeLike
I am receiving the follow error when trying to use a stored procedure: ‘The FunctionImport ‘GetPartnerInputPickup’ could not be found in the container ‘K1TrackerContext’.’, I have add the return type as ComplexType. Here is my stack trace:
System.InvalidOperationException was unhandled by user code
HResult=-2146233079
Message=The FunctionImport ‘GetPartnerInputPickup’ could not be found in the container ‘K1TrackerContext’.
Source=EntityFramework
StackTrace:
at System.Data.Entity.Core.Common.Utils.CommandHelper.FindFunctionImport(MetadataWorkspace workspace, String containerName, String functionImportName)
at System.Data.Entity.Core.Common.Utils.MetadataHelper.GetFunctionImport(String functionName, String defaultContainerName, MetadataWorkspace workspace, String& containerName, String& functionImportName)
at System.Data.Entity.Core.Objects.ObjectContext.CreateEntityCommandForFunctionImport(String functionName, EdmFunction& functionImport, ObjectParameter[] parameters)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, ExecutionOptions executionOptions, ObjectParameter[] parameters)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, MergeOption mergeOption, ObjectParameter[] parameters)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, ObjectParameter[] parameters)
at EI.K1Tracker.Data.K1TrackerContext.ExecuteQuery(String functionName, Int32 memberId) in c:\TFS\Projects\K1TrackerDev\EI.K1Tracker.Data\K1TrackerContext.cs:line 102
at EI.K1Tracker.Data.Infrastructure.Repository`1.ExecuteQuery(String functionName, Int32 parameters) in c:\TFS\Projects\K1TrackerDev\EI.K1Tracker.Data.Infrastructure\Repository.cs:line 112
at EI.K1Tracker.API.Tests.WebAPITests.Can_Retrieve_EntityTypes() in c:\TFS\Projects\K1TrackerDev\EI.K1Tracker.API.Tests\UnitTest1.cs:line 34
InnerException:
LikeLike
I need to see how you set up your context and how the method is defined and invoked. Ideally create a new bug on codeplex (you can use this link: https://codefirstfunctions.codeplex.com/WorkItem/Create) and attach a minimal repro I could take a look at.
Thanks,
Pawel
LikeLike
This article and NuGet package helped me. Thanks.
LikeLike
Great to hear!
LikeLike
First, moozzyk, thank you for all your hard work on this project.
I’ve followed your code above and looked at your E2ETests.cs code to ensure I’m following your conventions correctly. I keep getting a runtime error that appears to be happening when it is trying to configure the store functions. My functions take Guids as a parameter and return Guids as part of the return set. Would that cause the problem?
If I could narrow down the ‘name’ referred to below, I might be able to move on.
Here’s the error details:
HResult=-2147024809
Message=The argument ‘name’ cannot be null, empty or contain only white space.
Source=EntityFramework
StackTrace:
at System.Data.Entity.Utilities.Check.NotEmpty(String value, String parameterName)
at System.Data.Entity.Core.Metadata.Edm.EdmProperty.Create(String name, TypeUsage typeUsage)
at CodeFirstStoreFunctions.StoreFunctionBuilder.CreateReturnRowType(String propertyName, EdmType edmType)
at CodeFirstStoreFunctions.StoreFunctionBuilder.CreateFunctionReturnParameters(FunctionDescriptor functionDescriptor)
at CodeFirstStoreFunctions.StoreFunctionBuilder.Create(FunctionDescriptor functionDescriptor)
at CodeFirstStoreFunctions.FunctionsConvention.Apply(EntityContainer item, DbModel model)
at System.Data.Entity.ModelConfiguration.Configuration.ConventionsConfiguration.ModelConventionDispatcher.Dispatch[T](T item)
at System.Data.Entity.ModelConfiguration.Configuration.ConventionsConfiguration.ModelConventionDispatcher.VisitEdmEntityContainer(EntityContainer item)
at System.Data.Entity.Edm.EdmModelVisitor.VisitCollection[T](IEnumerable`1 collection, Action`1 visitMethod)
at System.Data.Entity.Edm.EdmModelVisitor.VisitEntityContainers(IEnumerable`1 entityContainers)
at System.Data.Entity.Edm.EdmModelVisitor.VisitEdmModel(EdmModel item)
at System.Data.Entity.ModelConfiguration.Configuration.ConventionsConfiguration.ModelConventionDispatcher.VisitEdmModel(EdmModel item)
at System.Data.Entity.ModelConfiguration.Configuration.ConventionsConfiguration.ModelConventionDispatcher.Dispatch()
at System.Data.Entity.ModelConfiguration.Configuration.ConventionsConfiguration.ApplyStoreModel(DbModel model)
at System.Data.Entity.DbModelBuilder.Build(DbProviderManifest providerManifest, DbProviderInfo providerInfo)
at System.Data.Entity.DbModelBuilder.Build(DbConnection providerConnection)
at System.Data.Entity.Internal.LazyInternalContext.CreateModel(LazyInternalContext internalContext)
at System.Data.Entity.Internal.RetryLazy`2.GetValue(TInput input)
at System.Data.Entity.Internal.LazyInternalContext.InitializeContext()
at System.Data.Entity.Internal.InternalContext.Initialize()
at System.Data.Entity.Database.Initialize(Boolean force)
LikeLike
This looks like an issue with creating the Edm return type for your stored procedure. Can you show me the type you are trying to return (or even better, provide me with a minimal, complete repro?)
Thanks,
Pawel
LikeLike
I think I figured out at least a portion of it. Originally I was trying to do this in an existing project. I created a new solution and have been pulling in TVFs and stored procs one at a time. Where I got stuck, I would create an EDMX and look at the generated cs file to see where I might have made a mistake. Some observations:
– It looks like all types need to be nullable in my result classes, so
public partial class GetChildProducts_Result
{
public Guid ProductGUID { get; set; }
public Guid ParentProductGUID { get; set; }
}
became
public partial class GetChildProducts_Result
{
public Nullable ProductGUID { get; set; }
public Nullable ParentProductGUID { get; set; }
}
– I needed to define the result classes as complex objects in OnModelCreating(), like so:
modelBuilder.ComplexType();
I am now walking through modifying code to handle stored procs that return multiple result sets and functions/stored procs that return scalar values.
Thanks again for your work on this project.
LikeLike
I am glad you were able to find a solution. Yes, if columns of the resultset returned by a stored procedure can contain NULL values then the corresponding properties of the type the resultset is mapped to have to be nullable.
And yes, the type the resultset is mapped to have to be part of the model. I actually remember that once my tests failed for the same reason but I don’t remember the exact exception. Either I thought the exception was reasonable or that this was not a typical scenario (if this is the latter then I was apparently wrong).
Thanks,
Pawel
LikeLike
I’ve been trying to apply your extension with SQL Server Fulltext search predicate “Contains()”, but to no avail.
What would I need to set DbFunctionAttribute to in order to have the Store Functions extension work with built in functions?
LikeLike
See Diego’s response on the same topic: http://entityframework.codeplex.com/workitem/2660
LikeLike
Have you noticed any issues with performance around this?
First let me say it has worked really well (meaning, the ability to use scalar functions was easy to set up and I’m getting the data I wanted), but, for me, I’m running into some big slow downs when I build a query using several functions and just run it in something like TOAD vs. doing the same in LINQ using the method above.
I have a query that takes about 5 seconds within TOAD and about 18 if I run it within .NET using the method above (and using LINQ).
I’m sorry this is terribly vague to not provide an example, but just figured I’d ask if anyone noticed a big performance hit like that. If I run this same query in LINQ but remove the functions, the query is quite fast (less than a second). I would expect the query to be a little slow, like when it runs in TOAD though because the functions used are doing sums of data (the query is using indexed columns though). I can do this without the functions (and maybe that’s what I’ll end up doing), but it just is a lot easier to use them.
Anyways, again, apologize for being vague, but just more of a general “has anyone noticed performance problems using scalar functions this way” question.
LikeLike
I am not aware of performance issues related to using this project. In the end to end scenario there can be multiple places (like query translation, execution, materialization etc.) which can cause performance issues and without narrowing down the area it is hard to tell what the issue is. One thing I don’t completely understand is that you said you can do the same thing with LINQ – if you can then why you want to do the same with store functions? Also, are you sure that when you compare performance of both methods both of them do the same thing (like – is the query sent to the database the same, is the client processing the same results in both cases etc.). Sorry – I don’t know what TOAD is.
Pawel
LikeLike
To answer your question of why I “can do the same thing with LINQ – if you can then why you want to do the same with store functions” … partly due to laziness to be honest.
However, I sucked it up and just re-did my query without functions. I used functions because the functions were to roll up some other data related to main data. Ex., I had a query that listed employees, and then the functions did summaries on their data in other tables. However, I decided just to not be lazy and just join everything I needed, and do the group by. The query is pretty cumbersome (a few outer joins, and some inner joins) to put together but it works well now, and I was just being a lazy coder using the functions.
I’m fairly experienced when it comes to SQL queries, but I’m fairly new to LINQ, so that was most of the reason for me doing it that way initially.
Anyways, I think you’re right that there’s so many places where the performance problem could have been, but figured I’d check if anyone experienced any issues with functions this way.
LikeLike
Oh, and TOAD is a fairly commonly used application for SQL (I think more so in the business world than anything though).
LikeLike
Working with EF is a bit different than working directly with the database. I think it is preferable to have the entire logic in one place instead of splitting it up between the app and the database. You are already using an ORM which kind of hides the database so, if a task at hand can be done in the app in a reasonable way doing it in the database breaks this pattern. An average C# developer may also not be skilled enough, comfortable with or just have sufficient permissions to change a TVF or stored procedure if they need to fix a bug or introduce a new feature. Having said that – world is not black-and-white and sometimes it is necessary to use store functions – e.g. you work with a legacy database that already has stored procs you need need to use, the company has a policy that data is only accessed/modified via stored procedures or maybe due to performance issues.
Another mistake people make is mimicing their SQL queries in Linq. In the majority of cases joins in Linq to Entities queries are examples of this. First, Linq to EF contains the .Include() extension method that allows joining related tables for you so you don’t have to write a join explicitly. Second, a complicated Linq query can be blown even more by the EF query pipeline and result in a very complicated SQL query which, while correct will perform poorly. Rather, it might be better to have two simpler queries and leave it to EF to fix up the relationships.
Thanks,
Pawel
LikeLike
I try to use this in an existing webapplication but when i add this code to the OnModelCreatiing:
modelBuilder.Conventions.Add(new FunctionsConvention(“dbo”));
I get this error on the existing EF queries that worked before:
Additional information: The argument ‘name’ cannot be null, empty or contain only white space.
When not using the FunctionConvention the old code works but then i get an error on the new SP function i added using your example:
public ObjectResult Security_UserIsSA(Guid userId)
{
var userIdParameter = new ObjectParameter(“UserId”, userId);
return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction(“FWAPP_Security_UserIsSA”,
userIdParameter);
}
I then get this error on this new SP function:
An exception of type ‘System.InvalidOperationException’ occurred in EntityFramework.dll but was not handled in user code
Additional information: The FunctionImport ‘FWAPP_Security_UserIsSA’ could not be found in the container ‘FwAppContext’.
Maybe you can steer me in the right direction on what is wrong?
LikeLike
I got it to work! The error came from my TVF, I needed to change the return type from a Guid to a complexType . There were also some errors where the SP method had a name other than SP in the database
LikeLike
hi Pawel
i try to call a stored procedure that return nonthing , just run update whatever.
but it seems i must define the method return type to be ObjectResult.
i try write code : ObjectResult
i got the error
“The data reader returned by the store data provider does not have enough columns for the query requested”
any suggestions?
thank you very much!
LikeLike
Hi JiangZhen,
If your stored procedure does not return a resulted then consider using Database.ExecuteSqlCommand
Thanks,
Pawel
LikeLike
thanks reply
Database.ExecuteSqlCommand way has some problem when changing database
like named parameter in sqlserver is @Variable but oracle is :Variable
again ,thanks you open source.
i wirte custome Convention to solve this problem .i check the db first generated MetadataWorkspace infomations . after some hours debug ,it works.. 😀
finally i just set EdmFunctionPayload object’s ReturnParameters property to zero array.
now i try to find same nice way to add this feature base on your code .
LikeLike
I am glad you found the project useful!
LikeLike
I can’t seem to get this to work; using your sample code I get an error about System.Data.Entity.Core.EntitySqlException: ‘dbo.CustomersByZipCode’ cannot be resolved into a valid type or function.
What am I missing?
LikeLike
Hi, i have this error .. i dont have idea what are i doing wrong
FirstCodeModel.NotifierSQLView’ cannot be resolved into a valid type or function. Near member access expression, line 1, column 17.
LikeLike
Show more code I don’t have enough context to understand what you are asking – I don’t even know if you are trying to invoke a stored procedure or a TVF.
Pawel
LikeLike
I have a new error:
No EdmType found for type ‘EFFunctions.firscode.NotifierSQLView’.
From:
[DbFunction(“FirstCodeModel”, “NotifierSQLView”)]
public virtual IQueryable NotifierSQLView(string userID, Nullable type)
{
var userIDParameter = userID != null ?
new ObjectParameter(“UserID”, userID) :
new ObjectParameter(“UserID”, typeof(string));
var typeParameter = type.HasValue ?
new ObjectParameter(“Type”, type) :
new ObjectParameter(“Type”, typeof(int));
return ((IObjectContextAdapter)this).ObjectContext.CreateQuery(“[FirstCodeModel].[NotifierSQLView](@UserID, @Type)”, userIDParameter, typeParameter);
}
LikeLike
I founded the problem, i add this lines on OnModelCreating:
modelBuilder.Conventions.Add(new FunctionsConvention(“dbo”));
modelBuilder.ComplexType();
LikeLike
Not sure why/if you need this line:
modelBuilder.ComplexType();but the first line is a must indeed. Without it things won’t work. Glad you were able to resolve this!Thanks,
Pawel
LikeLike
Hi, I got an exception when I use a model user function :
System.Data.Entity.Core.MetadataException: Schema specified is not valid. Errors:
(0,0) : error 0005: The ‘Aggregate’ attribute is not allowed.
(0,0) : error 0005: The ‘BuiltIn’ attribute is not allowed.
(0,0) : error 0005: The ‘NiladicFunction’ attribute is not allowed.
(0,0) : error 0005: The ‘IsComposable’ attribute is not allowed.
(0,0) : error 0005: The ‘ParameterTypeSemantics’ attribute is not allowed.
(0,0) : error 0005: The ‘Schema’ attribute is not allowed.
(0,0) : error 0005: The ‘Mode’ attribute is not allowed.
Can you help me ? Thanks
LikeLike
Show me how you set up things. This only tells me that the edmx is not built correctly but without seeing the code it is hard to tell why.
LikeLike
I figured out the bug. My apply function is called twice. Still investigating why. Thanks
LikeLike
Hi moozzyk,
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 FunctionsConvention(“dbo”));
}
but now all calls to my database context fail with the error “The argument ‘name’ cannot be null, empty or contain only white space,”
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 Categories { get; set; }
}
Am I missing something important here?
LikeLike
What is the stack trace? Do you have any other code than what you showed? Are you using the “dbo” scheme for your tables? If you can create a simple repro and post it on github I would take a look.
Thanks,
Pawel
LikeLike
Hi moozzyk,
Is there any way to make EF accept XML as an input parameter on a TVF? I’ve tried with XElement and SqlXml but both fail with the same message:
The type ‘System.Data.SqlTypes.SqlXml’ of the parameter ‘input’ of function ‘ParseStrings’ is invalid. Parameters can only be of a type that can be converted to an Edm scalar type.
I’m converting a project from Linq2SQL to EF and one method uses a TVF to pass in XML with many strings which forms a temporary table to join from in order to circumvent the 2100 parameter limit in a complicated search query. Your extension was a godsend as I discovered SqlQuery doesn’t return an IQueryable, however now I’m stuck since EF won’t accept XML as input .
LikeLike
Sorry to have bothered you, parsing it as a string seems to do the trick. 🙂
LikeLike
Yes, this is what I wanted to propose.
LikeLike
Unfortunately for me, this stops working when I try to use the function in an inherited context (e.g. “MyChildContext : MyContext”). Because then the type of the child context (“MyChildContext”) is the container name and conflicts with the namespace specified in the DbFunctionAttribute in the base context (“MyContext”). I have been experimenting for a while and could not come up with a solution yet, but my knowledge about the EF infrastructure is not as deep as yours. Is there a way to make this work?
LikeLike
I think you are running into: https://github.com/moozzyk/CodeFirstFunctions/issues/13. Unfortunately, it seems that fixing the problem would require changes to EF and this is not very likely to happen, so I guess the scenario is currently not supported. I am sorry.
Pawel
LikeLike
Is it maybe possible to declare a custom TVF in a static class outside the DbContext? I could not make it work and I wonder why, because other custom functions (non-TVF) worked just fine that way.
LikeLike
It’s been a while since I wrote this code and unfortunately I don’t remember from the top of my head why TVFs wouldn’t work if defined outside the DbContext class. I think discovery should work (as per this test: https://github.com/moozzyk/CodeFirstFunctions/blob/master/CodeFirstStoreFunctionsTests/FunctionDiscoveryTests.cs#L94-L99), so there must be something else. What error are you getting?
I don’t know if this can be helpful in your situation but maybe you could use partial classes to declare TVFs?
LikeLike