asp.net - Left join using a different or not equal in Entity Framework -

i have build query users did not received , alert new posts.

my relevant table structure follows:

posts: postid int users: userid int, email varchar postalertusers: postalertuserid int, postid int, userid int

all related fields have fk constraints between tables.

i've built query in sql couldn't find way work in entity framework:

select u.email users u inner join posts p on p.userid != u.userid  left join postalertusers pu on u.userid = pu.userid , p.postid = pu.postid  pu.postalertuserid null 

i've wrote following ef query, didn't got same results:

from u in context.users join pu in context.postalertusers on u.userid equals pu.userid postalerts pa in postalerts.defaultifempty() join p in context.posts on pa.postid equals p.postid pa.userid != u.userid select u.email; 

how can same results using linq entities. using dot syntax (i don't know correct term dbset.where(x => ...) syntax) better.

edit:

i want users didn't have record on postalertusers each post in posts isn't same user.

edit 2:

trying clarify bit more:

i want warn users new posts other users 1 time each post , routine run every hour check if there send message.

i want users aren't warned post not have record on postalertusers user , post combination have record on posts user other itself.

example data:

users ------------------------ userid | email ------------------------ 1      | email1@test.com 2      | email2@test.com 3      | email3@test.com ------------------------  posts (posts created users) ------------------------ postid | userid ------------------------ 1      | 1 2      | 3 3      | 1 ------------------------  postalertusers (every time user notified new post, 1 record added here) ------------------------ postid | userid ------------------------ 1      | 2 1      | 3 2      | 1 ------------------------ 

the resulting query output data:

result (using postid , userid identify user have notified post) --------------------------------- postid | userid | email --------------------------------- 2      | 2      | email2@test.com 3      | 2      | email2@test.com 3      | 3      | email3@test.com --------------------------------- 

edit:

thanks ad.net i've came following:

from u in context.users let posts = contexto.posts.where(p => p.userid != u.userid) p in posts join pau in context.postalertusers on u.userid equals pau.userid  alerts in alerts.defaultifempty() == null || a.postid != p.postid orderby p.idpost select new {     p.postid,     u.userid,     u.email } 

edited: attempt different angle, not sure performance, might bad cross-join

       p in context.posts        let otherusers = context.users.where(u => u.userid != p.user.userid)        u in otherusers        join pau in alerts on u.userid equals pau.user.userid alert        in alert.defaultifempty()        == null || a.post.postid != p.postid        select new {p.postid, u.email}; 

here's linqpad attempt:

void main() {      var users = new list<user>{new user{userid = 1}, new user{userid = 2}};             var posts = new list<post>             {                 new post{postid = 1, user = new user{userid = 2}},                 new post{postid = 2, user = new user{userid = 1}},                 new post{postid = 3, user = new user{userid = 2}},             };             var alerts = new list<postuseralert>             {                 new postuseralert{post = new post{postid = 1},                                      user = new user{userid = 1}}             };              var result = (from p in posts                 let otherusers = users.where(u => u.userid != p.user.userid)                 u in otherusers                 join pau in alerts on u.userid equals pau.user.userid alert                 in alert.defaultifempty()                 == null || a.post.postid != p.postid                 select new {p.postid, u.userid}).tolist();                  result.dump(); }    class user     {         public int userid { get; set; }         public string email { get; set; }     }      class post     {         public int postid { get; set; }         public user user { get; set; }          public list<postuseralert>  postuseralerts { get; set; }     }      class postuseralert     {         public post post { get; set; }         public user user { get; set; }     } // define other methods , classes here