1

If I want to select only few columns when retrieving data for an EF entity and cast them to the Entity type, I am not able to do that because it throws an error as mentioned in this post The entity cannot be constructed in a LINQ to Entities query. I don't want to select all the columns, because I need only few of them. I can use anonymous types, but if I am using repository pattern and want to encapsulate all data access code in repository object and pass strongly typed object collection to the controller (not an anonymous object collection), how can I achieve that? Is the only option to define a DTO object for every subset of the properties for the EF entity? I know there is a risk of losing data with partial loaded entities, but if I am ready to take the risk and want full control over data updates, is that not possible?

for example I would like the "ProductRepository" method signature to be like this

public IEnumerable<Product> GetProducts(int categoryID) //selection of subset of data

and I want to pass this product collection from the controller to the view (in ASP.NET MVC project) and in the view I want to have strongly typed model (with intellisense) object. Is this possible? if not, I may have to reconsider using EF for my project due to this limitation. I am using EF 4.1 version.

Community
  • 1
  • 1
RKP
  • 5,285
  • 22
  • 70
  • 111

2 Answers2

1

Yes, what you want is totally possible using viewmodels instead of entities. Here is example controller code:

var productEntities = productRepos.GetProducts(6);
var productViewModels = Automapper.Mapper
    .Map<IEnumerable<ProductViewModel>>(productEntities);
return View(productViewModels);

Your view model will have only the properties it needs for the view. Check out automapper.

danludwig
  • 46,965
  • 25
  • 159
  • 237
  • but in this code, I will have to select all the columns from the Product table and then create a new class called "ProductViewModel" with subset of columns. what I wanted was to pass the partial loaded entity from the repository to the controller and from there to the view directly. – RKP Dec 21 '11 at 12:54
  • Why? Are you worried about network performance of too much data being passed from the db? – danludwig Dec 21 '11 at 12:58
  • 3
    yes, for small tables it doesn't matter, but for large tables selecting everything from the table may cause performance issues and why select everything when only few of the columns are needed? defining a DTO for every subset is more work, but there doesn't seem to be another option at the moment except the workaround suggested in another answer. – RKP Dec 22 '11 at 10:33
1

Yes the option in this case is special object for each subset of properties you want to select. You can call the object DTO because it is just a result of the projection. This is the correct approach because if your UI doesn't need other properties of entity type it is correct to pass it only specialized ViewModel.

Another more complex (and worse) option is selecting anonymous type inside your Linq-to-entities query, calling ToList and after that construction the real entity type. Partial entity selection is not allowed and projecting to mapped entity types is not allowed as well. That is the reason why you have to use such a cumbersome approach. Example:

// Select anonymous projection
var query = from x in context.Entities
            where ...
            select new { ... };

// Repopulate entity type
var reultSet = query.ToList().Select(x => new Entity { ... });
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • Thanks for the reply. it works, but populating an anonymous object collection first and then populating the entity collection from it is inefficient. I would rather have a DTO for every subset of the properties, though it is a code bloat for me. selecting all the columns from the table is also inefficient (may cause performance issues with large tables) and if I am interested in only some of them, it doesn't make sense to select everything from the table. – RKP Dec 22 '11 at 10:18
  • The ViewModels are normally defined in UI layer and if I need to cast it to the ViewModel in repository layer, then I will have to define them in another project, because repository layer can't reference the UI layer. – RKP Dec 22 '11 at 10:20
  • ViewModels are normally defined in UI but your DTO from repository layer can be your ViewModel. The solution with selecting anonymous types and after that casting it back to entity type doesn't have any inefficiency which you should bother with. It is just more complex. – Ladislav Mrnka Dec 22 '11 at 10:43