0

This is my code:

var icdCodes = DbContext.MasterIcd.Select(x => x.IcdCode).AsQueryable();
var count = icdCodes.Where(x => !x.Any(char.IsDigit)).Count();

I'm trying to find those IcdCodes which doesn't contain any characters. But using count throws the following error: “Internal .NET Framework Data Provider error 1025.”

As mentioned in Internal .NET Framework Data Provider error 1025 I'm using AsQuerable() but still getting the error. Please help

Prashant Yadav
  • 531
  • 2
  • 9
  • 25
  • I bet it is not mapping '!x.Any(char.IsDigit)' to SQL. Try replacing it with something else, e.g. 'x.Length > 0' – mikelegg Oct 08 '19 at 11:17

2 Answers2

6

The AsQueryable() does not solve this other cause of the same error. As also explained in Casting LINQ expression throws "Internal .NET Framework Data Provider error 1025.", the problem is that Where(x => !x.Any(char.IsDigit)) can't be translated to SQL.

The C# code you use treats a string as a char array and calls a function that uses a Unicode lookup table to check if each character is a digit.

The T-SQL variant of this is ISNUMERIC. See How to know if a field is numeric in Linq To SQL:

DbContext.MasterIcd
         .Select(x => x.IcdCode)
         .Where(i => SqlFunctions.IsNumeric(i) == 1)
         .ToList();
CodeCaster
  • 147,647
  • 23
  • 218
  • 272
  • 2
    Be wary about using `ISNUMERIC`. It's extremely lax about what is considered "numeric", including such things as `$`, `-`, `.` and `10,3`. It's comparable to checking if every character is a digit, but certainly not the same thing -- `SqlFunctions.PatIndex("%[^0-9]%", i) == 0` comes closer. – Jeroen Mostert Oct 08 '19 at 11:20
  • @Jeroen agreed. That "laxness" of course has to do with [money](https://learn.microsoft.com/en-us/sql/t-sql/data-types/money-and-smallmoney-transact-sql?view=sql-server-2017), decimals and thousands separators. If they really want just 0-9, a regex could be a better option. That too differs from `char.IsNumeric` though, as that yields true for about 200-300 characters. – CodeCaster Oct 08 '19 at 11:24
  • A regex works, but that crucially must run on the client side of things, as T-SQL doesn't support those. If that's an option, you can of course materialize the results explicitly (`.ToList()`) and go crazy, with added overhead on the client side. Even if you did want "anything that's convertible to a number", `TRY_CONVERT` is a better choice, but that unfortunately doesn't appear to be exposed through `SqlFunctions`. – Jeroen Mostert Oct 08 '19 at 11:29
  • @JeroenMostert Agreed. I'm using DbContext.MasterIcd .Select(x => x.IcdCode) .Where(i => SqlFunctions.PatIndex("%[0-9]%", i) == 0) .ToList(); – Prashant Yadav Oct 08 '19 at 11:29
  • @Jeroen sorry, that was an oversimplification, PATINDEX of course isn't a regular expression, but, as the name implies, a pattern expression - so I meant to agree that the solution in your comment is a better match for the OP's code. – CodeCaster Oct 08 '19 at 11:30
0

Not every Request is translatable into SQL. Splitting a string into an character array and doing array-options on it, might be one of them.

Approach A. You load all you strings im Memory (replacing AsQueryable with ToList()) And do your Test locally. (your code is OK).

Approach B. If your IcdCode is of limited length (maximum 9), it could be simpler, just to TryParse it to int or long, disallowing signs if you want. With this Type-Conversion approach there could be also a solution that can be expressed in SQL. (Like if conversion is possible than ...). But it sometimes hard to find out how to express something in C#, that can be converted to SQL, this is what actually is done when executing LINQ to SQL, and it still depends on the type of SQL-Server itself.

Holger
  • 2,446
  • 1
  • 14
  • 13