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.

" I want to save the possibility to use my code with the other type of database." : unfortunately, entity framework can easily be "overrated" at this point. At the end, you depend on the db / dbconnector used. Some functions could be translated bbut will offer very poor performance on some db, while other expressions cannot be translated at all. — Pac0
– Pac0, Commented Sep 19, 2021 at 21:43
@ChristophLütjen yes, I know that I can use client side processing, but I want to avoid it, because I am going to have thousands of records. I've tried something this: return name => (name.IndexOf('\"') != -1) && (DbFunctions.Reverse(name).IndexOf('\"') != -1) && name.IndexOf('\"') != DbFunctions.Reverse(name).IndexOf('\"') ? name.Substring(name.IndexOf('\"') + 1, DbFunctions.Reverse(name).IndexOf('\"') - name.IndexOf('\"') - 1) : name; but it also throw an exception due to failed translation. — Misha
– Misha, Commented Sep 19, 2021 at 21:50

Steve Py · Accepted Answer · 2021-09-19 21:46:31Z

1

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.

answered Sep 19, 2021 at 21:46

Steve Py

36.4k4 gold badges34 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Misha Over a year ago

Thank you, your answer gives me a food for thought how to definitely implement the process of filterring. But some of my records looks like Ltd "Contoso", so this will interfere to use BeginsWith. In fact I am dealing with parsed and saved at DB data, so now I am thinking about to add a new column representing a short name to the database which will be filled while the parsing is processed.

Steve Py Over a year ago

Yes, if the source data could be mixed like that where the details of the full name could be mixed up like that then having a processing step to run a more thorough rule to populate more suitable persisted columns would be good. If this data is coming from an external source of truth and it's possible that a name could be updated then consider recording a staging table for the process to refer to decide if / when the parsed details might need to be refreshed.

Felipe Oriani · Accepted Answer · 2021-09-19 21:46:50Z

0

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();

answered Sep 19, 2021 at 21:46

Felipe Oriani

38.7k20 gold badges141 silver badges203 bronze badges

Comments

Misha · Accepted Answer · 2021-09-29 20:16:30Z

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.

Collectives™ on Stack Overflow

Extract text from string inside LINQ-to-Entities expression

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related