Linq query to display a proper sort order

Question

Table 1: Lookups

LookUpID
LookUpName
Desc
DisplayOrder

Table 2: BillingRates

BillingRateID
BillingRate
ClientID
LookupID

I want the lookup name to be displayed (sort by Bill rate)

DataContext DataContext1 = new DataContext1(AppSettings.ConnectionString);

return ( from Lookups in DataContext1.Lookups
         join BillingRates in DataContext1.BillingRates
         on  Lookups.LookupID equals BillingRates.LookupID
         orderby BillingRates.BillingRate
         select new
         {
             Lookups.LookupID,
             Lookups.LookupName,
             Lookups.Desc
         }).Distinct();

It gave me all the row, so I used Distinct(); The lookup Name is still not based on billing rate.

I am new to LINQ. Any pointers would be appreciated.

Answer 1

Why not just do the OrderBy at the end?

return (from Lookups in DataContext1.Lookups 
     join BillingRates in DataContext1.BillingRates 
     on  Lookups.LookupID equals BillingRates.LookupID 
     select new 
     { 
         Lookups.LookupID, 
         Lookups.LookupName, 
         Lookups.Desc,
         BillingRates.BillingRate
     })
     .GroupBy(x => x.LookupID)
     .Select(y => y.OrderByDescending(x => x.BillingRate).First())
     .OrderByDescending(x => x.BillingRate);

EDIT: I am kind of confused but try the following and let me know if that helps.

Answer 2

First of all, if you have a foreign key relationship set up, LINQ will create the join for you automatically, so it would be just:

 DataContext1.Lookups.Max(LkUp => LkUp.BillingRate.BillingRate)

Otherwise, (with the explicit join)

return ( from Lookups in DataContext1.Lookups   
     join BillingRates in DataContext1.BillingRates   
     on  Lookups.LookupID equals BillingRates.LookupID 
     orderby BillingRates.BillingRate desc
     select new   
     {   
         Lookups.LookupID,   
         Lookups.LookupName,   
         Lookups.Desc,  
         BillingRates.BillingRate  
     }).First();