TSQL query CAST to int + 1 to LINQ

Question

I'm cracking my brains about the following linq to sql query. The idea is to get the smallest next integer number, compared to all REG_CODE items. This field (REG_CODE) is an varchar(10) field. I am trying to convert the following tsql to linq-to-entities (EF 6.0):

SELECT TOP 1 CAST( [Extent1].[REG_CODE] AS int) + 1 
    FROM [dbo].[Notifications] [Extent1]
WHERE NOT ([Extent1].[REG_CODE] LIKE N'%~[a-z]%' ESCAPE N'~') AND (1 = (ISNUMERIC([Extent1].[REG_CODE]))) AND
      NOT EXISTS(SELECT * FROM [dbo].[Notifications] t2 WHERE CAST( t2.REG_CODE AS int) = CAST( [Extent1].[REG_CODE] AS int) + 1 )
ORDER BY [Extent1].[REG_CODE]

(take note of the + 1 's, I want the next part) The design is not really that awsome. The field [REG_CODE] should have been an integer field, bud it's not and will not be anytime soon.

This:

float notificationMaxRegCodeNumeric =
    db.Notifications.Where(not => not.Reg_Code != null && !not.Reg_Code.Contains("[a-z]") && SqlFunctions.IsNumeric(not.Reg_Code) == 1)
        .OrderByDescending(not => not.Reg_Code)
        .Select(not => not.Reg_Code)
        .Cast<int>()
        .Max();

Converts successfully into:

SELECT MAX(CAST( [Extent1].[REG_CODE] AS int)) AS[A1]
FROM[dbo].[Notifications] AS[Extent1]
WHERE ([Extent1].[REG_CODE] IS NOT NULL) AND(NOT([Extent1].[REG_CODE] LIKE N'%~[a-z]%' ESCAPE N'~')) AND(1 = (ISNUMERIC([Extent1].[REG_CODE])))

So far I've got:

int nextNotificationMaxRegCodeNumericInt = db.Notifications.Where(not =>
    not.Reg_Code != null && !not.Reg_Code.Contains("[a-z]") &&
    SqlFunctions.IsNumeric(not.Reg_Code) == 1 &&
    db.Notifications.Any(klainternal => not.Reg_Code.Cast<int>() == klainternal.Reg_Code.Cast<int>())
    )
.OrderByDescending(not => not.Reg_Code)
.Select(not => not.Reg_Code)
.Cast<int>();

but it throws:

DbExpressionBinding requires an input expression with a collection ResultType`.

Also Convert.ToInt32() is throwing:

linq to entities does not recognize the method 'int32 toint32(system.string)' method`

(the .Max() is not really relevant in what I am looking for, bud it's in the working part of the query)

Any suggestions?

I don't think there would be a need to cast the field to an integer with c# since it should already be defined as such, no? — user1666620, Nov 16 '18 at 14:52
`Cast` is a Linq method that will cast all the values in a collection, not a single value (and I doubt it translates to SQL even if used properly). You'd need a method that's setup to translate to the desired SQL. I don't believe there's anything built in but check out this question about how to create your own https://stackoverflow.com/questions/5971521/linq-to-entities-does-not-recognize-the-method-double-parsesystem-string-met — juharr, Nov 16 '18 at 14:58
@user1666620 the field REG_CODE is an varchar(10) field, so i need the smallest next integer number, compared to all REG_CODE items — BramscoChill, Nov 16 '18 at 15:03
Isn't the SQL dangerous with `ORDER BY REG_CODE` when you are trying to do numeric order, or does `REG_CODE` contain leading zeroes? — NetMage, Nov 16 '18 at 19:53
Can you show the code using `Convert.ToInt32`? That is the proper method for LINQ to Entities - what version of EF are you using? Are you using EF Core? — NetMage, Nov 16 '18 at 20:00

Ivan Stoev · Accepted Answer · 2018-11-18T11:27:49.193

First, congratulations for finding the Cast<T>() trick! It seems to be the only out of the box EF6 way of casting string to something else - all other attempts like (int)(object)stringValue or Convert.ToInt32(stringValue) are simply blocked as not supported.

But note that the Cast<T>() method is defined for IEnumerable and IQueryable and the result is respectively IEnumerable<T> and IQueryable<T>, i.e. works on sequence and produces sequence. It appears on string because string is IEnumerable<char>, thus IEnumerable, but it's not what we need.

So the trick is to always use projection (Select) + Cast to do the conversions. Applying it to your query leads to something like this:

int nextNotificationMaxRegCodeNumericInt = db.Notifications
    .Where(n => n.Reg_Code != null &&
        !n.Reg_Code.Contains("[a-z]") &&
        SqlFunctions.IsNumeric(n.Reg_Code) == 1)
    .Select(n => n.Reg_Code).Cast<int>() // <--
    .Select(reg_Code => reg_Code + 1)
    .Where(reg_Code => !db.Notifications.Select(n => n.Reg_Code).Cast<int>() // <--
        .Contains(reg_Code))
    .OrderByDescending(reg_Code => reg_Code)
    .FirstOrDefault();

which converts to

SELECT TOP (1)
    [Project1].[C1] AS [C1]
    FROM ( SELECT
         CAST( [Extent1].[Reg_Code] AS int) + 1 AS [C1]
        FROM [dbo].[Notifications] AS [Extent1]
        WHERE ([Extent1].[Reg_Code] IS NOT NULL) AND ( NOT ([Extent1].[Reg_Code] LIKE N'%~[a-z]%' ESCAPE N'~')) AND (1 = (ISNUMERIC([Extent1].[Reg_Code])))
    )  AS [Project1]
    WHERE  NOT EXISTS (SELECT
        1 AS [C1]
        FROM [dbo].[Notifications] AS [Extent2]
        WHERE  CAST( [Extent2].[Reg_Code] AS int) = [Project1].[C1]
    )
    ORDER BY [Project1].[C1] DESC

That's what I was looking for! One more thing is the: ORDER BY [Project1].[C1] DESC, this has to be without the DESC, then it get's the next non existing largest REG_CODE. Thanks! — BramscoChill, Nov 19 '18 at 08:26

TSQL query CAST to int + 1 to LINQ

1 Answers1