CODEWITHSUNDEEP
entity-framework-4.1
dotnetnoob
dotnetnoob

Reputation: 11340

Advice required - using entity framework with normalised data

I've recent gone through the process of revamping my database, normaising a lot of entities. Obviously I now have a few more tables than I had. A lot of data I use on the website is readonly so this is simple to denormalise using a view, however there are entities that could benefit from denormalised retrieval but still need to be updated.

Here's an example.

A User may be a Member
A Member may have a Profile
A Member may have an Account

In addition I have 3 further lookup tables.

In total there are 3 tables for User and 4 tables for Member.

Ideally, I can create 2 views from the above tables.

However, User needs to be updated as do the entities belonging to Member. Additionally there are 6 separate tables associated with Users/Members, i.e. FavouriteCategories that also need to be retreived and updated from time to time.

I'm struggling to come up with the best, most efficient way of doing this.

I could simply not use views and bring all the entities and lookups into the model, but I would be reliant on EF to produce the retreival queries. The stuff I've read suggest that EF is not best at dealing with joined data.

I could add both the view and tables, using the tables for updates only. This seems sloppy due to the duplication, complication of the model, as well as underutilising the EF model functionality.

Maybe I could use the readonly view for data retrieval and create stored procs. I believe that the process of using EF with stored procs is a bit of a hack, so I'd probably keep the stored procs distinct from EF and simply pass params and call the SP via traditional methods. This again seems like a bit of a halfway house.

I'm not that experienced with .net or EF, so would appreciate some solid advice on either the methods I've referred to above or any better technique to acheive this. I don't want to go hacking the edmx file at this stage because... well it's just wrong.

I have a few entities that would benefit from the right solution. The User example is amongst the simplest, so there's a lot to gain from the right approach.

Help and advice would be very much appreciated.

Upvotes: 1

Views: 106

Answers (1)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364279

Do you want to use EF? If yes use either first approach with not using views at all and allowing EF to handle everything or the last approach with using views and mapping stored procedures for insert, update and delete operations.

Combining mapped views for reading and mapped tables for modifications is possible as well but it is mostly the first solution (allowing EF to handle everything) with additional views for some query optimization.

You will not find cleaner approaches. Are mentioned approaches are valid solution for your problem. The only question is if you want to write SQL yourselves (view and stored procedures) or let EF to do that.

The worst approach is using EF for querying and manual calling of stored procedures for updating but in some cases it can be also useful.

Upvotes: 1

Related Questions