How to put into linq lambda expression two opposite things but one has additional clause-CodePudding

I have following situation: In my db table Schedule I have columns: IsConfirmed, IsAnonymous, DateTimeUtcRequestSend, EmailAddress, UMCN, Deleted, ServiceId

User can be registered user or anonymous. If the user is anonymous, it has a flag IsAnonymous = 1 otherwise the flag is 0. If user is anonymous, he must click on the link and he has 10 minutes for activation. Field DateTimeUtcRequestSend is the time when he created a request.

If user is registered, automatically IsConfirmedis set to 1. For registered user DateTimeUtcRequestSend does not have any purpose. Both types of users must enter email (mandatory field), and can enetr umcn if they like so (not mandatory).

Each user registered or not can check multiple services (list of serviceId FKs)

I need to create a query that will check if there any row with users that:

registered users (IsConfirm = 1) and have same UMCN or email and have at least 1 identical serviceId
anonymous users (IsConfirm = 0 and IsAnonymous = 1 and DateTime.UtcNow - DateTimeUtcRequestSend < 10min or IsConfirm = 1 and IsAnonymous = 1) above mentioned conditions and if does, to return a message that new insert is not possible.

I tried this approach:

var query = scheduleService.GetAll()
    .Where(x => !x.Deleted
        && x.EmailAddress == data.email || x.UMCN== data.UMCN
        && x.IsConfirmed
        && servicesIdDistinctedBy.Contains(x.ServiceId));

but im not sure how to add opposite condition when user is not confirmed and he is anonymous and in timeframe of 10minutes:

var query = scheduleService.GetAll()
    .Where(x => !x.Deleted
        && x.EmailAddress == data.email || x.UMCN== data.UMCN
        && x.IsConfirmed
        && (!x.IsConfirmed && x.IsAnonymous.Value && DateTime.UtcNow - x.DateTimeUtcRequestSend < Constants.ConfirmationTimeWindow)
        && servicesIdDistinctedBy.Contains(x.ServiceId));

Are this parenthesis gonna help, to "isolate" condition? Maybe I need to use .Any() function instead of .Where()?

Do I need maybe some Expression<Func<T>> to be able to differentiate based on some conditions? I think it's not neccessery?

CodePudding user response：

First of all, you should avoid using GetAll(), which will scan your whole table instead of just querying what you need, and will take longer and longer as your table grows. Instead you should just go for:

var query = scheduleService.Where(x =>...

var query = scheduleService.Any(x =>...

Are this parenthesis gonna help, to "isolate" condition?

Yes it will, but you must be careful how you use it because it can change the whole meaning of your condition.

Maybe I need to use .Any() function instead of .Where()

If your goal is just to confirm that there is a row in your table that correspond to your condition, then yes, you should use Any() as it will be significantly faster than Where() because it will stop searching through the table once it has found the row it's looking for.

On the other end, if you need to get all of the data that matches your condition, then you need to stick with Where().

From your explanation, I'm not entirely clear what you need, but so far as I understand it I think the following should help:

var query = scheduleService.Where(x => !x.Deleted
    && (x.EmailAddress == data.email || x.UMCN== data.UMCN)
    && ((x.IsConfirmed || (!x.IsConfirmed && x.IsAnonymous.Value))
    && (DateTime.UtcNow - x.DateTimeUtcRequestSend < Constants.ConfirmationTimeWindow)))
    && servicesIdDistinctedBy.Contains(x.ServiceId));

CodePudding user response：

If I'm understanding you correctly, you are asking whether a confirmed user can check some services without a time limit, or if an anonymous user can do so within 10 minutes.

This comes down to orders of operations and interpreting your requirements correctly into code to make an efficient query. Let's break it down. I'm going to attempt to use your variable names.

You have this condition

registered users (IsConfirm = 1) and have same UMCN or email and have at least 1 identical serviceId

which, by itself, comes out to the following. The parentheses are significant and remove doubt about order of operations.

// registered user
x.IsConfirmed
  // whose email address matches or whose UMCN matches
  && (x.EmailAddress == data.email || x.UMCN== data.UMCN)
  // and whose service ID is one of the set of distinct service IDs
  && servicesIdDistinctedBy.Contains(x.ServiceId)

You then have

anonymous users (IsConfirm = 0 and IsAnonymous = 1 and DateTime.UtcNow - DateTimeUtcRequestSend < 10min or IsConfirm = 1 and IsAnonymous = 1) above mentioned conditions and if does, to return a message that new insert is not possible.

which comes to the following (in your text you said that "Both types of users must enter email (mandatory field), and can enetr umcn if they like so (not mandatory)." so I am re-including this condition:

// anonymous user is both not confirmed
!x.IsConfirmed 
  // and must have a value that is true (a case where == true is justified)
  // i.e. we can't just do x.IsAnonymous because that could throw as it is nullable
  && x.IsAnonymous?.Value == true
  // whose email address matches or whose UMCN matches
  && (x.EmailAddress == data.email || x.UMCN== data.UMCN)
  && DateTime.UtcNow - x.DateTimeUtcRequestSend < Constants.ConfirmationTimeWindow
  && servicesIdDistinctedBy.Contains(x.ServiceId)

The two conditions must then be OR'd and simplified.. OR'ing we get

(
x.IsConfirmed
  && (x.EmailAddress == data.email || x.UMCN== data.UMCN)
  && servicesIdDistinctedBy.Contains(x.ServiceId)
)
||
(!x.IsConfirmed 
  && x.IsAnonymous?.Value == true
  && (x.EmailAddress == data.email || x.UMCN== data.UMCN)
  && DateTime.UtcNow - x.DateTimeUtcRequestSend < Constants.ConfirmationTimeWindow
  && servicesIdDistinctedBy.Contains(x.ServiceId)
)

which, after simplifying (using the fact we can rearrange the ANDs and factor out the common parts), we get

// match either mandatory email or optional UMCN
(x.EmailAddress == data.email || x.UMCN == data.UMCN)
  && (
    // and must be confirmed
    x.IsConfirmed 
    // or anonymous, completing in 10 minutes
    || (!x.IsConfirmed
      && x.IsAnonymous?.Value == true
      && DateTime.UtcNow - x.DateTimeUtcRequestSend < Constants.ConfirmationTimeWindow))
  // and finally with the distinct service ID
  && servicesIdDistinctedBy.Contains(x.ServiceId)

So that's it with the conditions. Now, you need to make that into a query. Your query should use Any not Where. You're looking to see if the user meets a single requirement, not many requirements, so Any suffices, and Where is too much.

If GetAll() returns IQueryable you can add the Any() clause after GetAll() returns and still defer the entire thing so it runs on the server against the DB rather than after getting everything and then applying the condition in memory.

If that's not the case, it would be better to append the Any() to the enumerable directly.

scheduleService.AnyMatch(x => ...)

vs.

scheduleService.GetAll().Where(x => ...)

As you can see, this whole compound condition can be a single lambda and doesn't require expressions to get into the mix.