Like-operator in Entity Framework Core 2.0

Entity Framework Core 2.0 was announces yesterday and it brings some new and cool features. One of them is SQL Like-operator we can use now using directly in our code. Here is the simple web application that demonstrates using of new Like-operator.

Let’s start with simple Songs table in SQL Server database.

MSSQL: Songs table

Here are the model for table and database context.

public class Song
{
    public int Id { get; set; }
    public string Artist { get; set; }
    public string Title { get; set; }
    public string Location { get; set; }
}

public class PlaylistContext : DbContext
{
    public PlaylistContext(DbContextOptions<PlaylistContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Song>()
                    .HasKey(e => e.Id);

        base.OnModelCreating(modelBuilder);
    }

    public virtual DbSet<Song> Songs { get; set; }
}

I skip configuring database connection and all the stuff we need to do in Startup class as web is full of examples about these topics.

Let’s query out some songs from songs table in Home controller and let’s write the results out. Take a look at LINQ-query in Index method – this is where new Like-operator is used.

public class HomeController : Controller
{
    private readonly PlaylistContext _context;

    public HomeController(PlaylistContext context)
    {
        _context = context;
    }

    public IActionResult Index()
    {
        var query = from s in _context.Songs
                    where EF.Functions.Like(s.Title, "%angel%")
                    select s;
                       
        return View(query);
    }

    public IActionResult Error()
    {
        return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
    }
}

Index view needs also some changes to print out the list of songs found.

@model IEnumerable<Song>
@{

    ViewData["Title"] = "Angel songs";
}

<div class="row">
    <div class="col-md-4">
        <h2>Songs</h2>

        <table class="table table-bordered">
            <thead>
                <tr>
                    <th>Artist</th>
                    <th>Title</th>
                </tr>
            </thead>
            <tbody>
                @foreach (var song in Model)
                {
                    <tr>
                    <td>@song.Artist</td>
                    <td>@song.Title</td>
                    </tr>
                }
            </tbody>
        </table>
    </div>
</div>

Let’s run the application and see the output.

Entity Framework Core: Angel songs

Here is the the SQL-query generated by Entity Framework Core 2.0. If you need to see SQL generated by EF then one quick option is described in blog post Entity Framework Core 2.0 Trace Strings.

SELECT [s].[Id], [s].[Artist], [s].[Location], [s].[Title]
FROM [Songs] AS [s]
WHERE [s].[Title] LIKE N'%angel%'

This is it. Like-operator works like charm and generated SQL looks also okay.

Wrapping up

Using Like-operator is made easy in Entity Framework Core 2.0. There is special functions class EF.Functions that provides us with Like-operator but there is not much more. I’m not sure what’s next but it seems like this class will be extended in future with additional methods that help us keep queries on LINQ level and avoid writing direct SQL.

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.

    6 thoughts on “Like-operator in Entity Framework Core 2.0

    • Pingback:The Morning Brew - Chris Alcock » The Morning Brew #2406

    • August 17, 2017 at 1:56 am
      Permalink

      Hi, thanks for your post.

      How is this different from Linq **_context.Songs.Where(title=> title.Contains(“angel”)**

    • August 17, 2017 at 6:47 am
      Permalink

      Hi!
      Thanks for question. s.Title.Contains(“angel”) translates to (CHARINDEX(N’angel’, [s].[Title]) > 0). I guess it is handled different from LIKE operator.

    • August 21, 2017 at 10:53 am
      Permalink

      @Jose The LIKE operator starts getting different when you use the placeholder “%” in other places than at the start and end of the term. E. g. LIKE ‘dying%angel’

    • August 22, 2017 at 12:55 pm
      Permalink

      Good point! Thank you both for your answers

    • October 16, 2018 at 12:55 pm
      Permalink

      how do pass in a variable to the LIKE functions? I can’t find anything online to help me.

      example:
      string query = “angel”;
      EF.Functions.Like(s.Title, “%” + query + “%”)

    Leave a Reply

    Your email address will not be published. Required fields are marked *