OData v4 filter query fails when using contains on calculated property

Question

So I have a code first EF 6 layer which has a Contact class of:

public class Contact
{
    [Key]
    public int Id { get; set; }

    [MaxLength(50)]
    public string Prefix { get; set; }

    [MaxLength(50)]
    public string Suffix { get; set; }

    [MaxLength(50)]
    public string FirstName { get; set; }

    [MaxLength(50)]
    public string MiddleName { get; set; }

    [MaxLength(50)]
    public string LastName { get; set; }

    [NotMapped]
    [DisplayName("Full Name")]
    public string FullName
    {
        get
        {
            string tempName =
                (!string.IsNullOrEmpty(Prefix) ? Prefix + " " : "") +
                (!string.IsNullOrEmpty(FirstName) ? FirstName + " " : "") +
                (!string.IsNullOrEmpty(MiddleName) ? MiddleName + " " : "") +
                (!string.IsNullOrEmpty(LastName) ? LastName + " " : "") +
                (!string.IsNullOrEmpty(Suffix) ? Suffix + " " : "");
            return tempName.Trim();
        }
    }

    [MaxLength(50)]
    public string JobTitle { get; set; }

    public bool? Primary { get; set; }
    public bool? Inactive { get; set; }

    public int? Customer_Id { get; set; }

    [ForeignKey("Customer_Id")]
    public virtual Customer Customer { get; set; }

    public virtual ICollection<Email> Emails { get; set; }
    public virtual ICollection<Address> Addresses { get; set; }
    public virtual ICollection<PhoneNumber> PhoneNumbers { get; set; }
    public virtual ICollection<Note> Notes { get; set; }
}

I have an ASP.NET Web API 2 service running that offers up a list of contacts, but when I perform an OData query of $filter=contains(tolower(FullName), tolower('smith')) I get a BadRequest response. I verified in the WebAPI get method that it is successfully getting results from the database, but it sends back a BadRequest error.

It definitely has something to do with the FullName field either being a calculated field or because it has the NotMapped attribute. When I change the OData query to $filter=contains(tolower(LastName), tolower('smith')) it works fine. I also tried using the display name of "Full Name" in the query instead of "FullName" and that too did not work.

Is there something I need to do to make OData play nice with a calculated or notmapped field?

Answer 1

Implement an OData function on your ContactsController that takes a string for comparison and returns the filtered set of Contacts. Something like:

    [HttpGet]
    [ODataRoute("Contacts/Default.FullNameContains(value={value})")]
    public IHttpActionResult FullNameContains(string value)
    {
        value = value.ToLower();
        return Ok(db.Contacts.ToList().Where(c => c.FullName.Contains(value)));
    }

Because FullName is computed, the function must perform the filtering in memory.

Answer 2

@lencharest's answer will give you the correct result, but keep in mind that you will be pulling all of the data into memory in order to perform the filter. Not a big deal if you have 100 contacts, but what if you have a large number?

I would create a view in the DB that includes the FullName logic. Then, expose this view as your entity. Then, the filtering can occur in the database and you can have a fully queryable entity.

UPDATE: With a little more consideration, an even better approach would be a computed column in the table for FullName. Assuming you may eventually need to support other verbs (POST, PATCH, etc...), having the full entity definition in the table will keep things straight-forward. By implementing the OData function, you've essentially given up on a queryable odata model. They have their place, but are typically used for complex, multi-entity operations, not to implement a filter on a single attribute for a basic entity.