Anyone using this successfully with code-first approach?

[skurik]

Hi, I am trying to use this library for importing the STRING_SPLIT function and having it called inside a LINQ query, but it's not working

I have

[DbConfigurationType(typeof(DbContextConfiguration))]
public abstract class DbContext : System.Data.Entity.DbContext
{
    [Function(FunctionType.TableValuedFunction, name: nameof(StringSplit), namespaceName: nameof(DbContext), Schema = "dbo")]
    public IQueryable<StringSplitResult> StringSplit(
        [Parameter(DbType = "nvarchar", Name = "string")] string @string,
        [Parameter(DbType = "nvarchar", Name = "separator")] string separator)
    {
        var sourceParameter = new ObjectParameter("Source", @string);
        var separatorParameter = new ObjectParameter("Separator", separator);
        
        return ObjectContext.CreateQuery<StringSplitResult>($"STRING_SPLIT(@Source, @Separator)", sourceParameter, separatorParameter);
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.AddFunctions<DbContext>();
    }
}

[ComplexType]
public class StringSplitResult
{
    public string Value { get; set; }
}

but when using it in a LINQ query like this

EntityContext.DbContext.StringSplit(joined, ",").Any(r => r.Value == s.Id.ToString())

it throws

System.NotSupportedException: The specified method 'System.Linq.IQueryable`1[Mews.EntityFramework.Db.StringSplitResult] StringSplit(System.String, System.String)' on the type 'Mews.EntityFramework.Db.DbContext' cannot be translated into a LINQ to Entities store expression.
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.ThrowUnresolvableFunction(Expression Expression)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.FindFunction(String namespaceName, String functionName, IList`1 argumentTypes, Boolean isGroupAggregateFunction, Expression Expression)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.FunctionCallTranslator.TranslateFunctionCall(ExpressionConverter parent, MethodCallExpression call, DbFunctionAttribute functionAttribute)
   at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)

[kemsky]

@skurik, yes, it is possible, but you will have to fork this library, because it has several bugs.

See example here (uses alternative): https://erikej.github.io/ef6/sqlserver/2021/12/01/ef6-sqlserver-cache-pollution.html