Member Avatar for Ggalla1779

Hi guys

I need to find next highest no in a field that is a stick ref it has chars at front and 0 padded

How so I strip off front chars and 0's to get actual highest no number in the table field?

Am using LINQ against entity framework

cheers

George

  • 2 Contributors
  • 4 Replies
  • 238 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by thines01

Dani AI

Generated

If the prefix (company number) is a fixed length and the sequence is a fixed-width, zero-padded number (exactly your sample: company 5660303 + a 5-digit sequence), do the work on the database with LINQ-to-Entities. Because the numeric part is zero-padded, a string Max on the substring will return the numerically highest value — no client-side scan needed. As suggested, extract the suffix with Substring and call Max; below is a concise C# pattern for EF and a couple of caveats.

string prefix = "5660303";
int suffixLen = 5;

var maxSuffixStr = context.MyTable
    .Where(r => r.StickRef.StartsWith(prefix))
    .Select(r => r.StickRef.Substring(prefix.Length, suffixLen))
    .Max();

int maxSuffix = int.Parse(maxSuffixStr);            // parsing happens client-side
string nextSuffix = (maxSuffix + 1).ToString().PadLeft(suffixLen, '0');
string nextRef = prefix + nextSuffix;

Notes and alternatives:

  • Substring, StartsWith and Max are translated to SQL by EF; int.Parse is not — so only the Max string is fetched, then parsed locally. Always apply the Where(prefix) filter first to minimize rows sent.
  • If suffix width or zero-padding is not reliable, extract the candidates to the client and parse the numeric tail (Regex) before taking Max. This is simpler but can be costly on large tables; always filter by prefix in SQL before calling AsEnumerable().
  • For many prefixes or very large tables, consider a grouped query (GroupBy substring(0,prefixLen).Max(...)) or adding a computed/integer column for the sequence so the DB can index and aggregate efficiently.

Recommended Answers

All 4 Replies

Member Avatar for thines01

Do you have an example of the data or even the code you're tried?

Member Avatar for Ggalla1779

Sample of the data would be company number (5660303) plus no so

566030300001
566030300002
566030300003
566030300004

Not tried code as I was a bit stuck with LINQ not used it apart from basic queries

Member Avatar for thines01

Technique 1:

Imports System.Collections.Generic
Imports System.Linq

Module Module1
   Sub Main()
      Dim lst_strNums As New List(Of String) From _
         {"566030300001", "566030300002", "566030300003", "566030300004"}

      Dim strMax As String = lst_strNums.Select(Function(s) s.Substring(7, 5)).Max()

      Console.WriteLine(strMax)
   End Sub

End Module
Member Avatar for thines01

Technique1 + Technique2:

Imports System.Collections.Generic
Imports System.Linq

Module Module1
   Function Max1(ByVal lst_str As List(Of String)) As String
      Return lst_str.Select(Function(s) s.Substring(7, 5)).Max()
   End Function
   Function Max2(ByVal lst_str As List(Of String)) As String
      Return lst_str.Select(Function(s) s).Max()
   End Function

   Sub Main()
      Dim lst_strNums As New List(Of String) From _
         {"566030300001", "566030300002", "566030300003", "566030300004"}

      Console.WriteLine(Integer.Parse(Max1(lst_strNums)))
      Console.WriteLine(Integer.Parse(Max2(lst_strNums).Substring(7, 5)))
   End Sub

End Module
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.