How to convert this SQL to LINQ

Question

I created a database with code code, and the question is, is it difficult for me to rewrite this SQL statement into C # code.

The following is an SQL statement in which I need help with the adaptation and the tables that I currently use. The purpose of this SQL Query is that there is a record in the TableViewedMessageLog table about which user saw what message and the desired effect all Non-Read messages (what information is stored in this table — TableViewMessageLogs) for a specific user should select.

http://gyazo.com/0105c0959bdd2930272bf5c07a112a11

select * from TableMessages tm where tm.Id not in (select tv.Message_Id from TableViewedMessageLogs as tv where tv.User_Email = ' asd@asd ') 

Answer 1

Try the following:

 var data = (from e in context.TableMessages where context.TableViewedMessageLogs .Where(x => x.User_Email == ' asd@asd ') .Select(x => x.Message_Id).Contains(e.Id) == false select e) .ToList(); 

Answer 2

Try this query:

 var data = from f in context.TableMessages where f.id != ( from fb in TableViewedMessageLogs where User_Email == ' asd@asd ' select fb.Message_Id ) select f; 

Answer 3

You can do this to prevent a subquery, providing better performance and greatly facilitating understanding of what is happening:

 var viewedLogs = context.TableViewedMessageLogs.Where(w => w.User_Email = ' asd@asd '); var result = context.TableMessages.Where(w => !viewedLogs.Contains(w.Id));