Cannot use TableValuedFunction within LINQ to Entities query

[mniemirka]

When method marked as FunctionType.TableValuedFunction is used within IQueryable linq method chain, exception: 'The specified method XXX on the type YYY cannot be translated into a LINQ to Entities store expression.' is thrown.

[Dixin]

@mniemirka Can you share your code so that I can look into?

[micahbright]

I have the same problem. I am using 1.4.0. Basically, I'm trying to use my function as a subquery. My LINQ to Entities query:

        _db.Things
            .Where(d => MyGuids.Contains(d.Guid.Value))
            .SelectMany(c => _db.fnMyFunction(c.Value)).Distinct()
            });

Where MyFunction is like this:

  [Function(FunctionType.TableValuedFunction, nameof(fnMyFunction), "MyModel", Schema = "dbo")]
    public IQueryable<MyReturnType> fnMyFunction(
        [Parameter(DbType = "uniqueidentifier", Name = "Value")]Guid Value,
        )
    {
        ObjectParameter valueParameter = new ObjectParameter("Value", Value);
        return this.ObjectContext().CreateQuery<MyReturnType>(
            $"[dbo].[{nameof(fnMyFunction)}](@Value)", valueParameter);
    }

[ahocquet]

Same issue here

[osnoser1]

Same problem... 😪

[osnoser1]

Fixed! I use in namespaceName the value nameof(MyContext).

[SmittyTan]

Hello guys, I am still having this issue. Here is my snippet, can you please point me on the right direction using EF 6.1.3

--------------database function---------------------

CREATE FUNCTION [dbo].[Split]
(   
      @Input NVARCHAR(MAX),
      @Delimiter CHAR(1)
)
RETURNS @Output TABLE (
         ID int IDENTITY(1,1) PRIMARY KEY,
      Item VARCHAR(100) --PRIMARY KEY
)
AS
BEGIN
……
END
RETURN

------------inside the context ------------------

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

[Function(FunctionType.TableValuedFunction, nameof(Split), nameof(CurriculumContext), Schema = "dbo")]
        public IQueryable<Keyword> Split(string input, string delimiter)
        {
            List<ObjectParameter> parameters = new List<ObjectParameter>();
            parameters.Add(new ObjectParameter("input", input));
            parameters.Add(new ObjectParameter("delimiter", "delimiter"));
            string functionStr = string.Format("[{0}].{1}", GetType().Name, "[Split](@input, @delimiter)");           
            return this.ObjectContext().CreateQuery<Keyword>(functionStr, parameters.ToArray());
        }

inside the service

            var assessments = _unitOfWork.RepositoryFor<Assessment>()
                .AllIncluding(a => a.TeacherAssessmentLinks)
                .Where(a => _unitOfWork.Split("adaptive", ",").Where(b => a.Title == b.Item).Count() > 0);

-------- exeption  occurred right here-----
var test1 = assessment.ToList()

exceptions: INQ to Entities does not recognize the method 'System.Linq.IQueryable`1[Curriculum.Core.Domain.Keyword] Split(System.String, System.String)' method, and this method cannot be translated into a store expression

If I call it directly it worked: var test = _unitOfWork.Split("hello,world", ",").Where(a => a.Item == "hello"); test1 = test.ToList();

Please advise and thank you in advance!

[SmittyTan]

osnoser1 - I am wondering if you could help me out here. thank you so much in advance!

[SmittyTan]

@Dixin, I am wondering if you know what is the cause of this issue. Thanks in advance!

Smitty