Help with Linq to Entities simple subquery -

i learning entity framework , trying following scenario work.

i have person class , message class. message class has property , property, both of type person. want retrieve list of messages using linq entities. db tables message , person. message has columns , of type int pointing pk id of person table.

below code have far populate queryable essage list. issue here loading person data. how can go doing in efficient way. explanations regarding method appreciated.

var messages = m in _entities.message                            select new bizobjects.message                            {                                messageid = m.messageid,                                = new bizobjects.person                                {                                    personid = m.from                                },                                = new bizobjects.person                                {                                    personid = m.to                                },                                subject = m.subject,                                content = m.content,                                read = m.read,                                createdon = m.createdon,                            }; 

please let me know if need more code or background information. in advance help.

you should have associations between message , person. since ef designer creates them automatically, do. however, since generated foreign keys instead of column names, automatically-created associations created less-than-useful names, message.person1 or something. can select them in designer , rename them more useful name.

let's presume associations called message.fromperson , message.toperson. you'll able write queries like:

        var messages = m in _entities.message                        select new bizobjects.message                        {                            messageid = m.messageid,                            = new bizobjects.person                            {                                personid = m.fromperson.personid,                                personname = m.fromperson.personname                            },                            = 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 association properties in linq entities query, causing entity framework transliterate reference left outer join in sql.

this efficient way load data (especially in contrast lazy loading, causes multiple database queries far many columns), since select columns need , done in 1 query.