We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

linq to sql vb.net case statements in select and order by

Medium Priority
2,836 Views
Last Modified: 2013-11-11
Hi all. I am trying to use linq to do a case statement in the order by but the problem is i have a custom column names that i want to sort on.  The only way i could get it to work was by writing another statement to sort the x variable into a new variable of z then i use that to bind to a dropdownlist as it is sorted as i want. But surely there is a better way of doing this than with 2 statements!

Thanks in advance for any help...


Dim x = From y In db.TicketSources Join pu In  db.tbO2PortalUsers On y.O2PortalUserID Equals pu.fdID _
                   Select New With {.value = y.TicketSourceID, .Text = If(y.O2PortalUserID <> 0 And pu.Active = 1, pu.FirstName & " " & pu.LastName, y.SourceName)} 
 
Dim z = From s In x Order By s.Text Ascending
 
 
return z.toList()  'the sorted list

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
I know that you can use extension methods to sort, like in this article:

Custom sorting order in LINQ (ORDER BY WEIGHTING)
http://www.daniel-skinner.co.uk/custom-sorting-order-in-linq-order-by-weighting/18/03/2009

But, I don't know the VB.NET-equivalent syntax for that kind of extension method.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
I am not sure about the ASP.NET dropdownlist but winforms version has a Sorted property.

Author

Commented:
Thanks both for you comments.

TheLearnedOne: i had seen those sorts during my extensive google searches, but i was hoping i could write the cope all in one statement like you can in actual SQL. Anyhow thanks for your reply.

CodeCruiser: No such property that i can find in asp.net VB version anyhow but again thankyou for your reply
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
I was able to sort a simple Select with an "Order By" clause:


        Dim db As New AdventureWorksDataContext()
 
        Dim q = From x In db.Products _
            Where x.Name.StartsWith("Road-650") _
            Order By x.Name _
            Select x
 
        Dim sb As New StringBuilder()
 
        For Each product As Product In q
            sb.Append(product.Name & vbCr & vbLf)
        Next

Open in new window

Author

Commented:
Thanks thelearnerone but that is an order by on a tables column name as apposed to what i was trying which was on a custom column name.  The order by for what i need would be the same as the select but its not possible as far as i can make out.

Thanks for your help.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
Oh, yeah, custom column name.  I got lost in a context switch (d'oh).

I don't see a way to order by a custom column name, but that doesn't mean that there isn't a way, just that I don't know every subtle nuance of LINQ.
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.