EF Core Linq Query to Select Rows That Match From a List of Possibilities

Question

Assume a DbSet<Thing> things; and Thing has a Name property.

If I want to retrieve a list of things that match a list of names, it's pretty straightforward:

var names = new List<string>{"John", "Jacob", "Jinkelheimer", "Smith"};

return things.Where(t => names.Contains(t.name));

However, what if I want to match a list of partial name possibilities?

var nameBits = (new List<string>{"ohn", "mit"}).ToLower();

// this doesn't seem good / right / efficient
things.Where(t => nameBits
   .Select(bit => EF.Functions.Like(t.Name, $"%{bit}%")
   .Any(m => m)
);

// I could also go with:

things.Where(t => nameBits
     .Select(bit => t.Name.Contains(bit)).Any(m => m)
);

... but I don't know how efficient any of those will be. How would I write the linq query that would efficiently match "John", "Johnathan", "Smith", "Smitty", etc using ohn and mit?

Sadly, SqlFunctions.PatIndex() will not work for my use case - I'm targeting an Oracle database. :-( — Scott Baker, Jan 29 '20 at 20:57
Then your best bet is probably to write a raw SQL query. I'll write an answer to that effect. — Robert Harvey, Jan 29 '20 at 20:59

score 1 · Accepted Answer · answered Jan 29 '20 at 21:03

1

The great thing about Entity Framework is that you can always drop to raw SQL when things aren't working out on the C# front.

var names = context.Names
    .FromSqlRaw("select Name from Names where name like %{0}%", name)
    .ToList();

Writing Raw SQL Queries in Entity Framework Core

answered Jan 29 '20 at 21:03

Robert Harvey

178,213
47
333
501

1

How do I use SQL to say "`where name like %a% or %b% or %c%`" - basically `like` "any item in this array"? – Scott Baker Jan 29 '20 at 21:17
Is the list of names small enough where you can just bring the entire list into memory and use ordinary Linq on it? – Robert Harvey Jan 29 '20 at 21:21
I think we're looking at a Full Text Search. – Robert Harvey Jan 29 '20 at 21:26
I don't know if I can get that column indexed or not - do you think something like this might work? https://stackoverflow.com/a/41230612/127888 – Scott Baker Jan 29 '20 at 21:35
It's worth a try. – Robert Harvey Jan 29 '20 at 21:39

EF Core Linq Query to Select Rows That Match From a List of Possibilities

1 Answers1