Using the LIKE filter in Entity Framework

Michael Roma

In Entity Framework, there is no LIKE statement, but the PATINDEX function can be used. Consider the following domain:

// Base table entity
public abstract class TableBase
{
    [Key]
    public int id { get; set; }
}


// Customer entity
public class Customer: TableBase
{                    
    // customer fields   
    public string Name { get; set; }        
    public int? Type { get; set; }

    // contacts collection
    public virtual ICollection Contacts { get; set; }
}

// Contact entity
public class Contact : TableBase
{
    // contact fields
    public string FirstName { get; set; }
    public string LastName { get; set; }        
}

// Customer View entity (not mapped)
[NotMapped]
public class CustomerView
{
    // define customer
    public Customer Customer { get; set; }

    // define count of contacts
    public int ContactCount { get; set; }
}

You can use the SqlFunctions.PatIndex function to filter by customers with a B in the name.

var customers = 
    Customers
        .Where(c => SqlFunctions.PatIndex(“%b%”, c.Name) > 0)
        .ToList();