This post has been heavily edited and updated!

The Intent:

I am writing an app that is essentially a mini ASP.NET MVC 3 accounting package. I am doing it to learn EF 4.1 Code First and Scaffolding

The Setup:

I am using SQL Server 2008 Express, Visual Studio 2010 SP1 and ASP.NET MVC 3 with Mvc Scaffolding 1.0.2.

I have an existing database. The database has the following tables:

Accounts
Banks
CostCentres
Currencies
DebitCredits
People
Transactions
TransactionTypes

There are a number of relationships, eg, Person_Accounts, etc.

I now want to use MVC Scaffolding to create some input pages for creating data for the look up tables in my database.

What I tried:

I created an .edmx and used that to create the POCO classes using the t4 auto generation. Have excluded the .edmx once I have the POCO classes.

Have got round the problem of EF 4.1 Code First not finding a connection string it likes, so going off and creating its own sql express database (see Rachel Appels blog for details on this gotcha)

Have finally used the convention context name = connection string name to get EF code first to talk to the correct db.

I have then used MVC 3 Scaffolding to scaffold the views. So the repository code that is produced is not my own, but Steve Sanderson's.

I haven't used EF before, so was hoping that this would be a way of progressing from LINQ to SQL, by means of "look and learn".

Turns out, I'm having some probl开发者_如何转开发ems...

The Problem:

First of all, if I use the database created by EF Code First, no problems.

But change the connection string to my previously existing database (that I used to create the .edmx file) I now get the following error when, for example, I request the Index view scaffolded for the Accounts entity:

Invalid column name 'Account_AccountId'.
Invalid column name 'Account_AccountId'.
Invalid column name 'Currency_CurrencyId'.
Invalid column name 'Transaction_TransactionId'.
Invalid column name 'Account_AccountId1'.
Invalid column name 'Account_AccountId'.
Invalid column name 'Account1_AccountId'.
Invalid column name 'CostCentre_CostCentreId'.
Invalid column name 'Currency_CurrencyId'.
Invalid column name 'TransactionType_TransactionTypeId'.
Invalid column name 'Account_AccountId1'.
Invalid column name 'Account_AccountId2'.
Invalid column name 'Account_AccountId2'.
Invalid column name 'Account_AccountId'.
Invalid column name 'Account1_AccountId'.
Invalid column name 'CostCentre_CostCentreId'.
Invalid column name 'Currency_CurrencyId'.
Invalid column name 'TransactionType_TransactionTypeId'.
Invalid column name 'Account_AccountId1'.
Invalid column name 'Account_AccountId2'.

--Note:--

The only difference between database created by EF and the one created by me (dead simple) are the relationships and a couple of triggers, plus an EdmMetadata table.

--End of Note--

My Reasoning:

The reason for this, at a first glance, very bizarre error is that despite just wanting a list of Accounts without any of the related data, what is happening is as follows:

With either my pre existing database or the one created by code first, when I check in SQL Profiler, it shows an entry SQL: BatchStarting with a massive SELECT query that seems to be selecting just about everything in the database. I have no idea why this massive query is called as opposed to a simple select for the Transaction data. Presumably, it is trying to load all related data, but I haven't asked for that.

Again, I stress that using the code first generated db, it all works. But using my pre existing db, it throws the error show above.

Two problems here:

1. One is the error. Why does it happen??

2. The other is that huge select statement for just about all the data in the db!!

My view is only trying to spit out a list of Accounts records. I have no interest (for this view) in the CostCentres or Currencies tables, etc, etc.

The Questions:

a. Why is the Scaffolded repository asking for ALL the data?

b. Why is the error happening in the pre existing database?

I have set a bounty on this question, and whoever answers the above two questions will get the bounty.

Other questions (not related to the bounty!):

c. Does anyone know of links to blogs where I can read up on what I should do to use MVC 3 scaffolding and code first with an existing database?

d. Is there a way to use the t4 templates to create a DbContext file that correctly maps to the existing database, with all its relationships, etc?

e. Any other suggestion (excluding career change)?

f. Any books for reading up on EF 4.1 Code First? (Julia Lerman's latest is EF 4.0, ie, Code first was only at beta at time of publication).

Update:

I have answered question a (Why the huge query bringing in all the data. The scaffolded repository has a method:

public IQueryable<Account> AllIncluding(params Expression<Func<Account, object>>[] includeProperties)
        {
            IQueryable<Account> query = context.Accounts;
            foreach (var includeProperty in includeProperties) {
                query = query.Include(includeProperty);
            }
            return query;
        }

That gets called from the Scaffolded controller:

//
        // GET: /Accounts/

        public ViewResult Index()
        {
            return View(accountRepository.AllIncluding(account => account.Person, account => account.DebitCredits, account => account.Transactions, account => account.Transactions1));
        }

My apologies. I was too bamboozled.

But question b remains unanswered.

question b: the reason you are receiving the error on the preexisting database columns is that they don't match the column naming conventions that codefirst uses to setup its own relationships to use the database as it exists you need to use fluent mapping or column attributes on your models to define what the relationships should be

the best way to do this for a code first api setup is not to use an edmx to generate your classes use the entity framework power tools ctp to do this instead

i would also try to do this by hand your first couple times after generating it to learn the fluent api and how it works

as there are several different patterns you can use with it

to answer your question about bringing in all the data it does not do this every time it does this once at compile time to create a datafile of everything in the database then tracks it for changes after that to improve performance so it doesnt have to hit the data base every time

question a: the AllIncluding function is there to allow you to query the data if you dont want this then remove it and replace it with how want to query the data

question d:

you can write your own t4 files to generate what ever code you want they are templates and designed specifically for this purpose

question f:

there no 4.1 specific books out that i know of as of yet as the codefirst framework is brand new i have only found ones on version 4.0 however there are 2 mvc 3 books out currently that i do know of the do cover some aspects of the codefirst framework which are professional asp.net mvc 3 (Wrox) and pro asp.net mvc 3 (apress) and are both available on amazon for about 35-40 bucks each