How to Make Linq Execute a (Sql) Like Range Search

How to make LINQ execute a (SQL) LIKE range search

The problem here is that expressions containing Regex can't be translated to SQL, so even when you'd succeed in building a correct expression, you can't use it in LINQ to a SQL backend. However, SQL's LIKE method also supports range wildcards like [0-9], so the trick is to make your LINQ translate to SQL containing a LIKE statement.

LINQ-to-SQL offers the possibility to use the SQL LIKE statement explicitly:

return namesList.OrderBy(r => SqlMethods.Like(r.Name, "[0-9]%")) ...

This SqlMethods class can only be used in LINQ-to-SQL though. In Entity Framework there are string functions that translate to LIKE implicitly, but none of them enable the range wildcard ([x-y]). In EF a statement like ...

return namesList.OrderBy(r => r.Name.StartsWith("[0-9]")) ...

... would translate to nonsense:

[Name] LIKE '~[0-9]%' ESCAPE '~'

I.e. it vainly looks for names starting with the literal string "[0-9]". So as long as you keep using LINQ-to-SQL SqlMethods.Like is the way to go.

In Entity Framework 6.1.3 (and lower) we have to use a slightly different way to obtain the same result ...

return namesList.OrderBy(r => SqlFunctions.PatIndex("[0-9]%", c.Name) == 1) ...

... because PatIndex in SqlFunctions also supports range pattern matching.

But in Entity Framwork 6.2 we're back on track with LINQ-to-SQL because of the new DbFunctions.Like function:

return namesList.OrderBy(r => DbFunctions.Like(r.Name, "[0-9]%")) ...

Finally, also Entity Framework core has a Like function:

return namesList.OrderBy(r => EF.Functions.Like(r.Name, "[0-9]%")) ...

How to use SQL 'LIKE' with LINQ to Entities?

http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/6529a35b-6629-44fb-8ea4-3a44d232d6b9/

var people = entities.People.Where("it.Name LIKE @searchTerm", new ObjectParameter("searchTerm", searchTerm));

How to do SQL Like % in Linq?

.Where(oh => oh.Hierarchy.Contains("/12/"))

You can also use .StartsWith() or .EndsWith().

Use LIKE %{%}% in Entity Framework with Linq?

When I need to perform such LIKE queries with entity framework (and this happens very rarely, because such like queries usually cannot use any index and so perform full table scan and are quite slow on big tables), I use PATINDEX, like this:

var routelist = (from dbHost in db.Hosts
                         where dbHost.Host == host
                         join dbRoute in db.Route on dbHost.HostsId equals dbRoute.HostId
                         where SqlFunctions.PatIndex("%{%}%",dbRoute.alias) > 0
                         select dbRoute).ToList();

PATINDEX function in sql server is like LIKE, but returns the position of the first match.

If you want to perform LIKE query in a form of "%something%", you can use Contains("something"). If it has the form of "%something" - use StartsWith("something"). If it has the form of "something%" - use EndsWith("something").

How to perform a date range filter on string with LINQ Query

While storing dates as string in database is not a good idea, at least the chosen format is orderable. And while EF Core does not provide translatable method for converting string to date, it allows you to have entity property of type DateTime (as it should have been), and map it to string column in database using value converter. Thus, you would write query against DateTime, and EF Core will convert the constant/parameter values to string and pass them to the SQL query.

Applying it to your case:

Model:

public class Movie
{
    // other properties...

    public DateTime TransDate { get; set; }
}

Configuration:

const string DateFormat = "yyyy-MM-dd";

modelBuilder.Entity<Movie>().Property(e => e.TransDate)
    .HasConversion(
        dateValue => dateValue.ToString(DateFormat, CultureInfo.InvariantCulture),
        stringValue => DateTime.ParseExact(stringValue, DateFormat, CultureInfo.InvariantCulture)
    )
    .IsRequired()
    .IsUnicode(false) // arbitrary
    .HasMaxLength(10); // arbitrary

LINQ query usage:

IQueryable<Movie> movies = ...;
DateTime startDate = ...;
DateTime endDate = ...;

movies = movies
    .Where(e => e.TransDate >= startDate && e.TransDate <= endDate);

Linq selecting range of records

How about:

var q = (
from Comments in db.tblBlogComments 
where Comments.blogID == this.ID 
orderby Comments.date descending 
select new { Comments.userID, Comments.comment, Comments.date }).Skip(10).Take(10);

---

Related Topics