Database and logic layer for ASP.NET MVC application

Question

I'm going to start a new project which is going to be small initially but may grow to big over the years. I'm strongly convinced that I'm going to use ASP.NET MVC with jQuery for UI. I want to go for MySQL as database for some reasons but worried on few things.

I'm totally new to Linq but it seems that it is easier to use once you are familiar with it.

First thing is that accessing data should be easy. So I thought I should use MySQL to Linq but somewhere I read that it is not directly supported but MySQL .NET connector adds support for EntityFramework. I don't know what are the pros and cons of it. DbLinq is what I also heard. I would love if I can implement repository pattern as it allows to apply filter in logic layer rather than in data access layer. Will it be possible if I use Entity Framework?

I'm also concerned about the performance. Someone told me that if we use Entity framework it fetches lot of data and then filter it. Is that right?

So questions basically are -

1. Is MySQL to Linq possible? If yes where can I get more details on it?
2. Pros and cons of using EntityFramework or DbLinq with MySQL?
3. Will it be easy to access data using EntityFramework or DbLinq with MySQL?
4. Will I be able to implement repository pattern which allows applying filter in logic layer rather than data access layer (when I use EntityFramework with MySQL)
5. Does it fetches hell lot of data from database and then apply filter on it?

If it sounds too many questions from my side in that case, if you can just let me know what you will do (with a considerable reason) in this situation as an experienced person in this area, that should answer my question.

Answer 1

As I am fan of ALT.NET I would recomend you to use NHibernate for your project instead of EntityFramework, you may google for the advantages over it, I am convinced you'll choose it.

Answer 2

Based on the points you've mentioned, then I would seriously consider going with MS SQL instead of MySQL initially and implementing LINQ-to-SQL instead of Entity Framework, and here's why:

1. The fact that you are anticipating a lot of traffic initially tells me that you need to think about where you plan to end up, rather than where to start. I have considerably more experience with MS SQL than I do with MySQL, but if you're talking about starting with the community version of MySQL and upgrading later, you're going to be incurring a significant expense anyway with the Enterprise version.
2. I have heard there is a version of LINQ that supports MySQL, but, unless things have changed recently, it is still in beta. I am completing an 18-month web-based project that used ASP.NET MVC 1.0, LINQ-to-SQL, JavaScript, jQuery, AJAX, and MS SQL. I implemented the repository pattern, view models, interfaces, unit tests and integration tests using WatiN. The combination of technologies worked very well for me, and I plan to go with the same combination for a personal project I'm developing.
3. When you get MS SQL with a hosting plan, you typically have the ability to create multiple databases from that single instance. It looks like they give you more storage because they give you multiple MySQL databases, but that's only because the architecture only supports the creation of one database per instance.
4. I won't use the Entity Framework for my ASP.NET MVC projects, because I wasn't crazy about ADO.NET in the first place. I don't want to have to open a connection, create a command object, populate a parameter collection, issue the execute method, and then iterate through a one-way reader object to get my data. Once you see how LINQ-to-SQL simplifies the process, you won't want to go back either. In the project I mentioned earlier, I have over 60 tables in the database with about 200 foreign key relationships. Because I used LINQ-to-SQL with the repository pattern in my data layer, I was able to build the application using not a single stored procedure. LINQ-to-SQL automatically protects against SQL injection attacks and support optimistic and pessimistic concurrency checking.

I don't know what your project is, but you don't want to get into a situation where you're going to have trouble scaling the application later. Code for the end result, not for the starting point, and you'll save yourself a lot of headaches later.