Solved

VB.Net Linq To Sql orderby problem

Posted on 2010-09-13
17
1,119 Views
Last Modified: 2013-11-11
The attached code does not seem to generate an order by clause in the sql that is actually executed.  This is true whether I orderby Asc or Desc.  Also, the client complains that the results are not distinct, though I am so far unable to prove that.

Any thoughts?


Public Shared Sub FillBillNoDropDown(ByVal f As Form1)

        Try

            f.billNo.Items.Clear()



            Dim ap3 As New Ap3DataContext(Globals.ConnString)



            Dim innerQuery = From i In ap3.esna_APApprovedBills Select i.bill_no.Trim()



            If Globals.QtyModeIndicator Then

                Dim billNos = From c In ap3.imrechst_sqls _

                    Where c.source = "R" And Not innerQuery.Contains(c.pack_no.Trim()) _

                        And (c.vchr_no Is Nothing Or c.vchr_no.Trim() = "") _

                        And c.rec_hst_dt > New DateTime(2010, 5, 31) _

                    Order By c.pack_no Ascending _

                    Select c.pack_no Distinct



                For Each billNo In billNos

                    If billNo <> Nothing Then

                        f.billNo.Items.Add(billNo)

                        End If

                Next 'billno

            Else

                Dim billNos = From c In ap3.imrechst_sqls _

                    Where c.source = "R" And innerQuery.Contains(c.pack_no.Trim()) _

                    Order By c.pack_no Ascending _

                    Select c.pack_no Distinct



                For Each billNo In billNos

                    If billNo <> Nothing Then

                        f.billNo.Items.Add(billNo)

                        End If

                Next 'billno

            End If



            If f.billNo.Items.Count = 1 Then

                f.billNo.SelectedIndex = 0

            End If

        Catch ex As Exception

            Throw New Exception(ex.Message)

        End Try

    End Sub

------------------------------------------------------------

Profiler Results:

exec sp_executesql N'SELECT DISTINCT [t0].[pack_no]

FROM [dbo].[imrechst_sql] AS [t0]

WHERE (((CONVERT(NVarChar(1),[t0].[source])) = @p0) AND (NOT (EXISTS(

    SELECT NULL AS [EMPTY]

    FROM (

        SELECT LTRIM(RTRIM([t1].[bill_no])) AS [value]

        FROM [dbo].[esna_APApprovedBills] AS [t1]

        ) AS [t2]

    WHERE [t2].[value] = LTRIM(RTRIM([t0].[pack_no]))

    ))) AND (([t0].[vchr_no] IS NULL) OR (LTRIM(RTRIM([t0].[vchr_no])) = @p1))) 

    AND ([t0].[rec_hst_dt] > @p2)',N'@p0 nvarchar(1),@p1 varchar(8000),

    @p2 datetime',@p0=N'R',@p1='',@p2='2010-05-31 00:00:00'

Open in new window

0
Comment
Question by:g_johnson
  • 8
  • 7
  • 2
17 Comments
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 33663394
Hi g_johnson;

It looks like you are ordering on a string values. Does the column c.pack_no contain only alpha characters, numeric digits only or both?

Fernando
0
 
LVL 4

Author Comment

by:g_johnson
ID: 33663775
it is a character field, but my data contains only numbers.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 33664801
If you have a character data field, with numeric data, you need to cast the field to a numeric type, otherwise you would be getting lexical sorting, instead of numerical.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 33665671
Hi g_johnson;

Yes, TheLearnedOne is correct and the reason for my question to you.

See the code snippet for changes to your query.

Fernando
Public Shared Sub FillBillNoDropDown(ByVal f As Form1)

    Try

        f.billNo.Items.Clear()



        Dim ap3 As New Ap3DataContext(Globals.ConnString)



        Dim innerQuery = From i In ap3.esna_APApprovedBills Select i.bill_no.Trim()



        If Globals.QtyModeIndicator Then

            Dim billNos = From c In ap3.imrechst_sqls _

                Where c.source = "R" And Not innerQuery.Contains(c.pack_no.Trim()) _

                    And (c.vchr_no Is Nothing Or c.vchr_no.Trim() = "") _

                    And c.rec_hst_dt > New DateTime(2010, 5, 31) _

                Select pack_no = Convert.ToInt32(c.pack_no)

                Distinct _

                Order By pack_no Ascending



            For Each billNo In billNos

                If billNo <> Nothing Then

                    f.billNo.Items.Add(billNo)

                    End If

            Next 'billno

        Else

            Dim billNos = From c In ap3.imrechst_sqls _

                Where c.source = "R" And innerQuery.Contains(c.pack_no.Trim()) _

                Select pack_no = Convert.ToInt32(c.pack_no)

                Distinct _

                Order By pack_no Ascending



            For Each billNo In billNos

                If billNo <> Nothing Then

                    f.billNo.Items.Add(billNo)

                    End If

            Next 'billno

        End If



        If f.billNo.Items.Count = 1 Then

            f.billNo.SelectedIndex = 0

        End If

    Catch ex As Exception

        Throw New Exception(ex.Message)

    End Try

End Sub

Open in new window

0
 
LVL 4

Author Comment

by:g_johnson
ID: 33665808
Well, I'm not sure the field will always be numeric in all client's data.  so how do i handle it if it's mixed data like that?

And, just out of curiosity, why do I not see the "Ascending" or "Descending" in the SQL Profiler trace?
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 33666116
So then the question now is how do you want the output to be in given a specific sequence of pack_no. If you can show a list of pack_no  before the sort and also after the sort that will be very helpful.

To your question, "why do I not see the "Ascending" or "Descending" in the SQL Profiler trace?", because this time around it is being done at the local system and not the server.
0
 
LVL 4

Author Comment

by:g_johnson
ID: 33666999
Whether or I am soring ascending or descending or even if I leave the orderby clause out entirely, the list comes out like this:

12678
21762
7659

For ascending, that would work, but not for descending.  Again, my client's COULD have an alpha-numeric in there, so I hesitate to do the cast as suggested above.

This is being fed, by the way, to an unsorted combo box.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 33667135
You can't sort alpha-numeric data numerically, if it possible to have alpha characters.  You are going to be stuck with lexical sorting, which is not what you want.  You might be able to sort on the front-end, with an IComparer/IComparable implementation, that can handle the alpha-numeric data.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 4

Author Comment

by:g_johnson
ID: 33667193
Okay -- I get that, I think.  Nonetheless, shouldn't the list be in a different order with ascending than with descending?  If I do the query in Management Studio, I get this for ascending:
               
125789        
12678          
21762          
7659          
77692          
889745        
980231        

and this for descending:

980231        
889745        
77692          
7659          
21762          
12678          
125789        
               

Note:  In this example, I did not bother to filter by the join on the other table or apply any other parts of the where clause.

Thanks
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 33667812
Hi g_johnson;

In your post ID: 33666999 You state that the results are as follows no matter if you do Ascending or Descending.

12678
21762
7659

Which is in Ascending  order. Where did you observe these values for both Ascending and Descending?
Fernando
0
 
LVL 4

Author Comment

by:g_johnson
ID: 33667848
f.billno is an unsorted combo box on the form.  It's in that combo box that I make that observation.

For the moment, I have done a work-around by adding the db results to a list<string> then selecting from that with an order by clause, and that seems to work.

I would still like to know what is going on with the linq to sql, though.

Thanks
0
 
LVL 4

Author Comment

by:g_johnson
ID: 33667862
Anticipating your next question, I did step through the code:

            For Each billNo In billNos
                If billNo <> Nothing Then
                    f.billNo.Items.Add(billNo)
                    End If
            Next 'billno

and I can observe there that it is always in the same order regardless of the order by clause
0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 33667977
Hi g_johnson;

Let see what SQL query is being sent to the server. If you can place this line of code just after creating the data context.

ap3.Log = Console.Out

After running the query look at the IDE Output window to see the query sent to the server, please use your original query in the question.

After that test try reformatting the query as follows:

Order By Ascending
Dim billNos = (From c In ap3.imrechst_sqls _
               Where c.source = "R" And Not innerQuery.Contains(c.pack_no.Trim()) _
               And (c.vchr_no Is Nothing Or c.vchr_no.Trim() = "") _
               And c.rec_hst_dt > New DateTime(2010, 5, 31) _
               Select c.pack_no).Distinct().OrderBy(Function(c) c)
               
Order By Descending              
Dim billNos = (From c In ap3.imrechst_sqls _
               Where c.source = "R" And Not innerQuery.Contains(c.pack_no.Trim()) _
               And (c.vchr_no Is Nothing Or c.vchr_no.Trim() = "") _
               And c.rec_hst_dt > New DateTime(2010, 5, 31) _
               Select c.pack_no).Distinct().OrderByDescending(Function(c) c)              

What were the results?

Fernando
0
 
LVL 4

Author Comment

by:g_johnson
ID: 33668210
This is from the original:

SELECT DISTINCT [t0].[pack_no]
FROM [dbo].[imrechst_sql] AS [t0]
WHERE (((CONVERT(NVarChar(1),[t0].[source])) = @p0) AND (NOT (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM (
        SELECT LTRIM(RTRIM([t1].[bill_no])) AS [value]
        FROM [dbo].[esna_APApprovedBills] AS [t1]
        ) AS [t2]
    WHERE [t2].[value] = LTRIM(RTRIM([t0].[pack_no]))
    ))) AND (([t0].[vchr_no] IS NULL) OR (LTRIM(RTRIM([t0].[vchr_no])) = @p1))) AND ([t0].[rec_hst_dt] > @p2)
-- @p0: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [R]
-- @p1: Input VarChar (Size = 0; Prec = 0; Scale = 0) []
-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [5/31/2010 12:00:00 AM]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1


This is from your suggested code:

SELECT [t3].[pack_no]
FROM (
    SELECT DISTINCT [t0].[pack_no]
    FROM [dbo].[imrechst_sql] AS [t0]
    WHERE (((CONVERT(NVarChar(1),[t0].[source])) = @p0) AND (NOT (EXISTS(
        SELECT NULL AS [EMPTY]
        FROM (
            SELECT LTRIM(RTRIM([t1].[bill_no])) AS [value]
            FROM [dbo].[esna_APApprovedBills] AS [t1]
            ) AS [t2]
        WHERE [t2].[value] = LTRIM(RTRIM([t0].[pack_no]))
        ))) AND (([t0].[vchr_no] IS NULL) OR (LTRIM(RTRIM([t0].[vchr_no])) = @p1))) AND ([t0].[rec_hst_dt] > @p2)
    ) AS [t3]
ORDER BY [t3].[pack_no]
-- @p0: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [R]
-- @p1: Input VarChar (Size = 0; Prec = 0; Scale = 0) []
-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [5/31/2010 12:00:00 AM]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

This is from the orderbydescending, and the results are now correct:
SELECT [t3].[pack_no]
FROM (
    SELECT DISTINCT [t0].[pack_no]
    FROM [dbo].[imrechst_sql] AS [t0]
    WHERE (((CONVERT(NVarChar(1),[t0].[source])) = @p0) AND (NOT (EXISTS(
        SELECT NULL AS [EMPTY]
        FROM (
            SELECT LTRIM(RTRIM([t1].[bill_no])) AS [value]
            FROM [dbo].[esna_APApprovedBills] AS [t1]
            ) AS [t2]
        WHERE [t2].[value] = LTRIM(RTRIM([t0].[pack_no]))
        ))) AND (([t0].[vchr_no] IS NULL) OR (LTRIM(RTRIM([t0].[vchr_no])) = @p1))) AND ([t0].[rec_hst_dt] > @p2)
    ) AS [t3]
ORDER BY [t3].[pack_no] DESC
-- @p0: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [R]
-- @p1: Input VarChar (Size = 0; Prec = 0; Scale = 0) []
-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [5/31/2010 12:00:00 AM]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1

I can't say that I understand what was wrong with the first technique, but if your technique works, that's great!

Can you explain the function part of it without wasting too much of your time?  :)
I really appreciate the help.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 33671990
Hi g_johnson;

To your statement, "I can't say that I understand what was wrong with the first technique, but if your technique works, that's great!", I am at a lost on why your first query did not have the Order By clause, where in my option it should have. I am just as surprised to see that in the two queries I asked you to do that the SQL sent to the server had the Order By and Distinct clauses in it and here is why:

Between "(From c In " and "Select c.pack_no)" part of the query you will note that the query is surrounded  by ( ... ) which is to be done first and the Distinct and OrderBy should be applied after the results come back from the server. I can only take a guess at why it happened this way and that would be that the system optimized it to run more efficiently but do not know that for a fact.

Fernando
0
 
LVL 4

Author Closing Comment

by:g_johnson
ID: 33672537
Thanks for your help!
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 33672653
Not a problem, glad I was able to help and if I can find out what was happening with the original query I will post here.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now