Can this EF query be optimized?

I'm new to Entity Framework and uncertain as to when to use eager loading and Include, when to use LINQ queries with Select, and when to load entities on demand. I also don't know if and when EF caches entities. (Yes, I'll read the docs eventually :-)

I'm building a messagin开发者_StackOverflow社区g system internal to ASP .NET MVC website, and performance is going to be really important. Imagine the following scenario.

When Rihanna goes into her inbox, she needs to see a list of messages, each with sender's name and picture. Imagine John wrote Rihanna lyrics to his new song. In her inbox, there is a message with John's picture. When John goes into his outbox, he sees the same message with Rihanna's name and picture.

So, when we grab a message from the database, we might need both sender and recipient profile pictures. Imagine the following query:

private static Func<MyEntities, int, Message> _getMessageByIdQuery = CompiledQuery.Compile(
    ( MyEntities ctx, int messageId ) => ctx.Messages
        .Include( "SenderUser" )
        .Include( "SenderUser.UserProfile.Image" )
        .Include( "RecipientUser" )
        .Include( "RecipientUser.UserProfile.Image" )
        .SingleOrDefault( message => message.ID == messageId ) );

Will this query be efficient? Is there any way to load SenderUser.UserProfile.Image without loading SenderUser, considering User.ID and UserProfile.UserID are one-to-one? I imagine I would need to use LINQ JOIN's for this. Is this correct?

Is it more efficient to use LINQ to avoid loading all fields when only one is needed (e.g. only load ImageID from UserProfile)?

Is it better to do the same using Load somewhere in code?

Does such query go to the database each time? Imagine we had a thousand messages, all from the same person. Would we query for his Image each time?

I'm using EF 4 and ASP .NET MVC 3.

Thanks.

One way would be to send custom display/data objects directly from the query, that way you do not need to load extra objects. Example:

var dto = ctx.Messages
          .Where(whereclause)
          .Select(m=> new DTOTYPE
                         {Id = m.Id, 
                          Content= m.Content, 
                          SenderId = m.SenderUser.Id, 
                          SenderImage = m.SenderUser.UserProfile.Image, 
                          SenderImageId = m.SenderUser.UserProfile.ImageId}) 
                         //more fields if you need any

This will depend on your scenario though, but this way you do not have to do crazy includes all over. But you'll need to manage your view/data objects as they might get out of hand for many different screens and views. Hope this helps.

Use projection as proposed with @AD.Net. When using projection you can transfer data you really need instead of whole objects full of properties which you never use. Be aware that this really loads pictures each time you query the database for the message.

If you plan to show more messages from the same user load the image only once in separate query! Moreover is it really necessary for the recipient to load his image? If yes why not load the image only once when he logs in and keep it for example in session instead of loading same data for every message in her inbox?

Nobody can tell you how your code will perform, you will have to benchmark. Yes you can avoid loading SenderUser if you use a join. Look here if you want to know how to see the generated sql code.