CODEWITHSUNDEEP
ado.netinsertforeign-keys
nanonerd
nanonerd

Reputation: 1984

ADO.NET INSERT new record that has a foreign key in the table

I am fairly new to ADO.NET. I am ok with all the basic INSERT, etc. But now, I have a problem inserting a record into a table that contains a foreign key. I have done some research but am still stuck ... so here goes:

I want to INSERT a new record into a table called Professionals. It has a foreign key mapped to a different table. The FK is WAPublicUserID.

See Image:

enter image description here

When I create a data model, the WAPublicUserID isn't listed in the Properties of the Professional data model.

See Image:

enter image description here

Therefore, when I try to create an INSERT in my code, the WAPublicUserID field can't be found and I can't insert the record. The WAPublicUserID that I wish to use already exists in the WAPublicUser table that the FK is mapped to.

See Image:

enter image description here

How do I go about Inserting a new record in the Professionals table that contains a foreign key to an existing record in the WAPublicUser table? Thanks!

Upvotes: 0

Views: 1685

Answers (3)

nanonerd
nanonerd

Reputation: 1984

OK, here's the real answer to my OP:

The asp.net website that I took over to manage was targeting .NET 3.5. Apparently, there are some issues with 3.5 and Entity Framework.

I converted the website to target .NET 4.0(*see below to see how). When I went to create the entity data model, voila , by default, it now included the Foreign Keys and therefore, I did not have any issues as described in OP.

If you run into this situation, you have to make sure that the web server is also upgraded to .NET 4.0. Because if you upgrade/convert the website files to target .NET 4.0, but your web server hasn't been upgraded, then although the website runs smooth on your dev machine (assuming that it has .NET 4.0 framework), it will crash on the live web server.

As an aside, .NET 4.0 framework will run apps that were built using previous versions of .NET (backward compatibility) ... however, an app that targets .NET 4.0 will not run in an environment with .NET 3.5 framework or prior.

CONVERT WEBSITE TO .NET 4.0:

Two ways to convert/upgrade website to .NET 4.0. 1) Usually, when you open a fresh copy and it's targeting 4.0, visual studio will ask if you want to convert/upgrade (Choose YES). 2) Within visual studio (commercial version), click WEBSITE tab, START OPTIONS, BUILD ... then you should see the options to change the "Target Framework" ...

Upvotes: 0

nanonerd
nanonerd

Reputation: 1984

Got it. Here's how it works, in case anyone else reads this. #1 and #2 are focal points.

Mucho thanks to @BonyT for getting me on the right path ... 

using (JONDOEntities myEnt = new JONDOEntities())
        {    
            // #1) Need to create WAPublicUser object first
            var wap = (from w in myEnt.WAPublicUsers
                       where w.WAPublicUserID == 981
                       select w).FirstOrDefault();

            var proUser = (from p in myEnt.Professionals
                           where p.WAPublicUser.WAPublicUserID == wap.WAPublicUserID
                           select p).FirstOrDefault();

            // If the record does not exist in the Professional table, insert new record. 
            if (proUser == null)
            {
                JONDOModel.Professional pro = new JONDOModel.Professional()
                {                        
                    ProfessionalType = "unknown",
                    FirstName = "unknown",
                    LastName = "unknown",
                    PhoneNumber = "unknown",
                    WebsiteUrl = "unknown",
                    TaxID = "unknown",
                    BusinessInfo = "unknown",
                    ProfessionalLogo = "unknown",
                    IsApproved = true,
                    CATaxExempt = false,                       
                    WAPublicUser = wap     // #2) Plug in the WAPublicUser object here
                };

                myEnt.AddToProfessionals(pro);
                myEnt.SaveChanges();
            }

Upvotes: 0

BonyT
BonyT

Reputation: 10940

Someone has set "Include Foreign Key Properties in Model" to false.

Hence you have the navigation property of WAPublicUser but not the ForeignKey property.

This means you will have to Attach the relevant WAPublicUser object to the WAPublicUser property on the object you are trying to save.

I'd need a lot more code to know exactly what you are doing, but the basics of it are as follows:

If the WAPublicUser already exists:

  • Grab the existing entity from the database - OldEntity.

  • Update the OldEntity with the properties of the new one you are currently trying to save.

  • Save the (now updated) Old entity back to the database - because you have just read it, it should have the WAPublicUser reference already set.

If it doesn't:

  • Create a new WAPublicUser

  • Set the WAPublicuser property of the Professional object to the newly created WAPublicUser - that line goes where your code stops above.

  • myEnt.AddToProfessionals(pro);

  • myEnt.SaveChanges();

Upvotes: 1

Related Questions