Howto implement String.Split function via SQLiteFunctions

[bairog]

Hello.
Let's say we have the following entity (I use EF6):

Code: Select all

public class MyEntity
{
public Guid Id { get; set; }
public String Name { get; set; }
public Int32 Category { get; set; }
public String Directions { get; set; }
}

And last property is filled with comma separated direction names:

Code: Select all

MyEntity.Directions = "East, North, South";

I want to select entites with Linq to Entites applying among others a filter by direction name:

Code: Select all

var result = MyDBContext.MyEntites.Where(e => (e.Category == 123) && (e.Directions.Split(',').Contains("East")).Tolist()

The code above fails because Linq to Entites doesn't support System.String[] Split(char[]) method.
The only workaround for now is to switch from Linq to Entites to Linq to Objects via .AsEnumerable() method:

Code: Select all

var result = MyDBContext.MyEntites.Where(e => e.Category == 123).AsEnumerable().
Where(e => e.Directions.Split(',').Contains("East")).Tolist()

But that approach is ugly and slow (because .AsEnumerable() loads data from database to memory - and amount of that data is significantly more that I really need).

So is it possible to get the first query work (maybe with the hel of SQLiteFunctions class)?

P. S. If it will be easier to implement I can replace String Directions -> "East, North, South" with Int32 DirectionCodes -> "0, 2, 3" and filter by Int32 value.

[Shalex]

Here is an example of implementing user-defined functions: viewtopic.php?f=29&t=28777. But Entity Framework Classic (EF6) puts limitations on parameter types and returned types mapped in EF model. Split function returns string[], but EF6 mapping doesn't allow returning the array, it allows returning single scalar value of primitive type or collection of complex types or entities.

As a workaround, create a function with a public interface for using in the LINQ query:

Code: Select all

    bool SplitContains(string value, string separator, string item)

Its logic should do Split(separator), apply Contains(item) to the result of splitting, then return bool.