Entity Framework Join without a foreign key

Question

I have a message table in which there is an identifier of the user who sent it on each line. But I can not edit the database, and there are no foreign keys. Can I join without any relationship?

var msgs = (from m in dbContext.messages join a in dbContext.users on m.userid equals a.id into sender where (m.date > LastReceivedDate) orderby m.date select new { Sender = sender.FirstOrDefault(), Message = m }) 

Here is my code and it works, but never returns anything. When I participate, I get results.

thanks

Answer 1

Yes it is possible. Look at the generated SQL; this should give you the key to the problem.

Answer 2

In addition to my comment on Craig Stuntz's answer , you can get a second request and eliminate all of them coming back in one request if you refuse .FirstOrDefault() and can deal with the sender, possibly null if the connection does not invoke users.

 var msgs = (from m in dbContext.messages join a in dbContext.users on m.userid equals a.id into sender where (m.date > LastReceivedDate) orderby m.date select new { Sender = sender, Message = m }) 

Answer 3

joining without common attributes is a cross product of both objects. Here's how to make a cross product using a LINQ query:

http://alitarhini.wordpress.com/2010/11/20/114/