2

I have developed a quite nice web-app using EF 5 and code first. But while running benchmarks I found that the performance was not as good as I wanted... looking further I kinda figured out that all the queries that EF generates are similar to Select * From and that is not best practise.

Reading this answer here Select Specific Columns from Database using EF Code First I understood that I could generate a view and map it to a entity. My question is how do I map a view to a entity or vice-versa using EF 5 code first?

The reason I'm asking this is: I have a very wide table on which I perform "preliminar search" search items by name and then go back for the rest of it on one case... in another I have a big table and most of the time I only use the Title and Description and not the LOB column... in all thouse cases Im getting something from the database Im not using...

So if I could indeed map a view to a entity or vice-versa I could save alot of bandwith between backbone and application tier...

Community
  • 1
  • 1
Leonardo
  • 10,737
  • 10
  • 62
  • 155

2 Answers2

1

It's not the same thing you're talking about - i.e. not an exact answer - but it's addressing performance, via what EF calls 'views'.

I'd suggest you try out the EF Power Tools - and 'Generate Views'.

By running that - the 'views' file is added to the project - which is a .cs one - and that enhances the core EF performance (this is an EF feature, not the code-first - but with power-tools we can now use it with code-first as well).

It doesn't add the 'Db views' - but as far as I can tell - it works by pre-analyzing and code-generating the SQL templates.

"Before the Entity Framework can execute a query against a conceptual model or save changes to the data source, it must generate a set of local query views to access the database. The views are part of the metadata which is cached per application domain. If you create multiple object context instances in the same application domain, they will reuse views from the cached metadata rather than regenerating them. Because view generation is a significant part of the overall cost of executing a single query, the Entity Framework enables you to pre-generate these views and include them in the compiled project. For more information, see Performance Considerations (Entity Framework)."
http://msdn.microsoft.com/en-us/library/bb896240.aspx

I could 'feel' a boost in performance.

Notes:
There are couple issues with it - and you might get some exceptions running it the first time:

  • Make sure your class is the only context in the file (it takes the first one),
  • I had to move the project out of a 'solution dir' (that is a trick I learned from power-shell console - which required the same)

Also, any other attempts to manually 'tweak' the Db with the 'real' views - would be futile I think, as it isn't closely integrated w/ the ORM (you need more then one - and matching calls etc.).

NSGaga-mostly-inactive
  • 14,052
  • 3
  • 41
  • 51
0

the way I achieve that is not very clean but:

  • I create a type
  • declare a dbset for the type
  • drop the table in the db if necessary
  • create a view named as the dropped table with the same field (type and name).

Of course all that is encapsulated in the seed method.

Not clean but running. I think some trouble is to come if you want to "migrate" the structure of the view. But this way all his nearly as if you get an entity. Of course insertion and update may be touchy, but this is not my purpose.

if you respect the naming convention even the loading strategies are available.

tschmit007
  • 7,559
  • 2
  • 35
  • 43