CODEWITHSUNDEEP
.netmysqlentity-framework.net-4.0
Matt
Matt

Reputation: 1582

Entity Framework Join without foreign key

I have a messages table which has an id of the user that sent it in each row. But I cannot edit the database, and there are no foreign keys. Is it possible to join without any relationships?

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
           })

Thats my code, and it runs, but never returns anything. When I take the join away I get results.

Thanks

Upvotes: 5

Views: 12691

Answers (1)

Jesse C. Slicer
Jesse C. Slicer

Reputation: 20157

In addition to my comment on Craig Stuntz's answer, you can get the 2nd query eliminated and have it all returned in a single query if you forgo the .FirstOrDefault() and can deal with Sender possibly being null if the join turns up no 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
                        })

Upvotes: 3

Related Questions