Modelling pivoted 3NF data using EF4.0 / LINQ

I'm currently working on a project that has almost all of its business logic encapsulated in the database (S开发者_C百科QL Server) and now have a requirement to remove the database dependency entirely.

I've got some normalised data in a database in the form:

Record [RecordID, RecordTypeID, OwnerID, RecordValue] RecordType [RecordTypeID, Description]

with a foreign key between RecordTypeIDs. What I want to do is strongly type the record set; there are queries in the database that PIVOT the data so I get a record set as:

TypedRecord [RecordType1Value, RecordType2Value, RecordType3Value...]

but obviously can't use the PIVOT command in this project. Can anyone suggest an appropriate strategy for tackling this? We'll be using .NET 4.0 (inc EF4.0) so linq solutions would be fine.

It is nice idea to remove "database dependency" but obviously you have heavy data oriented application so you should consider some aspects. Even with EF it is still useful to use SQL for advanced queries - PIVOT is such example. Don't remove these queries or you will have to compute pivoted data directly in memory of your application = transfer all related data from db to application and compute aggregations for pivoting.

EF4 offers several ways to call tranditional SQL. The way to go is probably encapsulating your queries into stored procedures and use them.