CODEWITHSUNDEEP
c#sql-serverlinq
user1464139
user1464139

Reputation:

How can I issue a LINQ query to a SQL Server to check if a column value starts with a word?

I have this already

        if (options.English != null) query = query
            .Where(w => w.English.Contains(options.English));

What I would like to do is to extend this (maybe with another if clause) to make it so that if:

I am using a SQL Server back-end database. Could anyone give me a suggestion as to how I could implement this functionality.

Upvotes: 2

Views: 88

Answers (3)

nikovn
nikovn

Reputation: 2000

Assuming that you're using Entity Framework, you can use the StartsWith() and EndsWith() methods, to achieve the same results as Contains() except only at the beginning or the end of a string. It will generate the code for you.

Then simply create conditional statements in your code, in order to determine which one of the methods you should use.

Some word of advice:

There might be a bug with EF Core, in which it turns StartsWith("string") into LIKE "string%" which might yield incorrect results with strings, containing wildcard characters such as "_".

So I'd advise you to use plain SQL with EF Core, and given that you're using SQL Server as a DBMS, query like that:

if (searchText.StartsWith("^"))
{
    var result = query.FromSql($"SELECT something FROM table WHERE PATINDEX({searchText.Substring(1)}, something) = 1");
}
else
{
    var result = query.FromSql($"SELECT * FROM table WHERE PATINDEX({searchText.Substring(1)}, something ) <> 0");
}

With PATINDEX() you will get correct results even if your pattern string contains wildcard characters - escaping potential bugs with relying on StartsWith() and EndsWith() to generate proper SQL code.

But that's only for EF Core, EF 6 works like a charm the way other people answered :)

Upvotes: 3

CodeCaster
CodeCaster

Reputation: 151584

You can put that choice in a conditional statement:

IQueryable<Whatever> query = ...;

if (searchText.StartsWith("^"))
{
    query = query.Where(w => w.English.StartsWith(searchText.Substring(1)));
}
else
{
    query = query.Where(w => w.English.Contains(searchText));
}

You can also do the same comparison inline, but that'll generate very ugly SQL, if it even works:

query = query.Where(w => 
        searchText.StartsWith("^")
            ? w.English.StartsWith(searchText.Substring(1))
            : w.English.Contains(searchText));

Do note that you generally don't want to search text using SQL, as that results in a pretty poor user experience. Take a look at full-text indexing.

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460098

if (options.English != null) 
{
    bool englishStartsWith = options.English.StartsWith("^");
    if(englishStartsWith)
    {
       query = query.Where(w => w.English.StartsWith(options.English.Substring(1)));
    }
    else
    {
       query = query.Where(w => w.English.Contains(options.English));
    }
}

Upvotes: 1

Related Questions