Entity Framework - Botched association

Question

I am attempting to create "code first from database" classes using Entity Framework 6.1.3 but it seems E.F. is having trouble making associations (or I am simply not setting something up correctly in my database).

Here are the two tables in question, I am making classes from using entity framework;

In my database CVE has a one-to-many relationship with AssessmentChecks. Thus the AssessmentChecks table has a foreign key in it as such;

ALTER TABLE NVD.[AssessmentChecks] ADD CONSTRAINT
    FK_AssessmentChecks_CVE FOREIGN KEY
    (
    CveIDFK
    ) REFERENCES NVD.CVE
    (
    CveID
    ) ON UPDATE  NO ACTION 
     ON DELETE  NO ACTION

However, when I create classes from this database, Entity Framework is marking "CveIDFK" as a required field...

public partial class AssessmentCheck
{
    public int AssessmentCheckID { get; set; }

    [Required]
    [StringLength(20)]
    public string CveIDFK { get; set; }

    public string AssessmentCheckSystem { get; set; }

    public string AssessmentCheckURL { get; set; }

    [StringLength(200)]
    public string AssessmentCheckName { get; set; }

    public virtual CVE CVE { get; set; }
}

If I use that same database to instead create an EDMX file (EF designer, instead of "Code First"), here is what that model diagram and AssessmentCheck class look like (which is more of what I was expecting);

public partial class AssessmentCheck
{
    public int AssessmentCheckID { get; set; }
    public string CveID { get; set; }
    public string AssessmentCheckSystem { get; set; }
    public string AssessmentCheckURL { get; set; }
    public string AssessmentCheckName { get; set; }

    public virtual CVE CVE { get; set; }
}

What gives? How can I make Entity Framework create the association in the code first model, so that "CveIDFK" is not "Required"?

To be clear, my end goal is to create classes from the database, create a "CVE" object, add an "AssessmentCheck" to the "CVE" object, and then "SaveChanges". The problem is when I do this, and try to Save Changes, EF is saying I need to explicitly provide the "CveIDFK" field of the "AssessmentCheck" object. I believe this FK should be automatically implied given the relationship between CVE and AssessmentChecks. Am I missing something?

EDIT:

I updated the database to go back to using CveID" as the name of the foreign key in the AssessmentChecks table, and recreated my classes.

Here is the code I am using to try to save the data;

CVE cve = new CVE
            {
                CveID = "CVE-2016-2140"
            };

            AssessmentCheck AC1 = new AssessmentCheck
            {
                AssessmentCheckName = "First Assessment check"
            };

            AssessmentCheck AC2 = new AssessmentCheck
            {
                AssessmentCheckName = "Second Assessment Check"
            };

            cve.AssessmentChecks.Add(AC1);
            cve.AssessmentChecks.Add(AC2);

            using (var context = new NVDModel())
            {
                try
                {
                    context.Database.Log = Console.WriteLine;
                    context.CVEs.Add(cve);       
                    context.SaveChanges();
                }
            }

And the error I get is

Entity of type "AssessmentCheck" in state "Added" has the following validation e rrors: - Property: "CveID", Error: "The CveID field is required." Entity of type "AssessmentCheck" in state "Added" has the following validation e rrors: - Property: "CveID", Error: "The CveID field is required."

If I remove the "required" data annotation from the "CveID" field of the "AssessmentChecks" class, it works (the foreign key is automatically inserted without me explicitly providing it), but I wouldnt think I have to remove this data annotation.

Also, if I move the addition of the children after I add the parent to the context, that also works;

try
                {
                    context.Database.Log = Console.WriteLine;
                    context.CVEs.Add(cve);
                    cve.AssessmentChecks.Add(AC1);
                    cve.AssessmentChecks.Add(AC2);
                    context.SaveChanges();
                }

What is going on here?

Completely off-topic but I read that title as "botched assassination". — Ian, Jan 27 '16 at 00:19
In the screenshot of your database, `CvelDFK` is marked `NOT NULL` (and thus required). EF has done the right thing in this case — Rob, Jan 27 '16 at 02:31
@Rob it absolutely is required, but in code, when I create a CVE object, and add an AssessmentCheck to it, then "SaveChanges" I would think EntityFramework should automatically populate the "CveIDFK" foreign key field, but it does not. Instead it tells me that the CveIDFK field is required and thus the "save" fails. I believe I shouldn't have to explicitly set "CveIDFK", it should be automatically derived from the association — n00b, Jan 27 '16 at 16:15
I don't see where you have made the association in code first. You can do it by convention if you change CveIDFK to CveId (notice that is what database first generated). You can do it by annotation if you add ForeignKey("CveIDFK ") above the CVE object. Or you can do it with the fluent api. https://msdn.microsoft.com/en-us/data/jj819164.aspx Show the code that actually throws the error when saving. — Steve Greene, Jan 27 '16 at 20:13
@SteveGreene I'm confused, shouldn't EF be making the association for me, when I do code first from database? Shouldnt it recognize the relationship? — n00b, Jan 27 '16 at 20:20
It will if you follow convention (see link in prior comment). When you call it CveIDFK it can't make the connection so if you are not going to call it CveId then you need an annotation or fluent config (my preference). — Steve Greene, Jan 27 '16 at 20:26
@SteveGreene I initially had it named CveID, and I thought it was the problem, so I changed the name to CveIDFK but the same problem persist. when I try to save the CVE object with associated AssessmentCheck added to it, .NET says I need to supply CveID (or CveIDFK) in the AssessmentCheck object (instead of implying it from the relationship) — n00b, Jan 27 '16 at 20:29
OK, that's a different issue then. There are several ways to save parent-child entities. What technique did you use? See http://stackoverflow.com/questions/13148585/entity-framework-add-child-entity — Steve Greene, Jan 27 '16 at 20:31
@SteveGreene Please see my updated edits for the code I am using to save the relationship. Note, adding the children inside the "using" block, after adding the CVE also allows a successful addition, without explicitly defining the foreign key but I want to add the children outside the "using context" block if possible. — n00b, Jan 27 '16 at 23:56
OK, didn't catch the parent id was not an identity. So if you already know the parent id why not just explicitly set it in the child? — Steve Greene, Jan 28 '16 at 14:22
@SteveGreene I can do that, but I think E.F. should be picking up the relationship and implicitly setting the parent id for me, so thats why i think im doing something wrong here. in fact E.F. does do this, if i move the addition of the children inside the using block (see the last block of code I posted) — n00b, Jan 28 '16 at 14:34
I do exactly what you have coded and it works - the difference is my parent key is identity. madan's answer explains. Here is another way to do it: http://stackoverflow.com/questions/9355429/using-ef-4-1-inserting-a-parent-record-and-multiple-child-records-as-a-single-u — Steve Greene, Jan 28 '16 at 15:17

score 1 · Answer 1 · answered Jan 28 '16 at 12:58

Once you add the entity into dbset(CVEs in your case) entity framework start tracking any changes made to it hence when you add the child object(AssessmentCheck) into this entity it is able to assign the parent id through the information provided by you i.e. data annotation in case of code first. Hence the second second method is working.

In first method you will be able to achieve the same if instead of providing the PK(CveId) for CVE you were using the identity i.e. auto generated, but since you are using string as PK in CVE identity is not possible, one simple solution is have int as PK and make the existing CveId a unique key, otherwise use a custom add instead of cve.AssessmentChecks.Add... which assign/set the FK to children.

You can see this action if you use the following method before save changes

var entitychanges = context.ChangeTracker.Entries().Where(e => e.State == EntityState.Added);

you will notice in first method CveID is not set for AssessmentCheck while in second it is.

I appreciate the response, though im still confused. is there documentation for EF that explains why the relationship can only be automatically setup for identity/integer primary keys as opposed to string? also, why does the first method work (adding assessment checks outside of the context) when using the EF EDMX designer as opposed to the code first approach? the only difference is I use the "EF designer from database" to generate clases, as opposed to "code first from database", but the object creation/saving is the same, and it works with the EF designer approach — n00b, Jan 29 '16 at 16:33
FWIW, I simply ended up using the Visual EDMX model, which didnt suffer from this 'having to assign the parent of children outside of the context' issue — n00b, Feb 03 '16 at 17:49

Entity Framework - Botched association

1 Answers1