none
From SQL syntax into LINQ RRS feed

  • Question

  • I found this SQL statement that will return the longest string for a specific column from a specific table.

    SELECT TOP1 Address1 - Column Name
    FROM tblCustomers - Table Name
    ORDER BY LEN(Address1) DESC

    The Results:

    Can someone show me how to convert this to LINQ?

    Is there a tutorial out there that shows how a LINQ query is built?

    Thanks in advance,


    MRM256

    Friday, October 19, 2018 6:05 PM

All replies

  • Here is an example using Entity Framework (LINQ/Lambda)

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Using context As New NorthWindAzure1Entities
                Dim longestName = context.Customers.
                    Select(Function(cust) cust.CompanyName).
                    OrderByDescending(Function(name) name.Length).
                    FirstOrDefault
    
                Console.WriteLine(longestName)
            End Using
        End Sub
    End Class

    TSQL

    SELECT TOP 1 CompanyName
    FROM NorthWindAzure1.dbo.Customers
    ORDER BY LEN(dbo.Customers.CompanyName) DESC;

    Both return the same company name

    Then w/o a database

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim names = New List(Of String) From {"Amy", "Anastasia", "Mary"}
        Console.WriteLine(names.OrderByDescending(Function(s) s.Length).FirstOrDefault)
    End Sub

    Or include the index too

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim names = New List(Of String) From {"Amy", "Anastasia", "Mary"}
        Dim result = names.
            Select(Function(name, index) New With {.Name = name, .Index = index}).
            OrderByDescending(Function(s) s.Name.Length).
            FirstOrDefault
    
        Console.WriteLine($"{result.Index} - {result.Name}")
    End Sub


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites




    Friday, October 19, 2018 6:43 PM
    Moderator
  • Hi Karen,

    This isn't what I need. 

    In order to build the dynamic forms I have to find how long the data is in each column and have the application create a text box of the appropriate length. The SQL string was inside a Function that returned the string itself. 

    The returned string was placed into another function that to determine the width of the text box.

     Private Function Det_T_Box_Width(ByVal ctrl As TextBox, _
                                      ByVal s As String) _
                                      As Long
        Dim g As Graphics = ctrl.CreateGraphics
        ctrl.Text = s
        ctrl.Width = (g.MeasureString(ctrl.Text, _
                                      ctrl.Font).Width) + 15
        'Debug.Print("DetTextWidth by Record:" & _
        'ctrl.Width.ToString)
        Return ctrl.Width
    End Function

    I don't know if this helps, but here is the Function for returning the longest string in the column for the specific database table.

     Private Function Longest_Str(ByVal strColName As String, _
                                    ByVal dt_Name As String) _
                                    As String
        'Purpose:       Finds the length of the longest string 
        '               in the database column.
        'Parameters:    strColName As String - The name of the column
        '                                      we are searching.
        '               dt_Name As String - The name of the database table 
        '                                   we are working with
        'Returns:       The Longest string in the given column  
            Dim Qry = (From col In dt_Name Where Len(col))
         Return Qry
    End Function

    Does this help any?

    Thanks,


    MRM256

    Friday, October 19, 2018 9:42 PM
  • Hi Karen,

    This isn't what I need. 

    In order to build the dynamic forms I have to find how long the data is in each column and have the application create a text box of the appropriate length. The SQL string was inside a Function that returned the string itself. 

    The returned string was placed into another function that to determine the width of the text box.

     Private Function Det_T_Box_Width(ByVal ctrl As TextBox, _
                                      ByVal s As String) _
                                      As Long
        Dim g As Graphics = ctrl.CreateGraphics
        ctrl.Text = s
        ctrl.Width = (g.MeasureString(ctrl.Text, _
                                      ctrl.Font).Width) + 15
        'Debug.Print("DetTextWidth by Record:" & _
        'ctrl.Width.ToString)
        Return ctrl.Width
    End Function

    I don't know if this helps, but here is the Function for returning the longest string in the column for the specific database table.

     Private Function Longest_Str(ByVal strColName As String, _
                                    ByVal dt_Name As String) _
                                    As String
        'Purpose:       Finds the length of the longest string 
        '               in the database column.
        'Parameters:    strColName As String - The name of the column
        '                                      we are searching.
        '               dt_Name As String - The name of the database table 
        '                                   we are working with
        'Returns:       The Longest string in the given column  
            Dim Qry = (From col In dt_Name Where Len(col))
         Return Qry
    End Function

    Does this help any?

    Thanks,


    MRM256

    All I can say is, you should have included this information in your initial question which I did answer. I have zero time to allot to this.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, October 19, 2018 10:13 PM
    Moderator
  • I'm sorry. I thought this problem was just a difference in syntax.

    The SQL one was SELECT TOP 1 <columnName> FROM <database table> ORDER BY LEN(<columnname>] in DESC order. Pretty straight forward.

    LINQ starts with FROM <Variable> IN<DataSource> SELECT MAX(LEN(<columnName>)).

    I sure miss the old days where you had reference books that explained how to use specific features.

    Frustrated,


    MRM256

    Saturday, October 20, 2018 12:23 AM
  • I'm sorry. I thought this problem was just a difference in syntax.

    The SQL one was SELECT TOP 1 <columnName> FROM <database table> ORDER BY LEN(<columnname>] in DESC order. Pretty straight forward.

    LINQ starts with FROM <Variable> IN<DataSource> SELECT MAX(LEN(<columnName>)).

    I sure miss the old days where you had reference books that explained how to use specific features.

    Frustrated,


    MRM256

    I don't care for LINQ but this is how you do LINQ

    ublic Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim names = New List(Of String) From {"Amy", "Anastasia", "Mary"}
            Dim result =
                    (
                        From theName In names
                        Select theName Order By theName.Length Descending
                    ).FirstOrDefault()
    
            Console.WriteLine(result)
        End Sub
    End Class
    
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, October 20, 2018 12:38 AM
    Moderator