EF Designer ignores my foreign key

[Adrian T.]

I have two simple tables that are linked by a foreign key. When I add
the tables to the EDM using the wizard there is the association and
naviagtion properties that I would expect to see are not there. Below
is the script that I am using to create the database tables.

------------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[ItemGroups](
[CompanyId] [int] NOT NULL,
[ItemGroupId] [smallint] NOT NULL,
[ItemGroupName] [nvarchar](30) NOT NULL,
CONSTRAINT [PK_ItemGroups] PRIMARY KEY CLUSTERED
(
[CompanyId] ASC,
[ItemGroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Items](
[CompanyId] [int] NOT NULL,
[ItemId] [int] NOT NULL,
[ItemName] [nvarchar](40) NOT NULL,
[ItemGroupId] [smallint] NOT NULL,
CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED
(
[CompanyId] ASC,
[ItemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Items] WITH CHECK ADD CONSTRAINT
[FK_Items_ItemGroups] FOREIGN KEY([CompanyId], [ItemGroupId])
REFERENCES [dbo].[ItemGroups] ([CompanyId], [ItemGroupId])

GO

 

[Cowboy \(Gregory A. Beamer\)]

Oh this is beautiful.

First, make sure it is truly ignoring your foreign key. In Entity Framework,
foreign keys are encapsulated in their own object. Here is a small bit of
code you can use to test this:

//Replace {EntityName} with name of entity
EntityKeyReferenceOf{EntityName} er = new
EntityKeyReferenceOf{EntityName}();

foreach(EntityKey key in er.EntityKeys)
{
Console.WriteLine(key.EntitySetName);

foreach(EntityKeyMember member in key.EntityKeyValues)
{
Console.WriteLine("\t{0} = {1}", member.Key, member.Value);
}
}

You may find the value is there. This was one of the reasons I abandoned the
Entity Framework in my latest work.
http://gregorybeamer.spaces.live.com/blog/cns!B036196EAF9B34A8!806.entry

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://feeds.feedburner.com/GregoryBeamer#

or just read it:
http://feeds.feedburner.com/GregoryBeamer

********************************************
| Think outside the box! |
********************************************

 

[Adrian T.]

I managed to find what went wrong. I did some searches in the edmx
file and finally I found this message: "warning 6030: The relationship
'FK_Items_ItemGroupId' uses the set of foreign keys '{CompanyId,
ItemGroupId}' that are partially contained in the set of primary keys
'{CompanyId, ItemId}' of the table 'Items'. The set of foreign keys
must be fully contained in the set of primary keys, or fully not
contained in the set of primary keys to be mapped to a model." This is
just wrong (please notice I didn't use the word crap). I was very
enthusiastic about EF but my faith is getting weaker each day.
The scenario I explained is very common. In fact, you will hit this
whenever you need to create a database that contains multicompany
data. After I found the warning message in the edmx file I did some
searches on that and I found out that there are other people that
posted information about this a long time ago. Please see
https://forums.microsoft.com/Forums/ShowPost.aspx?PostID=3186392&SiteID=1.
The problem this guy has is identical with mine. Because he has a
multicompany database, the CompanyId field is part of most primary
keys in the database. I would just like to hear somebody say that
there are at least plans to make this a supported scenario. Otherwise,
EF has no chance of becoming a serious candidate to use even in medium-
sized projects.

Best regards,
Adrian

 

[Frans Bouma [C# MVP]]

I managed to find what went wrong. I did some searches in the edmx
file and finally I found this message: "warning 6030: The relationship
'FK_Items_ItemGroupId' uses the set of foreign keys '{CompanyId,
ItemGroupId}' that are partially contained in the set of primary keys
'{CompanyId, ItemId}' of the table 'Items'. The set of foreign keys
must be fully contained in the set of primary keys, or fully not
contained in the set of primary keys to be mapped to a model." This is
just wrong (please notice I didn't use the word crap). I was very
enthusiastic about EF but my faith is getting weaker each day.
The scenario I explained is very common. In fact, you will hit this
whenever you need to create a database that contains multicompany
data.

At first I thought: they're right, because what you're doing is
creating a m:n relationship between two tables. However, your link below
to Andres' post cleared it up as his tables clearly show that this is a
scenario which should work properly.

After I found the warning message in the edmx file I did some
searches on that and I found out that there are other people that
posted information about this a long time ago. Please see
https://forums.microsoft.com/Forums/ShowPost.aspx?PostID=3186392&SiteID=1.
The problem this guy has is identical with mine. Because he has a
multicompany database, the CompanyId field is part of most primary
keys in the database. I would just like to hear somebody say that
there are at least plans to make this a supported scenario. Otherwise,
EF has no chance of becoming a serious candidate to use even in medium-
sized projects.

(Btw, Andres was the former lead developer of DeKlarit, so he
definitely knows what he's talking about and what an O/R mapper does,
it's strange that the EF team didn't take his remarks more seriously, as
it's been almost a year since he posted that.)

I don't know why the EF team didn't fix this, and I can only describe
from my own experience with reverse engineering an entity model from a
relational schema, but as I do recall we had a similar bug very early on
in LLBLGen Pro as well (I clearly see this as a bug) and I think it was
even in the first beta cycle of v1. The thing is this:
the PK side in Supplier is: CompanyID*, SupplierID*. The FK side in
product is: CompanyID*, SupplierID. Both sides have a PK field,
CompanyID. You can now conclude two things:
1) it's a 1:1 relationship but there's an error: SupplierID isn't in the
PK to make it a true 1;1 relationship
OR
2) it's an m:1 relationship (which is the correct conclusion).

In theory, you can say: the first conclusion (which apparently is what
MS chose to do) is legit. However, in the case that the model is reverse
engineered, so the FK is correct. So conclusion 1) can't be legit in
practise, because otherwise the FK wouldn't be correct. In the case of
reverse engineering the model of course

So in this case, the logic which detects the relationship type can't
decide it's an error, it's a m:1 relationship, and this is then simple
to detect: the FK side doesn't form the PK of the fk side table (product).

Now, can they always detect if the model is reverse engineered, or
better: that the store model is indeed read from a db? Well, unless they
store that information (which they don't), you can't: someone could have
written the store XML meta data by hand and could have made an error.

I think, the core mistake is that they don't store all the data which
they read from a db as 'data from a db schema', or at least make it
detectable that the store meta data is indeed read from a relational
schema and not written by hand. That way, you can simply conclude:
what's in a relational schema is correct, and should be followed as-is.

After all: by reverse engineering from a relational schema, the store
meta data follows from the relational schema in the database. By
model-first it follows from the entity definitions + mappings.

But of course, I can only guess. Perhaps they simply didn't see it as
that important, however to me it looks like a fix with 1 line of code...
Yes, the EDM is much more than 'just an ORM'. Funny that the basic
ORM stuff isn't even in place.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Cowboy \(Gregory A. Beamer\)]

Okay, it looks like both of us are in the boat where EF is useless to us. I
could have created a work around for mine (wrapping in another class), but
that would totally defeat the purpose of having an O/R Mapper. In your case,
it looks like it is not going to be fixed any time soon considering the post
is 3 days shy of a year old.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://feeds.feedburner.com/GregoryBeamer#

or just read it:
http://feeds.feedburner.com/GregoryBeamer

********************************************
| Think outside the box! |
********************************************