[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 448
  • Last Modified:

Need Better Way of using Linq-to-SQL to bind to datagrid

Dear Experts,

I've been working on trying to make this work for two days. Most of my frustration lies in the fact that I can't take a single function and have it just return the different queries necessary to do my searches.

But what really gets me is that in Linq (I am just starting to learn Linq) it seems like I have to define every single possible query and actually hardcode it in. I have found that I can do an order by  instead of having to make one query for sorting ascending and one query sorting descending, but even still if I want to sort by different columns I gotta write out a select statement for each one.

I tried to make a function return an "anonymous" type but couldn't figure out what kind of object to return or how to return it, because I wanted to have a function that just returns the basic query for each of the different searches. (Address, Parcel, TrusteeSaleNum, & Zip). Then I wanted to modify said query to sort by a certain column which I would like a way to do without writing each one out in a select statement.

If you look at the code below you'll see a function called PerformSearch_Address(). Reading through it you can see that it is extremely inefficient and extremely long, and this is all due to the necessity of hard coding every query.
I have to write three more versions of this same function, one for each of the 4 basic searches I'm currently doing.

I'm actually going to have many more searches and I'm going to have many at run-time filters and such the user can apply to his searches to find the data he needs on our site.

So, I suppose I could put in the dynamic class I found in one of the 101 VB Samples, but if you look at it, it is an extremely long class and would actually take many more lines of code than even I'm doing it. Although, if this is the best answer, I will try using the dynamic querying class.

I considered trying to have it return a datatable or some object I could work with, but am not sure how to do so, but that might actually be a good solution. I couldn't find any documentation or method on the linq to sql classes that use a context from a dbml, however, that could turn a query into a datatable. It just seems odd to me that the way this linq querying is written is in such a way that you can't pass the data between functions or convert it to basic data that you can bind to datagrids and other objects, and nor can you pass in a parameter such as the name of the field you wish to use in your query.

Does anyone know any good ways around these limitations that don't require huge classes to make "dynamic" queries, or a good, easy way to convert a list or something from an "anonymous" query into a solid typed object that can be used and sorted/bound to a datagrid?

Thanks!
~Jeffrey

Here's my current working (but woefully inefficient) implementation:

Private Enum msSearchTypes
        Address
        Parcel
        TrusteeSaleNum
        ZipCode
    End Enum

    Private Enum dgSortOptions
        Ascending
        Descending
    End Enum

    'Very simple class that just holds the user's sort preferences for the datagrid and the information needed to requery it
    'I've decided not to store the datagrid in the Session ~Jeffrey 9/11/2012
    Private Class dgSessionSortInfo
        Private m_SearchValue As String
        Private m_searchType As msSearchTypes
        Private m_SortExpression As String
        Private m_SortDir As dgSortOptions

        Public Sub New(ByVal searchValue As String, ByVal searchType As msSearchTypes, ByVal sSortExpression As String, ByVal SortDir As dgSortOptions)
            m_SearchValue = searchValue
            m_searchType = searchType
            m_SortExpression = sSortExpression
            m_SortDir = SortDir
        End Sub

        Public ReadOnly Property SearchValue As String
            Get
                Return m_SearchValue
            End Get
        End Property
        Public ReadOnly Property SearchType As msSearchTypes
            Get
                Return m_searchType
            End Get
        End Property
        Public ReadOnly Property SortExpression As String
            Get
                Return m_SortExpression
            End Get
        End Property
        Public Property SortDir As dgSortOptions
            Get
                Return m_SortDir
            End Get
            Set(ByVal value As dgSortOptions)
                m_SortDir = value
            End Set
        End Property
    End Class

Protected Sub dgResults_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles dgResults.Sorting
        'User has clicked a column to sort it

        'Get the current sorting preferences out of the session object
        Dim dgSessInfo As dgSessionSortInfo = Session("DGSessionSortInfo")

        'If it's the same column we should flip the sort direction, otherwise start ascending
        If dgSessInfo.SortExpression = e.SortExpression Then
            dgSessInfo.SortDir = IIf(dgSessInfo.SortDir = dgSortOptions.Ascending, dgSortOptions.Descending, dgSortOptions.Ascending)
        Else
            dgSessInfo.SortDir = dgSortOptions.Ascending
        End If

        'Perform the search with the new sort
        PerformSearch(dgSessInfo.SearchValue, dgSessInfo.SearchType, e.SortExpression, dgSessInfo.SortDir)
    End Sub

#End Region

#Region "Perform Search"

    Private Sub PerformSearch(ByVal sSearchValue As String, ByVal searchType As msSearchTypes, ByVal sSortExpression As String, ByVal SortDir As dgSortOptions)
        'Performs a search based on any criteria
        Dim iResultsCount As Integer

        'Save this in our Session so that when the page reloads or user selects a column we can update our search
        Dim dgSessInfo As New dgSessionSortInfo(sSearchValue, searchType, sSortExpression, SortDir)
        Session("DGSessionSortInfo") = dgSessInfo

        'Set the data to the grid
        Select Case searchType
            Case msSearchTypes.Address
                iResultsCount = PerformSearch_Address(sSearchValue, sSortExpression, SortDir)
            Case msSearchTypes.Parcel
                'iResultsCount = PerformSearch_Parcel(sSearchValue)
            Case msSearchTypes.TrusteeSaleNum
                'iResultsCount = PerformSearch_TrusteeSaleNum(sSearchValue)
            Case msSearchTypes.ZipCode
                'iResultsCount = PerformSearch_Zip(sSearchValue)
        End Select

        'Show/Hide the divs depending on whether or not we got results
        Me.divDgResults.Visible = (iResultsCount > 0)
        Me.divNoResults.Visible = (iResultsCount = 0)
    End Sub

    Private Function PerformSearch_Address(ByVal sSearchValue As String, ByVal sSortExpression As String, ByVal SortDir As dgSortOptions) As Integer
        'Performs an address-based search
        Dim context As PostponementsDataDataContext
        Try
            context = New PostponementsDataDataContext
            Dim query = From myRow In context.Main_Site_Datas _
                Where myRow.PropertyAddress Like "*" & sSearchValue & "*" _
                Select myRow.TrusteeSaleNum, myRow.PropertyAddress, myRow.PropertyZip, _
                myRow.Trustee _
                Take 500

            'Set the column and the sort direction
            Select sSortExpression
                Case "TrusteeSaleNum"
                    If SortDir = dgSortOptions.Ascending Then
                        query = query.OrderBy(Function(s) s.TrusteeSaleNum)  'an inline function that just returns each element, which the orderBy func sorts
                    Else
                        query = query.OrderByDescending(Function(s) s.TrusteeSaleNum)
                    End If
                Case "PropertyAddress"
                    If SortDir = dgSortOptions.Ascending Then
                        query = query.OrderBy(Function(s) s.PropertyAddress)  'an inline function that just returns each element, which the orderBy func sorts
                    Else
                        query = query.OrderByDescending(Function(s) s.PropertyAddress)
                    End If
                Case "PropertyZip"
                    If SortDir = dgSortOptions.Ascending Then
                        query = query.OrderBy(Function(s) s.PropertyZip)  'an inline function that just returns each element, which the orderBy func sorts
                    Else
                        query = query.OrderByDescending(Function(s) s.PropertyZip)
                    End If
                Case "Trustee"
                    If SortDir = dgSortOptions.Ascending Then
                        query = query.OrderBy(Function(s) s.Trustee)  'an inline function that just returns each element, which the orderBy func sorts
                    Else
                        query = query.OrderByDescending(Function(s) s.Trustee)
                    End If
            End Select

            Me.dgResults.DataSource = query.ToList()
            Me.dgResults.DataBind()
            Return query.Count()
Finally
            If context IsNot Nothing Then context.Dispose()
        End Try
    End Function

Open in new window

0
JeffreyDurham
Asked:
JeffreyDurham
  • 4
  • 3
1 Solution
 
Bob LearnedCommented:
I am not sure that I completely understand, but this sounds like a job for LINQ composition.

Composable LINQ to SQL query with dynamic OrderBy
http://blogs.msdn.com/b/swiss_dpe_team/archive/2008/06/05/composable-linq-to-sql-query-with-dynamic-orderby.aspx

Example:

var query = from ad in carfinderDB.ExpandedAds 
            select ad;

if (!string.IsNullOrEmpty(name)) query = query.Where(ad => ad.Name.StartsWith(name));

if (!string.IsNullOrEmpty(carModelName)) query = query.Where(ad => ad.Name.StartsWith(carModelName));

if (minPrice !=null) query = query.Where(ad => ad.Price >= (minPrice));

if (maxPrice != null) query = query.Where(ad => ad.Price <= (maxPrice));

if (firstRegistration != null) query = query.Where(ad => ad.FirstRegistration >= firstRegistration);

string command = carfinderDB.GetCommand(query).CommandText;

// query is composed and executed at this point 
return query.ToList();

Open in new window

0
 
Bob LearnedCommented:
VB.NET translation:

Dim query = 

If Not String.IsNullOrEmpty(name) Then
	query = query.Where(Function(ad) ad.Name.StartsWith(name))
End If

If Not String.IsNullOrEmpty(carModelName) Then
	query = query.Where(Function(ad) ad.Name.StartsWith(carModelName))
End If

If minPrice IsNot Nothing Then
	query = query.Where(Function(ad) ad.Price >= (minPrice))
End If

If maxPrice IsNot Nothing Then
	query = query.Where(Function(ad) ad.Price <= (maxPrice))
End If

If firstRegistration IsNot Nothing Then
	query = query.Where(Function(ad) ad.FirstRegistration >= firstRegistration)
End If

Dim command As String = carfinderDB.GetCommand(query).CommandText

' query is composed and executed at this point 
Return query.ToList()

'=======================================================
'Service provided by Telerik (www.telerik.com)
'Conversion powered by NRefactory.
'Twitter: @telerik, @toddanglin
'Facebook: facebook.com/telerik
'=======================================================

Open in new window

0
 
JeffreyDurhamAuthor Commented:
TheLearnedOne,

I see in line 26 you say Return query.ToList(). what kind of object are you returning? Also, is there a way to convert a ToList() or something like that to a datatable? I know if I can get the data into a datatable I can sort it or filter it or do anything I want with it really from there.

Shortly, I will check out that link and also see if ad.Name is actually a way to refer to a field dynamically using linq or if that is just a field in your sql table called "Name". I will also do some reading and learn what LINQ Composition is, exactly.

Mainly, what I'm looking for is a way to design a query where I can change the actual fields it sorts by and especially which fields it searches by, without having to write out a different LINQ query for each individual search, or an if statement for every field I want to sort descending or ascending by.

Perhaps, a single function which builds a query for every search and then returns the data in a format I can bind to the datagrid.

It may be that the best way to do this is truly the dynamic sql class that comes in the vb 101 examples (from Microsoft, I think..). I was kinda hoping there were ways builtin to the LINQ that let you work with fields without them being actually hardcoded into the query, such as in vba where you can take a recordset and go me.recordset.fields("name of field"), something like that. Or ways to take the anonymous query, and execute it, and turn it into a datatable or something that I can pass between functions and work with.

Just trying to find the best way.

Thanks! ~Jeffrey
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
JeffreyDurhamAuthor Commented:
Ok I'm looking over the link you sent me and that's pretty impressive. I really like how there's a function in there for the dynamic order by. Now I just gotta figure out what it does exactly and I'll be good to go. Also I like how the query is built piece by piece depending on the criteria, that is also very good.

I was still wondering though, what kind of object is the function actually using when it returns the query.toList() so I know how to set up my function definition? I have had the worst of luck trying to pass these "anonymous" queries out into a usable form that can be bound to a datagrid.

Thanks! ~Jeffrey
0
 
JeffreyDurhamAuthor Commented:
Sweet, that awesome little "extension" to give me another syntax on the OrderBy is without doubt, utterly amazing and wonderful! And, based on that, I can see that I can return my query as an IQueryable(Of TEntity). That is just the coolest thing ever!

Below is what I ended up with on that in case it helps anyone.

Thanks TheLearnedOne! That was everything I needed to know.

~Jeffrey

Imports Microsoft.VisualBasic
Imports System.Linq.Expressions

Public Module DynamicOrderBy

    <System.Runtime.CompilerServices.Extension()> _
    Public Function OrderBy(Of TEntity As Class)(ByVal source As IQueryable(Of TEntity), ByVal orderByProperty As String, ByVal desc As Boolean) As IQueryable(Of TEntity)
        Dim command As String = If(desc, "OrderByDescending", "OrderBy")
        Dim type = GetType(TEntity)
        Dim [property] = type.GetProperty(orderByProperty)
        Dim parameter = Expression.Parameter(type, "p")
        Dim propertyAccess = Expression.MakeMemberAccess(parameter, [property])
        Dim orderByExpression = Expression.Lambda(propertyAccess, parameter)
        Dim resultExpression = Expression.[Call](GetType(Queryable), command, _
            New Type() {type, [property].PropertyType}, source.Expression, Expression.Quote(orderByExpression))
        Return source.Provider.CreateQuery(Of TEntity)(resultExpression)
    End Function

End Module

Open in new window

0
 
JeffreyDurhamAuthor Commented:
Best possible answer ever. Everything I disliked about Linq is solved. :)
0
 
Bob LearnedCommented:
Pretty cool...I haven't heard something like "best answer ever"!!  Expression trees are pretty cool, too.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now