linq query exception when navigation empty

2 posts, 0 answers
  1. issam
    issam avatar
    18 posts
    Member since:
    Sep 2008

    Posted 08 May 2014 Link to this post

    this is a subset of my model to illustrate the thing

    it's for a fitness center management software so :
    ADHERANTS            ->         customers
    ABONEMENTS        ->          subscriptions
    SEANCES                 ->          fitness session

    now i want to show a list of customers with their sessions count :
    this query gave me an exception 

      var adheants = _context.ADHERANTs.Select(c => new
                     adh = c,
                     cnt = c.ABONNEMENTs.OrderByDescending(s=>s.AB_DATE_DEBUT).FirstOrDefault().SEANCEs.Count
     my guess is beacause some of the cusomers don't have subscriptions yet  .

    secondly : What the Defaultifempty operator and can it help me in this situation
    thanks for your help and good day

  2. Viktor Zhivkov
    Viktor Zhivkov avatar
    290 posts

    Posted 13 May 2014 Link to this post

    Hello Issam,

    If I understand your scenario correctly you are trying to get the number of seances in the last client subscription. If so the most elegant way to do it is to define a scalar database function that can calculate the number of seances and use it in a LINQ query like this:
    1.var adheants = _context.ADHERANTs.Select(c => new
    3.    adh = c,
    4.    cnt = EntitiesModel.GetLastSubscriptionSeancesCount(c.Id)

    Where EntitiesModel.GetLastSubscriptionSeancesCount is a database mapped function defined in SQL as:
    01.CREATE FUNCTION GetLastSubscriptionSeancesCount(@adherantId int)
    02.RETURNS int
    05.    DECLARE @result int
    06.    SELECT @result = count(s.ID)
    07.     from [SEANCES] s
    08.     where s.ABONEMENTS_ID = (select top(1) a.ABONEMENT_ID from ABONEMENTS a where @adherantId = a.ADHERANT_ID order by a.ABONEMENT_ID desc)
    09.    RETURN @result

    If you need any further assistance, we will be happy to answer your questions related to Telerik Data Access.

    Viktor Zhivkov
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
  3. DevCraft banner
Back to Top