How to use string functions like Contains or StartWith with string backed StronglyTypedId for Like queries in ef core

[amit-srivastava-007]

Hello, I'm having a situation where a StronglyTypedIdBackingType.String ids need to be used in a ef core query with StartsWith. Currently, I'm not seeing any string function available for such ids. Please guide me how to use it.

I'm using v1.0.0-beta06 of the package

Example:

let's say abc/efg/mnpxyz is a value of an Id field stored as hierarchy for city/ward/building for Office entity.

public class Office
{
  [StronglyTypedId(backingType: StronglyTypedIdBackingType.String)]
  public partial struct Id { }
}

I want to write query

Office.Where(x=>x.Id.StartsWith("abc").Select(x=>x);

[andrewlock]

Unfortunately, I think the short answer is that you can't currently. I'm not aware of a mechanism in EF Core to allow this currently. I'm certainly interested if someone can show how to make it work, but the IDs generally are intended to be opaque IDs.

[loop8ack]

You could take a look at this:
https://learn.microsoft.com/en-us/ef/core/querying/user-defined-function-mapping#mapping-a-method-to-a-custom-sql

I used it once to make an important business calculation available in EFCore queries, it was easier than I expected.

[timdeschryver]

As @loop8ack mentioned a DbFunction provides a solution.

We've stumbled across the same issue the other day, and I wrote blog post to document our solution.
Maybe that it can help others as well who stumble on this issue, https://timdeschryver.dev/blog/create-a-custom-sql-expression-with-entity-framework-or-how-to-make-ef-interpret-your-value-types

I hope it's OK to add a shameless plug 😅