We've started using EF6 as part of rewriting our application suite. There are many perfectly reasonable tables in the existing suite and we're reusing them using a database-first approach. My problem is that EF6 seems to be enforcing what I think are code-first conventions on my database-first model.
Consider this minimal example with two tables defined thusly and appropriately populated with a few rows:
CREATE TABLE [dbo].[Table1] (
[Id] INT NOT NULL PRIMARY KEY,
[Table2Reference] INT NOT NULL REFERENCES [dbo].[Table2](Id) )
CREATE TABLE [dbo].[Table2] (
[Id] INT NOT NULL PRIMARY KEY,
[SomeColumn] NVARCHAR(25) )
After running Update Model From Database we get this model:
(Oops. Not enough reputation to post images. It's what you would imagine.)
So far so good, but when you write code to access the Table1 entity, like so...
var q = _context.Table1.ToList();
foreach (var item in q)
Debug.WriteLine("{0}", item.Table2Reference);
... it compiles fine but will throw on the ToList() line. This is because the SQL generated contains a request for a column that doesn't even exist:
SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Table2Reference] AS [Table2Reference],
[Extent1].[Table2_Id] AS [Table2_Id] <-- this one doesn't exist
FROM [dbo].[Table1] AS [Extent1]
I gather this has something to do with a code-first naming convention for foreign keys. I know I can rename Table2's Id column to Table2Id and rename Table2Reference to Table2Id and it will work. However, this is supposed to be database-first. Is there some way to tell EF to get out of the way and just go with what is actually in the pre-defined database? I did discover early on that I had to turn off the name pluralizing convention, but I can't seem to identify a convention to turn off that fixes this problem. I tried removing these:
modelBuilder.Conventions.Remove<PrimaryKeyNameForeignKeyDiscoveryConvention>();
modelBuilder.Conventions.Remove<TypeNameForeignKeyDiscoveryConvention>();
modelBuilder.Conventions.Remove<NavigationPropertyNameForeignKeyDiscoveryConvention>();
Anyway, I'm stumped. Is there an easy workaround that doesn't involve modifying the existing database?
Thanks for reading.
It turns out that there is a very important piece to a database-first approach besides having an EDMX file. That is, your connection string must contain the following section:
metadata=res:///IPE.csdl|res:///IPE.ssdl|res://*/IPE.msl; (replacing IPE with the base name of your EDMX)
Otherwise, EF will be unable to locate the EDMX information in the assembly and code-first conventions can come into play. Mostly things just work, until they don't.