X

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.

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.

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.

Liked this post? Empower your friends by sharing it!

View Comments (5)

  • Hi, thanks for your post.

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

  • 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.

  • @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'

  • 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 + "%")

Related Post