Linq query to display a proper sort order

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
         开发者_Python百科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.

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.

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();