Entity Framework greatly facilitates the development of systems using databases. We will not argue about the advantages and disadvantages of this framework (of which, of course, a lot), but consider one of the practical problems that I had to solve when developing such a system.

Suppose we have a SQLite database with a rather large number of records, and this database is used in our .NET application via System.Data.SQLite and the Entity Framework 6.0 . And here comes the customer and reports that he needs a new function to search for records in the database, so that you can search using standard regular expressions.

In this article, I will tell you how I achieved that the processing of the regular expression specified in the Linq query takes place on the server side, which makes it faster to process and prevent meaningless inflation of the client application memory due to the preliminary download of all data.

And what is the problem?

And the problems, generally speaking, are two.
First of all, Linq-to-Entities by default is not able to transfer calls to standard ones. NET regular expression methods ( Regex and Match classes) in SQL queries. It is understandable - not all DBMS can calculate regular expressions. It would be possible to write your Linq- provider, but I still want to do with “a little blood” (after all, there is already a ready provider in System.Data.SQLite ).
')
Secondly, it would be possible not to go deep into such jungle at all and to perform processing locally, on the client side. But it is obvious that the decision is unsuccessful - in order to perform simple filtering, you will have to download all the data from the table, in the worst case, then drop all records. And this is the use of memory and the loss of performance on the client side.

Fortunately, SQLite will help us.

In SQLite there is a special REGEXP operator, which is nothing but syntactic sugar for the user function regexp () . By default, this function is not implemented, and its call will cause an error, so the user must define this function before using it in his queries.

It is from this that we will make a start. The task boils down to the following:

• register the regexp () function on the SQLite server;
• force Linq-to-Entities to translate our Linq- query so that the REGEXP operator or regexp () function is used.

So let's get started.

System.Data.SQLite already has the functionality for registering user-defined functions, which is what we will use.
Create a class that implements the function:

[SQLiteFunction(Name = "REGEXP", Arguments = 2, FuncType = FunctionType.Scalar)] public class RegExSQLiteFunction : SQLiteFunction { public static SQLiteFunctionAttribute GetAttribute() { return (SQLiteFunctionAttribute)typeof(RegExSQLiteFunction).GetCustomAttributes(typeof(SQLiteFunctionAttribute), false).Single(); } public override object Invoke(object[] args) { try { //  ,       return Regex.IsMatch((string)args[1], (string)args[0]); } catch (Exception ex) { //   System.Data.SQLite,      return ex; } } } 

To register this function, call the appropriate method on the connection object:

 var connection = new SQLiteConnection(connectionString); connection.Open(); connection.BindFunction(RegExSQLiteFunction.GetAttribute(), new RegExSQLiteFunction()); 

Everything! Now, when using the REGEXP statement in any SQL query executed by the server, our implementation will be called. Convenient, isn't it?

How to call this through Linq in the Entity Framework?

Here begins the "black magic."
When creating the Entity Framework model, we need to register in it a new agreement describing our new function. To do this, for example, you can add the following method call in the data context class when it is created:

 protected override void OnModelCreating(DbModelBuilder modelBuilder) { //       Code First //     modelBuilder.Conventions.Add(new RegexpFunctionConvention()); } 

And the agreement class itself describes our function and makes it “understandable” to the Linq - System.Data.SQLite adapter:

 public class RegexpFunctionConvention : IStoreModelConvention<EdmModel> { public void Apply(EdmModel item, DbModel model) { //     var patternParameter = FunctionParameter.Create("pattern", this.GetStorePrimitiveType(model, PrimitiveTypeKind.String), ParameterMode.In); var inputParameter = FunctionParameter.Create("input", this.GetStorePrimitiveType(model, PrimitiveTypeKind.String), ParameterMode.In); //   var returnValue = FunctionParameter.Create("result", this.GetStorePrimitiveType(model, PrimitiveTypeKind.Boolean), ParameterMode.ReturnValue); var function = this.CreateAndAddFunction(item, "regexp", new[] { patternParameter, inputParameter }, new[] { returnValue }); } private EdmFunction CreateAndAddFunction(EdmModel item, string name, IList<FunctionParameter> parameters, IList<FunctionParameter> returnValues) { EdmFunctionPayload payload = new EdmFunctionPayload { StoreFunctionName = name, Parameters = parameters, ReturnParameters = returnValues, Schema = this.GetDefaultSchema(item), IsBuiltIn = true // ,      }; EdmFunction function = EdmFunction.Create(name, this.GetDefaultNamespace(item), item.DataSpace, payload, null); item.AddItem(function); return function; } //    } 

And the last thing to do is to create a “stub method” for the compiler so that, based on it, the Linq provider will already generate a real function in the SQL query.

 internal static class R { [DbFunction("CodeFirstDatabaseSchema", "regexp")] public static bool Regexp(string pattern, string input) { //       throw new NotImplementedException(); } } 

Living example

Having done all the preparatory work, we can finally reap the benefits and write concise Linq- requests that will also be performed on the server side (albeit using the implementation of the client-side user function, but this is not the same as processing the request using Linq-to-Objects locally).

 //   entities  Item,     Name    pattern using (MyDataContext context = new MyDataContext(myConnection)) { var filteredItems = context.Items.Where(i => R.Regexp(pattern, i.Name)); } 

Thus, we get IQueryable running on the server, with all its advantages and advantages!
I would be glad if my small article will help you in solving similar problems.