Design choice: Entity Framework vs Raw SQL

I am writing a Silverlight application to process millions of records. Since the load on the server can be quite heavy, I intend to shift some of the processing to the client side. For example, if I needed to show complete information about a book and its author, I would w开发者_如何学JAVAant to JOIN the Book table and the Author table using AuthorID as the common key. Instead of performing the JOIN on the server side, I would retrieve Author table separately and Book table separately and join them on the client side using C# code and display them in a DataGrid.

The clients are in a controlled environment so there is a minimum guarantee of their performance. Since there are so many records and since there are no updations taking place in the client-side DataGrids, I would choose Raw SQL over Entity Framework. However, there is the issue of doing the client-side join. My question is, would Entity Framework help make the client-side join easier? Consider development time to not be a significant factor here.

My question is, would Entity Framework help make the client-side join easier?

It's a matter of who's developing the application. Using the entity framework definitely has it's advantages. It gives developers the ability to write T-SQL "like" syntax within a C# or other .NET environment, but it has the same efficiency of executing SQL through the SQL libraries that already exist in .NET.

So easier? Maybe. More efficient? I doubt it. Usually you want to perform JOINS and things of the sort within stored procedures on the server side, only because the database is usually stored on a different machine, so why have one machine serve the website AND do the data manipulation you need?

That's my view on the whole thing. I know some people are starting to see the need to NOT write stored procs with everything becoming so easy to do within .NET, but I still like them.

I would personally agree with your choice to user raw SQL in this case, at least basing on what I read in your post. You need as "fast" access as you can, and fast being performance objective, is very your app dependent. So you can tune exactly what you want in place where you want.

I'm talking about even client side, where you want to do joins, considering that you wrote: "development time to not be a significant factor here".

So I'm personaly vote for Raw SQL.

Regards.

Databases are particularly designed to handle massive amount of data. Servers are intended to handle such scenarios. Sending so much data to the client would easily affect performance badly. It is better to filter the data in the server, and then send it to the client. Servers were designed for this exact purpose.

As for Raw SQL vs. Entity Framework - if you choose to filter the data in the server and you need to do special optimizations - go with raw SQL, as it is much less limited. Otherwise, go with Entity Framework.

I don't know about Entity Framework versus Raw SQL but have you considered using Microsoft's Caching Application Block with a backing store cache?

This will cache data locally - which sounds kind of like what you are you are after.