Click here to Skip to main content
15,921,028 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

I have a SQL Server table <entity> which is in a certain administration. The table <entity> has a relation with <entitystatus>.

Id (int, PK, auto-increment)
AdminstrationId int
Status int
Name varchar

Id (int, PK, auto-increment)
AdminstrationId int
Number int
Name varchar

As you can see, both tables have a Id field which is a PK and have auto-increment on.

Now I want entity framework to create a relation between both AdministrationId fields, and the <entity>.Status & <entitystatus>Number field.

How can I do this?

The code below in the 'what have you tried', actually works for the relation, but it removes the Id field as a PK of the status table. This causes an error when I enter new status values in the <entitystatus> table

What I have tried:

    .HasKey(c => new { c.AdministrationId, c.Number });

    .HasRequired(p => p.EntityStatus)
    .WithMany(x => x.Entities)
    .HasForeignKey(p => new { p.AdministrationId, p.Status });
Updated 7-Sep-17 8:16am

1 solution

I usually build tables/relationships for SQL in a SQL utility (such as SQL Server Management Studio).

That aside, I would still following something of the same route: create the tables, set primary key(s), and the relationships as discreet SQL Queries and not parametized with C# code.

I prefer letting SQL handle SQL (even if via C#, C++, php, ...)
Establish a SQL connection
Set the Query Strings
Execute them
Share this answer
Eduard Keilholz 8-Sep-17 3:00am    
That is exactly what I do, I just need to define the relation within my datacontext so I can use the include ion my linq queries to also fetch the statusses related to the main entity. And instead of using the ID in the entity, I'd like to use the status number because most uses have a fixed and recognizable status number.
W Balboos, GHB 8-Sep-17 6:39am    
Here I must profess some ignorance; the last time I used LINQ its SQL was a bit spotty and I defer to your expertise in LINQ over mine.

With that in mind, I wonder is it LINQ that loses your PK? Is it supposed to be aware of and make use of the table's primary key (and identity fields) - which would require it to remain connected.

Part of the excuse for my weakness w/LINQ is that I work with a DBA - we're mutually symbiotic - and have learned to create the original SQL so the returned data is what I need and how I need it. Probably, this lack will cost me at some point but my TSQL is much better than it used to be. Even for parent-child select lists in HTML (which could be handled very conveniently in a LINQ context).

But, for what things similar to what you are doing, problems have arisen

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900