C# EF Core - How to get first item or null-CodePudding

I am trying to write an EF Core Select that will get me a birthday, a birthday can have many NotificationPeriod's, but only one per user. So a birthday can have one NotificationPeriod for a specific user. This is so that a user can specify a non default notification period. By default users will have a notification period of 7 days, but if they choose to change this then an entry will be added into the NotificationPeriod table.

The tables look like this, I've not included the user one as this should be enough:

How can I write a EF Core Select to get me the one NotificationPeriod or if it null set it to the default of 7?

var birthday = _birthdayRepository.GetAll()
    .Where(x => x.BirthdayId == birthdayId)
    .Select(x => new BirthdayDto
    {
        BirthdayId = x.BirthdayId,
        Forename = x.Forename,
        Surname = x.Surname,
        DateOfBirth = x.DateOfBirth,
        NotificationPeriod = x.BirthdayNotificationPeriods.FirstOrDefault(bn => bn.UserId == user.UserId)?.NotificationPeriod ?? 7 // Default of 7 days
    }).FirstOrDefault();

As you can see I get a null propagation error, I can write this in a stored proc no problem but not sure how to do this with EF Core?

CodePudding user response：

Part of the problem is that C# designers didn't find a time to add null propagating operator support in expression trees.

At the same time, LINQ to Entities is a bridge between two worlds - SQL and C#. And while nulls propagate natively in SQL, C# doesn't know that.

So you have to tell C# explicitly that the result value is nullable by using the corresponding nullable type cast operator. e.g. either

NotificationPeriod = (int?)x.BirthdayNotificationPeriods
    .FirstOrDefault(bn => bn.UserId == user.UserId).NotificationPeriod ?? 7

(note the direct usage of . which in LINQ to Objects would produce Null Reference Exception. But not in server side LINQ to Entities query)

or a bit more verbose, but eliminating the . difference

NotificationPeriod = x.BirthdayNotificationPeriods
    .Where(bn => bn.UserId == user.UserId)
    .Select(bn => (int?)bn.NotificationPeriod)
    .FirstOrDefault() ?? 7

Replace int? with the corresponding NotificationPeriod nullable type in case it is not int.

CodePudding user response：

You are doing this on Expression tree and it doesn’t support the null conditional operator.

You can do like this.

Please change the variable names accordingly.

yourObj == null ? 7 : (yourObj.PropertyName)

CodePudding user response：

First, if you created your model well, then Birthday class would belong to a single User, no? In that case you don't need lambda inside FistOrDefault` at all. You can probably get away with something like

 NotificationPeriod = x.BirthdayNotificationPeriods ? x.BirthdayNotificationPeriods.First().NotificationPeriod : 7 // Default of 7 days

Probably somewhat of a kludge; but should work