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 IsConfirmed
is set to 1
.
For registered user DateTimeUtcRequestSend
does not have any purpose.
Both types of users must enter email (mandatory field), and can enter 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?
2
Answers
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:or
Yes it will, but you must be careful how you use it because it can change the whole meaning of your condition.
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 thanWhere()
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:
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
which, by itself, comes out to the following. The parentheses are significant and remove doubt about order of operations.
You then have
which comes to the following (in your text you said that "Both types of users must enter email (mandatory field), and can enter umcn if they like so (not mandatory)." so I am re-including this condition:
The two conditions must then be OR‘d and simplified.. OR‘ing we get
which, after simplifying (using the fact we can rearrange the ANDs and factor out the common parts), we get
So that’s it with the conditions. Now, you need to make that into a query. Your query should use
Any
notWhere
. You’re looking to see if the user meets a single requirement, not many requirements, soAny
suffices, andWhere
is too much.If
GetAll()
returnsIQueryable
you can add theAny()
clause afterGetAll()
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.vs.
As you can see, this whole compound condition can be a single lambda and doesn’t require expressions to get into the mix.