none
LINQ to SQL Conversion Problem from C# to VB.Net RRS feed

  • Question

  • LINQ to SQL Conversion Problem from C# to VB.Net



    I'm having two problems with the following code after I converted it to C#.  One is the following line won't compile:

    data = data.OrderBy(sort)

    Can you tell me what the correct syntax should be.   I'm also finding that when I comment this line I am then receiving this error message:

    This provider supports Skip only over ordered queries returning entities or projections that contain all identity columns, where the query is a single-table (non-join) query, or is a Distinct, Except, Intersect, or Union (not Concat) operation.

    Any ideas how I can clean up this code. Here is the entire code block. Thanks!

     

     Dim data = From l In Me.m_AccountData.AccountList _
                       Select New With _
                              {
                                    .LastName = l.LASTNAME, _
                                    .FirstName = l.FIRSTNAME, _
                                    .MiddleName = l.MIDDLENAME, _
                                    .Suffix = l.SUFFIX, _
                                    .MailCity = l.MailCity, _
                                    .MailState = l.MailState _
                              }

            Select Case category
                Case "City"
                    data = data.Where(Function(f) f.MailCity.StartsWith(search))
                    'was using .Contains(search)
                    Exit Select
                Case "LastName"
                    data = data.Where(Function(f) f.LastName.StartsWith(search))
                    Exit Select
                Case "FirstName"
                    data = data.Where(Function(f) f.FirstName.StartsWith(search))
                    Exit Select
                Case Else
                    Exit Select
            End Select

            'Setup(sorting)
            Dim desc As Boolean = sort.Contains(" DESC")

            If desc Then
                sort = sort.Replace(" DESC", "")
            End If

            If Not desc Then
                If Not String.IsNullOrEmpty(sort) Then
                    data = data.OrderBy(sort)
                Else
                    data = data.OrderBy(Function(f) f.LastName)
                End If
            Else
                If Not String.IsNullOrEmpty(sort) Then
                    data = data.OrderByDescending(sort)
                Else
                    data = data.OrderByDescending(Function(f) f.LastName)
                End If
            End If

            'Setup(paging)
            If maximumRows > 0 Then
                data = data.Skip(startRowIndex)

                data = data.Take(maximumRows)
            End If

            Return data.ToList()
        End Function

    Thursday, October 13, 2011 8:01 PM

Answers

  • Hi AZDev;

    The OrderBy(sort) method takes as its parameter as a Func<TSource, TKey> keySelector and no overload takes a string either in Vb .Net or C# so I don't know how it is working in VB .Net for you. This method iterates through the collection of TSource and orders the collection on a field in that collection called the KeySelector. So for example in your code if you wanted to order the collection in the variable called data in your code on the field named MailCity you would do the following :

    data = data.OrderBy(Function(c) c.MailCity)

    In C# this statement is translated to the following :

    data = data.OrderBy(c => c.MailCity);

    As for your second question, SQL Server 2000 has some limitations when using Skip and Take. Please see the following Microsoft documentation for working with Skip and Take in SQL 2000.

    How to: Return or Skip Elements in a Sequence (LINQ to SQL)

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by AZDev-IOrtiz Tuesday, October 18, 2011 2:58 PM
    Friday, October 14, 2011 2:24 PM

All replies

  • Hi AZDev;

    Can you please post the complete function with the function signature and as well as sample values for sort and category.

    The statement in question, data = data.OrderBy(sort), has the syntax :

    data = data.OrderBy(Func<data, TKey> keySelector)

    Where Func<data, TKey> keySelector can be a lambda expression that takes the form,

    d => d.FieldToSortOn.

     

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, October 13, 2011 9:18 PM
  • Hi Fernando, here is the full class module

    Public Class AccountDirectoryController

        Public Property AccountData() As Acctdb
            Get
                Return m_AccountData
            End Get
            Set(value As Acctdb)
                m_AccountancyData = value
            End Set
        End Property
        Private m_AccountancyData As Acctdb

        Public Sub New()
            Me.AccountData = New Acctdb(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        End Sub
        Public Function GetAccountListCount(search As String, category As String, sort As String, maximumRows As Integer, startRowIndex As Integer) As Integer
            Return TryCast(Me.GetAccountList(search, category, "", -1, 0), ICollection).Count
        End Function

        Public Function GetAccountList(search As String, category As String, sort As String, maximumRows As Integer, startRowIndex As Integer) As IEnumerable

            Dim data = From l In Me.m_AccountData.LicenseeList _
                       Select New With _
                              {
                              
                                    .LastName = l.LASTNAME, _
                                    .FirstName = l.FIRSTNAME, _
                                    .MiddleName = l.MIDDLENAME, _
                                    .Suffix = l.SUFFIX, _
                                    .MailCity = l.MailCity, _
                                    .MailState = l.MailState _
                              }

            Select Case category
                Case "City"
                    data = data.Where(Function(f) f.MailCity.StartsWith(search))
                    'was using .Contains(search)
                    Exit Select
                Case "LastName"
                    data = data.Where(Function(f) f.LastName.StartsWith(search))
                    Exit Select
                Case "FirstName"
                    data = data.Where(Function(f) f.FirstName.StartsWith(search))
                    Exit Select
                Case Else
                    Exit Select
            End Select

            ' Setup sorting
            'Dim desc As Boolean = sort.Contains(" DESC")

            'If desc Then
            'sort = sort.Replace(" DESC", "")
            'End If

            'If Not desc Then
            'If Not String.IsNullOrEmpty(sort) Then
            'data = data.OrderBy(sort)
            ' Else
            ' data = data.OrderBy(Function(f) f.LastName)
            ' End If
            ' Else
            ' If Not String.IsNullOrEmpty(sort) Then
            'data = data.OrderByDescending(sort)
            'Else
            'data = data.OrderByDescending(Function(f) f.LastName)
            'End If
            'End If

            ' Setup paging
            ' If maximumRows > 0 Then
            'data = data.Skip(startRowIndex)

            ' data = data.Take(maximumRows)
            'End If

            Return data.ToList()
        End Function


        Public Function GetCustomerListCount(search As String, category As String, sort As String, maximumRows As Integer, startRowIndex As Integer) As Integer
            Return TryCast(Me.GetCustomerList(search, category, "", -1, 0), ICollection).Count
        End Function

        Public Function GetCustomerList(search As String, category As String, sort As String, maximumRows As Integer, startRowIndex As Integer) As IEnumerable

            Dim data = From f In Me.m_AccountData.CustomerList _
                     Select New With {
                 .Mailcity = f.MAILCITY, _
                 .Mailstate = f.MAILSTATE, _
                 .Name = f.NAME
                      }

            Select Case category
                Case "City"
                    data = data.Where(Function(f) f.Mailcity.StartsWith(search))
                    Exit Select
                Case "Name"
                    data = data.Where(Function(f) f.Name.StartsWith(search))
                    Exit Select
                Case Else
                    Exit Select
            End Select

            ' Setup sorting
            Dim desc As Boolean = sort.Contains(" DESC")

            If desc Then
                sort = sort.Replace(" DESC", "")
            End If

            If Not desc Then
                If Not String.IsNullOrEmpty(sort) Then
                    'TODO: Add this back in
                    'data = data.OrderBy(sort)
                Else
                    data = data.OrderBy(Function(f) f.Name)
                End If
            Else
                If Not String.IsNullOrEmpty(sort) Then
                    'TODO: Add this back in
                    'data = data.OrderByDescending(sort)
                Else
                    data = data.OrderByDescending(Function(f) f.Name)
                End If
            End If

            ' Setup paging
            If maximumRows > 0 Then
                data = data.Skip(startRowIndex)

                data = data.Take(maximumRows)
            End If

            Return data.ToList()
        End Function

    End Class

    Friday, October 14, 2011 12:49 PM
  • Another thing is we are using Visual Studio and SQL 2000 to access the data. I had to create the dbml file via SQLMetal.
    Friday, October 14, 2011 12:50 PM
  •  

    What version of Visual Studio, 2008, 2008 SP1, 2010?

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Friday, October 14, 2011 1:21 PM
  • Visual Studio 2010
    Friday, October 14, 2011 1:43 PM
  • Hi AZDev;

    The OrderBy(sort) method takes as its parameter as a Func<TSource, TKey> keySelector and no overload takes a string either in Vb .Net or C# so I don't know how it is working in VB .Net for you. This method iterates through the collection of TSource and orders the collection on a field in that collection called the KeySelector. So for example in your code if you wanted to order the collection in the variable called data in your code on the field named MailCity you would do the following :

    data = data.OrderBy(Function(c) c.MailCity)

    In C# this statement is translated to the following :

    data = data.OrderBy(c => c.MailCity);

    As for your second question, SQL Server 2000 has some limitations when using Skip and Take. Please see the following Microsoft documentation for working with Skip and Take in SQL 2000.

    How to: Return or Skip Elements in a Sequence (LINQ to SQL)

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by AZDev-IOrtiz Tuesday, October 18, 2011 2:58 PM
    Friday, October 14, 2011 2:24 PM