Devart Forums

Posted: **Thu 21 Mar 2019 04:31**

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

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.

Posted: **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.

Devart Forums

Howto implement String.Split function via SQLiteFunctions

Howto implement String.Split function via SQLiteFunctions

Re: Howto implement String.Split function via SQLiteFunctions