How to use sql server ISJSON function in linq query C# EF6 Database first

Question

I need to retrieve data from a table stored in SQL server, this table has json string column, then i need to retrieve only rows with a valid json column using linq in C# like that:

var q = from t1 in DBContext.table1 join  
        t2 in DBContext.table2 on t1.id equals t2.tId
        where 
        //Here what i need to use isJson like that but SqlFunctions does not contain it
        SqlFunctions.IsJson(t1.jsonTxt)
       select new resDTO
        {
          name=t1.name,
          details =t2.details 
        }

Simplest way is to create a view or table-valued function that filters the desired rows and use that as the basis. — Jeroen Mostert, Apr 14 '22 at 13:49
@SvyatoslavDanyliv what is the need of (which database) it is local DB stored in sql server and the code with dummy names just for explanation what i need to do, how these info you asked for will affect the answer? — Asmaa Rashad, Apr 14 '22 at 13:55
@JeroenMostert i need to do that using linq only , as i don't have the authority to change in sql server directly , so i am trying to do using linq only — Asmaa Rashad, Apr 14 '22 at 13:56
Never used EF6, check [EdmFunctionAttribute](https://stackoverflow.com/a/28864689/10646316), looks like it is how old EF works with defining custom functions for LINQ query. — Svyatoslav Danyliv, Apr 14 '22 at 14:53

Jurgen Volders · Answer 1 · 2022-04-15T07:09:19.270

If I understand correctly you just want to check for a valid Json string in your where clause. In that case you can do this I believe.

var q = from t1 in DBContext.table1 join  
    t2 in DBContext.table2 on t1.id equals t2.tId
    let isValid = IsValidJson(t1.jsonTxt)
    where isValid == true
   select new resDTO
    {
      name=t1.name,
      details =t2.details 
    }

   private bool IsValidJson(string strInput)
   {
     if (string.IsNullOrWhiteSpace(strInput)) { return false;}
     strInput = strInput.Trim();
     if ((strInput.StartsWith("{") && strInput.EndsWith("}")) || //For object
         (strInput.StartsWith("[") && strInput.EndsWith("]"))) //For array
      {
        try
        {
           var obj = JToken.Parse(strInput);
           return true;
        }
        catch (Exception)
        {
          return false;
        }
      }
     else
      {
       return false;
      }
   }

The method is from this thread: How to make sure that string is valid JSON using JSON.NET

I already tried this solution but it gives me this error : `LINQ to Entities does not recognize the method 'Boolean IsValidJson(System.string)' method, and this method cannot be translated into a store expression." RemoteStackIndex: 0` — Asmaa Rashad, Apr 14 '22 at 14:41
I edited my answer. You can use let to get around the method — Jurgen Volders, Apr 15 '22 at 07:09

How to use sql server ISJSON function in linq query C# EF6 Database first

1 Answers1