2

I have an order with specific data. Orders stores in dbo.Orders table which have SpecificData field (nvarchar(max)) for json-data. I use EF Code first. For JSON_VALUE I wrote DBFunction translation using this answer (https://stackoverflow.com/a/50490674/1411598) but I faced with a problem when I tried to add WHERE condition with INT type.

if (specificFilter.RegionIds != null && specificFilter.RegionIds.Count() > 0)
{
   query = query.Where(g => specificFilter.RegionIds.Contains(DbFunc.JsonValue(g.specificData, "$.RegionId")));
}

Because JsonValue function return only string I cannot add clause with "contains". Think a little bit I try to write convert UDF function and translate to EF object. Here is my code:

if (specificFilter.RegionIds != null && specificFilter.RegionIds.Count() > 0)
{
    query = query.Where(g => specificFilter.RegionIds.Contains(DbFunc.ConvertToInt(DbFunc.JsonValue(g.specificData, "$.RegionId"))));
}

UDF is very simple

CREATE FUNCTION [dbo].[ConvertToInt] (@value nvarchar(100))
returns int
AS
BEGIN 
    return convert(INT, @value)
END

It's works fine, but query is too slow:

WHERE (0 = [Extent1].[Status]) 
            AND ([dbo].[ConvertToInt](JSON_VALUE([Extent1].[specificData], N''$.RegionId'')) IN (1))

It's bad because if I have a lot of rows query works very slow (UDF runs for each row)

So, can you help me to find the solution? I want to finally get such query:

 WHERE (0 = [Extent1].[Status]) 
            AND (CAST(JSON_VALUE([Extent1].[specificData], N''$.RegionId'') as int) IN (1))

By the other words can I translate CAST or CONVER function in EF DBFunction? Or may be there are some other ways?

Denis
  • 519
  • 2
  • 5
  • 12
  • In EntityFrameworkCore convertion in WHERE clause works fine! https://entityframeworkcore.com/ru/knowledge-base/52017204/ but in EntityFramework has SqlException – Denis May 31 '19 at 09:01
  • Hi, did you find solution for this? I am also having same exact issue. Please let me know the approach you have taken to solve this. – sam Jul 02 '19 at 03:59
  • Hi. I have no idea how to solve this problem. Nobody can help me. So, I decided to work with native SQL. I wrote a simple script builder which prepare select query with JSON_VALUE function in WHERE clause and run it over Context.Database.SqlQuery(sql, parameters). It turned out it works very fast if you prepare some indexes. – Denis Jul 05 '19 at 08:46
  • Thank you Denis for the response. Will give it a shot. Thank you once again. – sam Jul 08 '19 at 04:43

0 Answers0