Howto implement String.Split function via SQLiteFunctions

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for SQLite
Post Reply
bairog
Posts: 113
Joined: Mon 29 Apr 2013 09:05

Howto implement String.Split function via SQLiteFunctions

Post by bairog » Thu 21 Mar 2019 04:31

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
Site Admin
Posts: 8926
Joined: Thu 14 Aug 2008 12:44

Re: Howto implement String.Split function via SQLiteFunctions

Post by Shalex » Tue 26 Mar 2019 11:17

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.

Post Reply