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