Need help creating complex ef linq select

Question

Hello, I am having trouble creating a complex Linq with multiple joins and left joins.

I have 4 tables, as indicated at the end, what it is used for, to see how I need to send an email about a new answer on a topic. Therefore, I receive all messages from the topic that the user has joined. The same user may due to more than 1 post in each topic. After that, I join UserEmailSetting, which means that the user has refused to receive email notifications. Finally, I need to know if an email has been sent to the user notifying me of a new reply (I don’t want to spam my users if many replies have been made), so if a reply notification has been sent since the last visit the site I don’t want to send another mail. Here is my attempt that works, but I would like to optimize it! The problem is that there can be many results in UserEmailSetting, so I get a lot of results when I get only 1 or 2 back.

here is my attept

var select = (from p in ForumPostRepository.Get() join u in UserRepository.Get() on p.UserId equals u.Id join ues in UsersEmailSettingRepository.Get() on u.Id equals ues.UserId join els in (from _el in EmailLogRepository.Get() where _el.Type == "ReplyToTopic" && _el.Values == topicId orderby _el.Id descending select _el) on u.Id equals els.UserId into emailLogs from el in emailLogs.DefaultIfEmpty() where p.TopicId == forumTopic.Id && ues.ReplyToTopic //&& // We only want people who need notifications //!u.Online // We only want people who are not online orderby p.Id descending, el.Id descending select new { User = u, EmailLog = el }); var result = select.DistinctBy(x => x.User.Id).ToList(); 

Here are the database classes

 public class ForumPost { public int? TopicId { get; set; } public int UserId { get; set; } ... } public class User { public int Id { get; set; } public bool Online { get; set; } public DateTime LastLogin { get; set; } ... } public class UsersEmailSetting { public int UserId { get; set; } public bool ReplyToTopic { get; set; } } public class EmailLog { public int Id { get; set; } public int UserId { get; set; } public string Type { get; set; } public string Values { get; set; } public DateTime Created { get; set; } } 

Updata: a slightly more structured layout of what I want linq to be able to do, hope it helps

• Get all posts from ForumPostRepository, where topicId is 13.
• Join UserRepository at ForumPostRepository.UserId = UserRepository.Id
• Now I want to see only users
• Join UsersEmailSettingRepository at UserRepository.Id = UsersEmailSettingRepository.UserId
• Left connection with EmailLogRepository on UserRepository.Id = EmailLogRepository.UserId AND EmailLogRepository.Type = "ReplyToTopic" And EmailLogRepository.Values ​​= "topicId"
• → Now there can be from 0 to * results for this query, I want only the last!

Answer 1

There is no guarantee that this will be implemented.

  var users = UserRepository.Get(); var userEmailSettings = UsersEmailSettingRepository.Get(); var emailLogs = EmailLogRepository.Get(); var posts = ForumPostRepository.Get(); var foo = from user in users where posts .Any(post => post.UserId == u.Id && post.TopicId == topicId) where userEmailSettings .Any(ues => u.Id equals ues.UserId && ues.ReplyToTopic == true) where false == ( from el in emailLogs where el.Type == "ReplyToTopic" && el.Values == topicId && el.UserId == user.Id && el.Created > user.LastLogin select el ).Any() select user; 

Signature

A linq ninja

edit: just saw that you have no navigation properties.