ASP.NET MVC & EF4 Entity Framework - Are there any performance concerns in using the entities vs retrieving only the fields i need?

Lets say we have 3 tables, Users, Products, Purchases. There is a view that needs to display the purchases made by a user.

I could lookup the data required by doing:

from p in DBSet<Purchases>.Include("User").Include("Product") select p;

However, I am concern that this may have a performance impact because it will retrieve the full objects. Alternatively, I could select only the fields i need:

from p in DBSet<Purchases>.Include("User").Include("Product") select new SimplePurchaseInfo() { UserName = p.User.name, Userid = p.User.Id, ProductName = p.Product.Name ... etc };

So my question is: Whats the best practice in doing this?

== EDIT

Thanks for all the replies.

[QUESTION 1]: I want to know whether all views should work with flat ViewModels with very specific data for that view, or should the ViewModels contain the entity objects.

Real example: User reviews Products

var query = from dr in productRepository.FindAllReviews()
            where dr.User.UserId = 'userid'
            select dr;
string sql = ((ObjectQuery)query).ToTraceString();

SELECT [Extent1].[ProductId] AS [ProductId], 
       [Extent1].[Comment] AS [Comment], 
       [Extent1].[CreatedTime] AS [CreatedTime], 
       [Extent1].[Id] AS [Id], 
       [Extent1].[R开发者_Python百科ating] AS [Rating], 
       [Extent1].[UserId] AS [UserId], 
       [Extent3].[CreatedTime] AS [CreatedTime1], 
       [Extent3].[CreatorId] AS [CreatorId], 
       [Extent3].[Description] AS [Description], 
       [Extent3].[Id] AS [Id1], 
       [Extent3].[Name] AS [Name], 
       [Extent3].[Price] AS [Price], 
       [Extent3].[Rating] AS [Rating1], 
       [Extent3].[ShopId] AS [ShopId], 
       [Extent3].[Thumbnail] AS [Thumbnail], 
       [Extent3].[Creator_UserId] AS [Creator_UserId], 
       [Extent4].[Comment] AS [Comment1], 
       [Extent4].[DateCreated] AS [DateCreated], 
       [Extent4].[DateLastActivity] AS [DateLastActivity], 
       [Extent4].[DateLastLogin] AS [DateLastLogin], 
       [Extent4].[DateLastPasswordChange] AS [DateLastPasswordChange], 
       [Extent4].[Email] AS [Email], 
       [Extent4].[Enabled] AS [Enabled], 
       [Extent4].[PasswordHash] AS [PasswordHash], 
       [Extent4].[PasswordSalt] AS [PasswordSalt], 
       [Extent4].[ScreenName] AS [ScreenName], 
       [Extent4].[Thumbnail] AS [Thumbnail1], 
       [Extent4].[UserId] AS [UserId1], 
       [Extent4].[UserName] AS [UserName]
       FROM    [ProductReviews] AS [Extent1]
       INNER JOIN [Users] AS [Extent2] ON [Extent1].[UserId] = [Extent2].[UserId]
       LEFT OUTER JOIN [Products] AS [Extent3] ON [Extent1].[ProductId] = [Extent3].[Id]
       LEFT OUTER JOIN [Users] AS [Extent4] ON [Extent1].[UserId] = [Extent4].[UserId]
       WHERE N'615005822' = [Extent2].[UserId]

or

from d in productRepository.FindAllProducts()
from dr in d.ProductReviews
where dr.User.UserId == 'userid'
orderby dr.CreatedTime
select new ProductReviewInfo()
       {
           product = new SimpleProductInfo() { Id = d.Id, Name = d.Name, Thumbnail = d.Thumbnail, Rating = d.Rating },
           Rating = dr.Rating,
           Comment = dr.Comment,
           UserId = dr.UserId,
           UserScreenName = dr.User.ScreenName,
           UserThumbnail = dr.User.Thumbnail,
           CreateTime = dr.CreatedTime
       };

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Name] AS [Name], 
[Extent1].[Thumbnail] AS [Thumbnail], 
[Extent1].[Rating] AS [Rating], 
[Extent2].[Rating] AS [Rating1], 
[Extent2].[Comment] AS [Comment], 
[Extent2].[UserId] AS [UserId], 
[Extent4].[ScreenName] AS [ScreenName], 
[Extent4].[Thumbnail] AS [Thumbnail1], 
[Extent2].[CreatedTime] AS [CreatedTime]
FROM    [Products] AS [Extent1]
INNER JOIN [ProductReviews] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ProductId]
INNER JOIN [Users] AS [Extent3] ON [Extent2].[UserId] = [Extent3].[UserId]
LEFT OUTER JOIN [Users] AS [Extent4] ON [Extent2].[UserId] = [Extent4].[UserId]
WHERE N'userid' = [Extent3].[UserId]
ORDER BY [Extent2].[CreatedTime] ASC

[QUESTION 2]: Whats with the ugly outer joins?

---

In general, only retrieve what you need, but keep in mind to retrieve enough information so your application is not too chatty, so if you can batch a bunch of things together, do so, otherwise you'll pay network traffic cost everytime you need to go back to the database and retrieve some more stuffs.

In this case, assuming you will only need those info, I would go with the second approach (if that's what you really need).

---

Eager loading with .Include doesn't really play nice when you want filtering (or ordering for that matter).

That first query is basically this:

select p.*, u.*, p2.*
from products p
left outer join users u on p.userid = u.userid
left outer join purchases p2 on p.productid = p2.productid
where u.userid == @p1

Is that really what you want?

There is a view that needs to display the purchases made by a user.

Well then why are you including "Product"?

Shouldn't it just be:

from p in DBSet<Purchases>.Include("User") select p;

Your second query will error. You must project to an entity on the model, or an anonymous type - not a random class/DTO.

To be honest, the easiest and most well performing option in your current scenario is to query on the FK itself:

var purchasesForUser = DBSet<Purchases>.Where(x => x.UserId == userId);

That should produce:

select p.*
from products p
where p.UserId == @p1

The above query of course requires you to include the foreign keys in the model.

If you don't have the FK's in your model, then you'll need more LINQ-Entities trickery in the form of anonymous type projection.

Overall, don't go out looking to optimize. Create queries which align with the scenario/business requirement, then optimize if necessary - or look for alternatives to LINQ-Entities, such as stored procedures, views or compiled queries.

Remember: premature optimization is the root of all evil.

*EDIT - In response to Question Update *

[QUESTION 1]: I want to know whether all views should work with flat ViewModels with very specific data for that view, or should the ViewModels contain the entity objects.

Yes - ViewModel's should only contain what is required for that View. Otherwise why have the ViewModel? You may as well bind straight to the EF model. So, setup the ViewModel which only the fields it needs for the view.

[QUESTION 2]: What's with the ugly outer joins?

That is default behaviour for .Include. .Include always produces a left outer join.

---

I think the second query will throw exception because you can't map result to unmapped .NET type in Linq-to-entities. You have to return annonymous type and map it to your object in Linq-to-objects or you have to use some advanced concepts for projections - QueryView (projections in ESQL) or DefiningQuery (custom SQL query mapped to new readonly entity).

Generally it is more about design of your entities. If you select single small entity it is not a big difference to load it all instead of projection. If you are selecting list of entities you should consider projections - expecially if tables contains columns like nvarchar(max) or varbinar(max) which are not needed in your result!

---

Both create almost the same query: select from one table, with two inner joins. The only thing that changes from a database perspective is the amount of fields returned, but that shouldn't really matter that much.

I think here DRY wins from a performance hit (if it even exists): so my call is go for the first option.