LINQ adding mystery "+1" to my int's in generated SQL

I have to admit, I am just plan stumped....I have the below LINQ to SQL Method -->

    public static int GetLastInvoiceNumber(int empNumber)
    {
        using (var context = CmoDataContext.Create())
        {
            context.Log = Console.Out;

            IQueryable<tblGreenSheet> tGreenSheet = context.GetTable<tblGreenSheet>();
            return (tGreenSheet
                            .Where(gs => gs.InvoiceNumber.Substring(3, 4) == empNumber.ToString())
                            .Max(gs => Convert.ToInt32(开发者_JAVA技巧gs.InvoiceNumber.Substring(7, gs.InvoiceNumber.Length)))
                            );
        }
    }

This was made based from a SQL query written by a co-worker to achieve nearly the same thing -->

SELECT DISTINCT 
SUBSTRING([InvoiceNumber], 1, 6) AS EmpNumber,
MAX(CAST(SUBSTRING([InvoiceNumber], 7, LEN([InvoiceNumber])) AS INT)) AS MaxInc
FROM [CMO].[dbo].[tblGreenSheet]
WHERE SUBSTRING([InvoiceNumber], 3, 4) = '1119' --EmployeeNumber
GROUP BY SUBSTRING([InvoiceNumber], 1, 6)

However, the SQL that is being generated, when I check through context.Log = Console.Out is this-->

SELECT MAX([t1].[value]) AS [value]
FROM (
    SELECT CONVERT(Int,SUBSTRING([t0].[InvoiceNumber], @p0 + 1, LEN([t0].[InvoiceNumber]))) AS [value], [t0].[InvoiceNumber]
    FROM [dbo].[tblGreenSheet] AS [t0]
    ) AS [t1]
WHERE SUBSTRING([t1].[InvoiceNumber], @p1 + 1, @p2) = @p3
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [7]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [3]
-- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [4]
-- @p3: Input VarChar (Size = 4; Prec = 0; Scale = 0) [1119]

You can see that it is actually fairly close with the GLARING exception of some added +1's!!!

WTH?!?

I even verified that it WOULD be correct by removing the +1's in the generated SQL and running it with it generating the same results as the original SQL.

So, what am I missing or doing wrong? Is this a well know LOL of LINQ's to screw with us less talented programmers?

SQL Server's SUBSTRING uses 1-based indexing, whereas string.Substring uses zero-based indexing. The + 1 maps between the bases to preserve the C# semantics.

As to why you had to remove the + 1 to make it work correctly, that's a mystery to me.

@MarceloCantos answer is correct, but here is the explanation as to why it works when you remove the + 1:

You have converted SUBSTRING([InvoiceNumber], 3, 4) to gs.InvoiceNumber.Substring(3, 4) but since C# uses zero-based index you should actually use gs.InvoiceNumber.Substring(2, 4) to start at the third character.

Similarly you should use gs.InvoiceNumber.Substring(6) as the substitution for SUBSTRING([InvoiceNumber], 7, LEN([InvoiceNumber])). Note that there is no need to specify the length of the substring in C# if you want all that is after the index. Actually gs.InvoiceNumber.Substring(7, gs.InvoiceNumber.Length) would cause an ArgumentOutOfRangeException if you tried to use it separately, but now that it is translated to T-SQL it actually works.

On a side note SUBSTRING([InvoiceNumber], 1, 6) AS EmpNumber seems to indicate that it the first six characters are the EmployeeNumber but you are only looking at the last four of them. Presumably because you only have < 10000 Employees at the moment, but this might come back to bite you later.

I would suggest you break out the methods above and give them proper names, to enhance readability. E.g. getEmployeeNumber(string invoiceNumber), etc.