Converting a Complex SQL Query to LINQ

Question

Hi can someone help me to convert this query to LINQ, please? All my trials failed so far..

SELECT MAX(CONVERT(INT, SUBSTRING([ItemID], 4, 8)))
  FROM [kalpz].[dbo].[Items]
  where ItemID like 'EXL%'

Sample ItemID s: EXL0001, SLA0001, etc.

Thanks!

Answer 1

var result = from i in items

     where i.ItemId.StartsWith("EXL")

     select new {Convert.ToInt32(i.ItemId.Substring(4)};

Answer 2

Hi ItsMeKapz;

When posting a question please tell us what programming language you are working with unless you specifically post to a thread that is specific to a language.

// C# Codes sample
using System.Data.Linq.SqlClient;
// Create a DataContext to query the Database
var db = new DataClasses1DataContext();

// The Sql query
var valueList = (from tid in db.kalpzs
                 where SqlMethods.Like(tid.ItemID, "EXL%")
                 select Convert.ToInt32(tid.ItemID.Substring(3))).ToList();

// Test to see if any values were returned and process
if( valueList.Count != 0 )
    Console.WriteLine("The Max value is : {0}", valueList.Max());
else
    Console.WriteLine("The query did not find a ItemID in the table");

'Visual Basic .Net Code sample
Imports System.Data.Linq.SqlClient

' Create a DataContext to query the Database
Dim db As New DataClasses1DataContext()

' The Sql query
Dim valueList = (From tid In db.kalpzs
                Where SqlMethods.Like(tid.ItemID, "EXL%")
                Select Convert.ToInt32(tid.ItemID.Substring(3))).ToList()

' Test to see if any values were returned and process
If valueList.Count <> 0 Then
    Console.WriteLine("The Max value is : {0}", valueList.Max())
Else
    Console.WriteLine("The query did not find a ItemID in the table")
End If

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 3

ItsMeKaptz :

You can try this one liner  to get max and do change the below slist accordingly with your context.

 static void Main(string[] args)
        {
            List<string> slist = new List<string>() { "EXL0001", "SLA9001", "xyx2132", "wew2323", "wew1111" };
            Console.WriteLine(slist.AsEnumerable().Select(a => a.Substring(3)).ToList().Max());
        }

Answer 4

If your LINQ Query will finally run in database, there is no reason to do it in LINQ. Final SQL query can become much more complex rather than your original.

If not, Michael L. Wagner's answer will solve your problem.

---

Please mark as reply if helped.
Also visit my blog http://msguy.net/

Answer 5

Hi ItsMeKapz;

Did one of the solutions work out for you?

  

---

Fernando (MCSD)

If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".