CODEWITHSUNDEEP
c#entity-framework
Tom
Tom

Reputation: 63

Table-Valued Function as IQueryable in EF?

I have a Table-Valued function which I would like to use as an IQueryable in a LINQ statement. I have created the following in my DbContext class:

[DbFunction("dbo","MyTableValuedFunction")]
public virtual IQueryable<MyClass> MyFunction(string keyword)
{
    var keywordsParam = new System.Data.Entity.Core.Objects.ObjectParameter("keywords", typeof(string))
    {
        Value = keyword
    };

    return (this as System.Data.Entity.Infrastructure.IObjectContextAdapter).ObjectContext
        .CreateQuery<MyClass>("dbo.MyTableValuedFunction(@keywords)", keywordsParam);
}

The result of dbo.MyTableValuedFunction(@keywords) match an existing class "MyClass". An example of how I'd like to use this function:

MyClass example = (from a in dbContext.MyClass
                  join b in dbContext.MyFunction("exampleKeyword")
                      on a.Id equals b.Id
                  join c in dbContext.MyOtherClass
                      on a.SomeId equals c.Id
                  select a);

...but enumerating this IEnumerable throws an exception: 'dbo.MyTableValuedFunction' cannot be resolved into a valid type or function.

I have tried reducing the function to just one column, and changing the type to <int>, but this doesn't work, so I am not sure what is happening here; it's like the TVF is not being correctly found/recognised/used?

I have also tried following https://weblogs.asp.net/Dixin/EntityFramework.Functions#Table-valued_function and on the off-chance there's some subtle difference, I create the function with: [Function(FunctionType.TableValuedFunction, "MyTableValuedFunction", Schema = "dbo")]

...but I run into exactly the same problem.

Upvotes: 4

Views: 841

Answers (1)

Ali Fattahian
Ali Fattahian

Reputation: 495

First if you used this extension, you should write following code according to your code:

[DbFunction("dbo","MyTableValuedFunction")]
public virtual IQueryable<MyClass> MyFunction(string keyword)
{
    var keywordsParam = new System.Data.Entity.Core.Objects.ObjectParameter("keywords", typeof(string))
    {
        Value = keyword
    };

    return (this as System.Data.Entity.Infrastructure.IObjectContextAdapter).ObjectContext
        .CreateQuery<MyClass>("[Your DbContext Name].MyFunction(@keywords)", keywordsParam);
}

According to the extension codes, the EF uses conventions to translate the C# syntax into sql syntax. for this reason, you should register your functions in dbContext first as the link mentioned.

Upvotes: 1

Related Questions