Requesting for a snippet that can be used in LINQ to SQL query
-
Hi, In my code, I am counting the number of records (sent emails) that are older than one month. In order to do that, I am using following snippet.
int totalSentEmailsOlderThanMonth = emailSendingService.ListSentEmails().ToArray().Count(x => (DateTime.Now - x.SentDateTime).TotalDays > 30);
The reason I am using
.ToArray()in my code as(DateTime.Now - x.SentDateTime).TotalDayscannot be translated in LINQ to SQL query so, by callingToArray(), I am loading all records into the memory and then counting. But there are more than hundreds of records in my database and loading all the records into the memory just for counting is not efficient at all. So, would anyone please give me an alternative more efficient snippet that can be used in this scenario. [[ By the way,emailSendingService.ListSentEmails()returnsIQueryable<MyRecordType>]] Regards. -
Hi, In my code, I am counting the number of records (sent emails) that are older than one month. In order to do that, I am using following snippet.
int totalSentEmailsOlderThanMonth = emailSendingService.ListSentEmails().ToArray().Count(x => (DateTime.Now - x.SentDateTime).TotalDays > 30);
The reason I am using
.ToArray()in my code as(DateTime.Now - x.SentDateTime).TotalDayscannot be translated in LINQ to SQL query so, by callingToArray(), I am loading all records into the memory and then counting. But there are more than hundreds of records in my database and loading all the records into the memory just for counting is not efficient at all. So, would anyone please give me an alternative more efficient snippet that can be used in this scenario. [[ By the way,emailSendingService.ListSentEmails()returnsIQueryable<MyRecordType>]] Regards.You need to re-write the predicate a little bit (I don't have time to check this, so consider it not-very psuedo psuedo code) and it might error:
DateTime latestSendDate = DateTime.Now.AddDays(-30);
int totalSentEmailsOlderThanMonth = emailSendingService.ListSentEmails().Count(x => x.SentDateTime <= latestSendDate);This removes the "calculation" from the LINQ query count predicate and puts it into a format it should be able to generate SQL from. There may be an out by one error (you get 29/31 days). I think this will work, as I say, I don't have time to check it. Let me know if there are any problems.
Sort of a cross between Lawrence of Arabia and Dilbert.[^]
-Or-
A Dead ringer for Kate Winslett[^] -
You need to re-write the predicate a little bit (I don't have time to check this, so consider it not-very psuedo psuedo code) and it might error:
DateTime latestSendDate = DateTime.Now.AddDays(-30);
int totalSentEmailsOlderThanMonth = emailSendingService.ListSentEmails().Count(x => x.SentDateTime <= latestSendDate);This removes the "calculation" from the LINQ query count predicate and puts it into a format it should be able to generate SQL from. There may be an out by one error (you get 29/31 days). I think this will work, as I say, I don't have time to check it. Let me know if there are any problems.
Sort of a cross between Lawrence of Arabia and Dilbert.[^]
-Or-
A Dead ringer for Kate Winslett[^]Owe, thats the cute idea. Thanks a loooot. Sure, it is a working code and it is working perfectly. Thank you very much for your help.