asked on

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

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

Bob Learned

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.

Nasir Razzaq

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

philipeharris

ASKER

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

Bob Learned

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

philipeharris

ASKER

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.

Bob Learned

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.

ASKER CERTIFIED SOLUTION

philipeharris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial