vendredi 27 février 2015

Entity Framework 6 and SQLITE GLOB operator

I have been converting my application to use SQLite and the SQLite Entity Framework 6 provider from a different database. The application has a search page that allows the user to enter a value to match a column in a table that can include %, _, and [xyz] type of expressions.


The previous database's LIKE operator supported matching strings with these constructs. It turns out that while the SQLite LIKE operator does support % and _ characters, it does not support the square bracket syntax. The SQLite GLOB operator, however, does support square brackets! The only problem is, I don't know how to generate an EF6 expression that will output SQL that uses the GLOB operator.


Some additional information: The EF model was generated using Database First. Tables involved:



CREATE TABLE Read (
ReadRowId INTEGER NOT NULL AUTOINCREMENT,
Plate TEXT NULL
);

CREATE TABLE ExplodedPlates (
Plate TEXT NOT NULL,
ReadRowId INTEGER NOT NULL
);


I'm currently using code like this to generate a LIKE expression:



// query is an IQueryable<T> that has been built using other expressions that don't use LIKE.
if ( !string.IsNullOrWhiteSpace( plate ) ) {
// The SQLite context class's members are all DbSet<> types. We have to convert them into
// ObjectSet<> types in order to do our LIKE query.
ObjectContext objectContext = ( (IObjectContextAdapter) context ).ObjectContext;
ObjectSet<ExplodedPlate> objectSet = objectContext.CreateObjectSet<ExplodedPlate>();

var temp = objectSet.Where( "it.plate LIKE @searchTerm", new ObjectParameter( "searchTerm", plate ) );

query = ( from info in query
join tmp in temp on info.Read.ReadRowId equals tmp.ReadRowId
select info ).Distinct();
};


I tried changing LIKE in the var temp = line to GLOB, but that throws an exception.


As far as I can tell from searching the web, SQLite is the only database with a GLOB operator, and it is unfortunate that the LIKE operator doesn't support square brackets like other databases do.


How can I make my query execute a GLOB instead of a LIKE?


Aucun commentaire:

Enregistrer un commentaire