How to use EF.Functions.Format in EF Core query condition

Question

I have BirthDate column in my UserBaseInfos table in SQL Server. I can get month of Persian Calendar in SQL Server by using

FORMAT(BirthDate, N'MM','fa-IR')

For example

SELECT
    BirthDate, FORMAT(BirthDate, N'MM','fa-IR') 
FROM [UserBaseInfos]

Also I can use it in a WHERE clause:

SELECT
    BirthDate, FORMAT(BirthDate, N'MM', 'fa-IR') 
FROM
    [UserBaseInfos]
WHERE
    FORMAT(BirthDate, N'MM', 'fa-IR') = 5

Now I want write this query in EF Core and filter records that Persian date month value is 5. But I get an error :

Error CS1501 No overload for method 'Format' takes 3 arguments

query = query.Where(q => EF.Functions.Format(q.BirthDate, "MM", "fa-IR") == 5);

How to write this query in EF Core without fetching data by ToList() and get directly from database on my IQueriable query variable?

Answer 1

Format function doesn't exist in EF Core. To achieve your goal, you can convert birthDate in code or use FromSqlRaw:

var query = dbContext.YourEntity
    .FromSqlRaw("SELECT * FROM YourEntityTable WHERE FORMAT(BirthDate, 'MM', 'fa-IR') = '05'")
    .ToList();