How can I filter rows where the first character of the column is in the range with EF and SQL Server?
I have the following table:
CREATE TABLE [dbo].[Phrase] (
[PhraseId] UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
[English] NVARCHAR (250) NOT NULL
)
Is it possible to use Entity Framework so that I can select all those rows where the first character of the [English] column is less than "k". For example, where is either "a", "b", "c" ... "j", etc.
Something like that:
query = query.Where(w => w.English.StartsWith("k");
but for a range of characters, not just "k"
source to share
You can filter this after fetching records from db. To do this, you can do the following:
var result = query.ToList();
And then filter it like this:
result = result.Where(w => Convert.ToChar(w.English.Substring(0,1).ToLower()) < 'k');
The SQL Server
following query will work:
SELECT * FROM table WHERE Substring(English, 1, 1) < 'k'
For a range of characters, your query would look like this:
SELECT * FROM table WHERE Substring(English, 1, 1) BETWEEB 'a' And 'j'
The above data will fetch records provided that the first character of the English column must be between a
and j
. Please note that a
and j
will be included in this case.
For certain characters only, your query will look like this:
SELECT * FROM table WHERE Substring(English, 1, 1) IN ('a', 'd', 'f')
The above return the records, English column that begins with a
, d
, f
. To negate, you can useNOT IN ('a', 'd', 'f')
I would suggest you write a procedure with the above query and then call it using an entity framework. This approach will be much faster.
source to share