Help with Linq to Entities simple subquery

Question

I am learning Entity Framework and I am trying to get the following scenario to work.

I have a Person class and a Message class. The message class has a From property and a To property, both of type Person. I want to retrieve a list of Messages using Linq to Entities. My DB tables are Message and Person. Message has columns From and To of type int pointing to the PK ID of the Person table.

Below is the code I have so far to populate the queryable essage list. So my issue here is loading the Person data. How can I go about doing this in the most efficient way. Any explanations regarding the method would be greatly appreciated.

var messages = from m in _entities.Message
                           select new BizObjects.Message
                           {
                               MessageId = m.MessageId,
                               From = new BizObjects.Person
                               {
                                   PersonId = m.From
                               },
                               To = new BizObjects.Person
                               {
                                   PersonId = m.To
                               },
                               Subject = m.Subject,
                               Content = m.Content,
                               Read = m.Read,
                               CreatedOn = m.CreatedOn,
                           };

Please let me know if you need more code or background information. Thanks in advance for your help.

Answer 1

You should have associations between Message and Person. Since the EF designer creates them automatically, you probably already do. However, since they will be generated from foreign keys instead of from column names, the automatically-created associations were probably created with less-than-useful names, like Message.Person1 or something. You can select them in the designer and rename them to a more useful name.

Let's presume your associations are called Message.FromPerson and Message.ToPerson. You'll then be able to write queries like:

        var messages = from m in _entities.Message
                       select new BizObjects.Message
                       {
                           MessageId = m.MessageId,
                           From = new BizObjects.Person
                           {
                               PersonId = m.FromPerson.PersonId,
                               PersonName = m.FromPerson.PersonName
                           },
                           To = new BizObjects.Person
                           {
                               PersonId = m.ToPerson.PersonId,
                               PersonName = m.ToPerson.PersonName
                           },
                           Subject = m.Subject,
                           Content = m.Content,
                           Read = m.Read,
                           CreatedOn = m.CreatedOn,
                       };

By using the association properties in my LINQ to Entities query, I am causing the Entity Framework to transliterate this reference into a LEFT OUTER JOIN in SQL.

This is a very efficient way to load the data (especially in contrast to lazy loading, which causes multiple database queries for far too many columns), since you only select the columns you actually need and it is all done in one query.