linq query exception when navigation empty

[Issam]

hi,
this is a subset of my model to illustrate the thing
[URL=http://www.hostingpics.net/viewer.php?id=984351model.jpg][IMG]http://img15.hostingpics.net/pics/984351model.jpg[/IMG][/URL]

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

[Viktor Zhivkov]

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
2.{
3.    adh = c,
4.    cnt = EntitiesModel.GetLastSubscriptionSeancesCount(c.Id)
5.});

Where EntitiesModel.GetLastSubscriptionSeancesCount is a database mapped function defined in SQL as:

01.CREATE FUNCTION GetLastSubscriptionSeancesCount(@adherantId int)
02.RETURNS int
03.AS
04.BEGIN
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
10.END
11.GO

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

Regards,
Viktor Zhivkov
Telerik