0

I have a query in entity framework which gets some messages from tables.

var ur = (from m in en.Messages
      join mb in en.aspnet_Membership on m.FromUserId equals mb.UserId
      join urs in en.UserProfiles on mb.UserId equals urs.UserId
      join g in en.Groups on m.ToUserId equals g.GroupId
      join ug in en.UserInGroups on g.GroupId equals ug.GroupId
      where ug.UserId == userId
      select new 
      {
          InboxId = m.MessageId,
          FromUser = urs.RaveName.Trim(),
          CreatedOn = m.CreatedOn
      }
      ).Concat(

      // msg is not deleted
      from m in en.Messages
      join mb in en.aspnet_Membership on m.FromUserId equals mb.UserId
      join urs in en.UserProfiles on mb.UserId equals urs.UserId
      where m.ToUserId == userId 
      select new 
      {
          InboxId = m.MessageId
          ,
          FromUser = urs.RaveName.Trim()          
          ,
          CreatedOn = m.CreatedOn
      }
      );

I have another table which shows whether the message is used:

var msg = (from m in en.MessagesUsed
            where m.UserId == userId
            select m
            );

Now, I need to check: Is there a message in 'ur' that is not in 'msg'? In T-SQL, we can use:

SELECT 1
FROM ur 
WHERE NOT EXISTS (SELECT 1 FROM msg WHERE msg.Id = ur.InboxId AND msg.FromUser = ur.FromUser AND msg.CreatedOn = ur.CreatedOn)

to check this. But how to do it in LINQ?

Thanks

urlreader
  • 6,319
  • 7
  • 57
  • 91
  • Im not sure how your linq relates to your sql but i think you are looking for the Any() method. – Ben Robinson Sep 17 '14 at 16:53
  • 'msg' is a list from the query, that's why I do not know how to write that .Any(x => x.Id == ?????) – urlreader Sep 17 '14 at 16:58
  • Duplicate of http://stackoverflow.com/questions/432954/not-in-clause-in-linq-to-entities – Guillaume CR Sep 17 '14 at 17:36
  • Do we have to use .Contains()? Any way to get around it? 'msg' is a long list, if I get it first, then use .Contains(), the performance will not be good, I guess. Maybe I'm wrong? – urlreader Sep 17 '14 at 17:53
  • @urlreader You're looking for .Except(). Read the duplicate answer I linked. – Guillaume CR Sep 17 '14 at 18:21
  • .Except() or .Contains(), they both require we get the list to be excluded first. In my case, it is the 'msg' list. It has to be a way to optimize this. otherwise, performance will be an issue if 'msg' is a long list. – urlreader Sep 17 '14 at 18:54
  • @urlreader I don't know how you could find out which ur isn't in msg without first loading msg. Donald Knuth once said "premature optimization is the root of all evil". Worry about performance when you meet a bottleneck, not before. – Guillaume CR Sep 17 '14 at 19:54

0 Answers0