locked
Order by Subtable in Controller RRS feed

  • Question

  • User-692821018 posted

    I have the following in my controller:

    var site = await _context.Sites
                    .Include(w=>w.Client)
                    .Include(w => w.SiteType)                    
                    .Include(w => w.WaterBodys)
                    .Include(w=>w.Job)                
                        .AsNoTracking()
                    .SingleOrDefaultAsync(m => m.SiteID == id);

    I want to order descending by booking date within Job.

    I tried various variations:

    var site = await _context.Sites
                    .Include(w=>w.Client)
                    .Include(w => w.SiteType)                    
                    .Include(w => w.WaterBodys)
                    .Include(w=>w.Job.OrderDescendingBy(b=>b.BookingDate))            
                        .AsNoTracking()
                    .SingleOrDefaultAsync(m => m.SiteID == id);
    
    
    var site = await _context.Sites
                    .Include(w=>w.Client)
                    .Include(w => w.SiteType)                    
                    .Include(w => w.WaterBodys)
                    .Include(w=>w.Job)
                        .OrderDescendingBy(b=>b.BookingDate)            
                        .AsNoTracking()
                    .SingleOrDefaultAsync(m => m.SiteID == id);

    etc

    Nothing seems to want to play nice.  I tried googling a solution but not quite sure what to search for.  I still get confused by this sometimes.

    Friday, March 31, 2017 9:36 PM

All replies

  • User527778624 posted

    Hi, Try this linq:

    var site = await _context.Sites
                    .Include(w=>w.Client)
                    .Include(w => w.SiteType)                    
                    .Include(w => w.WaterBodys)
                    .Include(w => w.Job)                
                    .AsNoTracking()
    		.OrderDescendingBy(w => w.Job.BookingDate)
                    .SingleOrDefaultAsync(m => m.SiteID == id);

    Saturday, April 1, 2017 6:47 AM
  • User-692821018 posted

    Hi, Try this linq:

    var site = await _context.Sites
                    .Include(w=>w.Client)
                    .Include(w => w.SiteType)                    
                    .Include(w => w.WaterBodys)
                    .Include(w => w.Job)                
                    .AsNoTracking()
    		.OrderDescendingBy(w => w.Job.BookingDate)
                    .SingleOrDefaultAsync(m => m.SiteID == id);

    'IQueryable<Site>' does not contain a definition for 'OrderDescendingBy' and no extension method 'OrderDescendingBy' accepting a first argument of type 'IQueryable<Site>' could be found (are you missing a using directive or an assembly reference?)

    Saturday, April 1, 2017 7:35 AM
  • User527778624 posted

    Hi,

    'IQueryable<Site>' does not contain a definition for 'OrderDescendingBy'

    Use OrderByDescending

    http://stackoverflow.com/questions/34226296/iqueryable-does-not-contain-a-definition-for-orderbydescending

    Saturday, April 1, 2017 7:52 AM
  • User-692821018 posted

    A lot of reading later...

    Okay so the issue is I am wanting to sort the Jobs by their booking date.  However the entity is site driven.  Therefore adding the order by clause is causing the site to want to sort by bookingDate, and as site doesn't contain booking date this isn't going to work.  Phew...what a sentence.

    Now, as a somewhat newcomer to coding I am struggling to move past this.  I have half figured out a kind of work around.

     var jobSort = from j in _context.Job
                              .OrderBy(j => j.BookingDate)
                              select j;
    
                var site = await _context.Sites
                    .Include(w => w.Client)
                    .Include(w => w.SiteType)
                    .Include(w => w.WaterBodys)                
                        .AsNoTracking()
                        .SingleOrDefaultAsync(m => m.SiteID == id);

    I think this is sorting the jobs by booking date first.  THen I want to include the result in the site results, and join on siteID (I assume).  I am not sure what to do from here?

    Saturday, April 1, 2017 9:23 PM
  • User-271186128 posted

    Hi BitLost,

    I think this is sorting the jobs by booking date first.  THen I want to include the result in the site results, and join on siteID (I assume).  I am not sure what to do from here?

    When using the SingleOrDefaultAsync method, it will return the a single element or the default value, so, you need  to sort the site first, then select the site.

    Therefore adding the order by clause is causing the site to want to sort by bookingDate, and as site doesn't contain booking date this isn't going to work

    From your description, I suggest you could using OrderBy/OrderByDescending and  ThenBy/ThenByDescending statement to sort the site. If the booking date is null, then, sort the site by the site ID. Then, using the SingleOrDefaultAsync method to select a single record.

    Code as below:

    var query = db.UserSelectedStocks.OrderBy(c => c.UserID).ThenBy(c => c.StockID).ToList();

    Best regards,
    Dillion

    Monday, April 3, 2017 8:38 AM