Extract text from string inside LINQ-to-Entities expression

Question

I have the following entity saved at the database:

public class Company
{
  public string FullName {get; set;}
}

Property FullName could contains the following data: "Contoso" Ltd or just Contoso. I am using EF Core and I want to filter the data with this property by short name. I mean if I have "Contoso" Ltd record saved at the database and a user will search for ltd the search result will be null.
For this I tried to use the following LINQ expression:

var result = context.Company.Where(c => c.FullName.ExtractShortNameExtension().Contains(filterString));

with a combination of an extension method:

public static string ExtractShortNameExtension(this string name)
        {
            int firstQuotes = name.IndexOf('\"');
            int lastQuotes = name.LastIndexOf('\"');
            if (firstQuotes == -1 || firstQuotes == lastQuotes)
            {
                return name;
            }

            return name.Substring(firstQuotes + 1, lastQuotes - firstQuotes - 1);
        }

but I get System.InvalidOperationException because LINQ is not able to translate expression. I've tried to use Nein LINQ, but it didn't help me, I have the same exception.
As I realize using of SqlFunctions class might help me to solve the problem, but I want to avoid it, because I want to save the possibility to use my code with the other type of database.

Answer 1

I've managed with the issue with extension methods and LINQKit package. As I mentioned above my example model class looks the following way:

public class Company
{
  public string FullName {get; set;}
}

FullName might be Ltd. "Contoso" or "Contoso" Ltd. or just Contoso Ltd. so to avoid program overload I need to not to ignore Contain(filter) filter parameters such as ltd. (it has different types of words, not only this one). So I've implemented the following extension method to string class:

public static string ExtractShortNameExtension(this string name)
        {
            int firstQuotes = name.IndexOf('\"');
            int lastQuotes = name.LastIndexOf('\"');
            if (firstQuotes == -1 || firstQuotes == lastQuotes)
            {
                return name;
            }

            return name.Substring(firstQuotes + 1, lastQuotes - firstQuotes - 1);
        }

but it is not allowed to use it with EntityFramework, so I've added IQueryable extension method with LINQKit:

public static IQueryable<Company> CompanyNameCheck(this IQueryable<Company> query, string filterName)
        {
            if (filterName == null)
            {
                return query;
            }

            var predicate = PredicateBuilder.New<Company>(true);
            predicate = predicate.And(c => c.FullName.ExtractShortNameExtension().Contains(filterName));

            Task<IEnumerable<Company>> execute = Task.Run(() => query.AsExpandableEF()
                .Where(predicate.Compile()));

            execute.Wait();
            var result = execute.Result;

            return result.AsQueryable();
        }

Of course the real model is a little more complicated and contains nested entities and inner comparisons but an overall idea discribed above.

Answer 2

In Linq To Objects it could work, but, using Linq To Sql it won't work because think yourself, how could this function call (extension method) be translated to a valid SQL statement?

You should get the first name from the context.Company dbSet and then define it in memory. For sample:

var queryResult = context.Company.Where(c => c.FullName.Contains(filterString)).ToList();

var result = queryResult.Where(c => c.FullName.ExtractShortNameExtension().Contains(filterString)).ToList();

Answer 3

EF has to translate expressions to SQL. The general approach to doing something like this is to keep it in the domain logic using an unmapped property in the entity, or better, in a view model:

public class Company
{
    public string FullName {get; set;}

    [NotMapped]
    public string ShortName
    {
        get { return FullName.ExtractShortNameExtension(); }
    }
}

This doesn't help your Where clause. That requires expressing your filtering into something that SQL will understand. A start there would be "how would I write a suitable search in SQL with this data?".

Firstly, string Contains searches should generally be avoided by default as these translate down to LIKE %term% which is always going to be inefficient. Defaulting to a BeginsWith on the FullName would be a start:

var result = context.Company.Where(c => c.FullName.BeginsWith(filterString));

This would work with "Contonso" or "Cont", but not "Ltd". However It wouldn't match "tonso", though the first question would be "does it need to?". If 99% of users would expect to know the first few characters of a company name they are searching for, is it enough to cater for that with simple, fast querying, or introduce something more complex and slower to impose on every search to cater for maybe 1% of searches?

If someone is searching for a particular company name that is multi-word such as "Contoso Verico Ltd" and they don't get a match on the first word because all they remember was something like "Verico" then you can provide support for a full text search option with the searching, which would provide a Contains type search. This would allow them to search on "Verico" or "toso", or "ltd" (You could always filter out particular keywords if you really didn't want them searching on that) It reverts to a slower query, but it isn't the default for the majority of searches.

The other key considerations for searching to avoid users from crashing the service by searching for effectively everything would also include:

• Considering a minimum search term length, especially for Contains type searches.
• Always imposing a limit to the # of search results. (Take) using pagination for result lists or simply limiting the number of returned rows for things like auto-complete.