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 problems...
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:
One is the error. Why does it happen??
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.
