EntityFramework using with database foreign key

Question

Actually I spend whole day on the EntityFramework for foreign key. assume we have two table. Process(app_id,process_id) LookupProcessId(process_id, process_description)

you can understand two tables with names, first table ,use process_id to indicate every application, and description is in the seoncd table. Actually i try many times and figure out how to do inquery: it was like

Dim result = (from x in db.Processes where x.LookupProcess is (from m in db.LookupProcessIds where descr = "example" select m).FirstOrDefault() select x).FirstOrDefault()

First I want to ask is there easier way to do it.

Second i want to ask question is about insert

 p As New AmpApplication.CUEngData.Process
    p.app_id=100
    p.LookupProcess = (from m in db.LookupProcessIds where descr = "example" select m).FirstOrDefault()
 db.AddToProcesses(p)
 db.SaveChanges()

from appearance it looks fine, but it give me error says Entities in 'AmpCUEngEntities.Processes' participate in the 'FK_Process_LookupProcess' relationship. 0 related 'LookupProcess' were found. 1 'LookupProcess' is expected.

can i ask is that insert wrong? and is that my query correct?

For the second part, is there actually an instance from LookupProcess returned ? — jeroenh, Sep 28 '12 at 06:04
Is there actually a record in the database for that description? — jeroenh, Sep 28 '12 at 06:37

score 0 · Answer 1 · answered Sep 28 '12 at 06:02

0

For your first question:

 Dim result = (from x in db.Processes 
               where x.LookupProcess.descr = "example" 
               select x).FirstOrDefault()

answered Sep 28 '12 at 06:02

jeroenh

26,362
10
73
104

actually i try this before, it says NULL pointer.... is that version of EF issue? – Hypnoz Sep 28 '12 at 06:26
and if it works, can i do thing like p.LookupProcess.descr = "example" as the way to insert – Hypnoz Sep 28 '12 at 06:27

score 0 · Answer 2 · answered Oct 12 '12 at 14:11

Actually, you missed some concepts from DataEntityModel, and its Framework. To manipulate data, you have to call object from contextual point of view. Those allow you to specify to the ObjectStateManager the state of an DataObject. In your case, if you have depending data from FK, you will have to add/update any linked data from leaf to root.

This example demonstrate simple (no dependances) data manipulation. A select if existing and an insert or update.

If you want more info about ObjectStateManager manipulation go to http://msdn.microsoft.com/en-us/library/bb156104.aspx

    Dim context As New Processing_context 'deseign your context (this one is linked to a DB)

    Dim pro = (From r In context.PROCESS
                    Where r.LOOKUPPROCESS.descr = LookupProcess.descr
                    Select r).FirstOrDefault()

    If pro Is Nothing Then 'add a new one
        pro = New context.PROCESS With {.AP_ID = "id", .PROCESS_ID = "p_id"}

        context.PROCESS.Attach(pro)
        context.ObjectStateManager.ChangeObjectState(pro, System.Data.EntityState.Added)
    Else
        'update data attibutes
        pro.AP_ID = "id"
        pro.PROCESS_ID = "p_id"

        context.ObjectStateManager.ChangeObjectState(pro, System.Data.EntityState.Modified)
        'context.PROCESS.Attach(pro)
    End If

    context.SaveChanges()

I hope this will help. Have a nice day!

Can't you just Add instead of attaching and changing the state to Added? — Pawel, Oct 12 '12 at 16:30
Not recommended especially if you manipulate data with references or anything else. — Minus, Oct 12 '12 at 17:31
Attach assumes that the entity is already in the database. Also if you change the state to Added I believe EF will not see any difference. Obviously you want to use Attach if you know the key and the database generates keys but if you set your keys on your own then I believe Add is the right thing to do. As far as related entities go - neither Attach nor Add is ideal. Attach will attach the entity and all related entities (so it will assume that they all arr in the database) while Add will add the entity and all related entities (so it will assume that none of them are in the database) — Pawel, Oct 12 '12 at 17:42

score 0 · Answer 3 · edited May 23 '17 at 12:12

For your first question, to expand on what @jeroenh suggested:

Dim result = (from x in db.Processes.Include("LookupProcess")  
    where x.LookupProcess.descr = "example"  
    select x).FirstOrDefault()

The addition of the Include statement will hydrate the LookupProcess entities so that you can query them. Without the Include, x.LookupProcess will be null which would likely explain why you got the error you did.

If using the literal string as an argument to Include is not ideal, see Returning from a DbSet 3 tables without the error "cannot be inferred from the query" for an example of doing this using nested entities.

For your second question, this line

p.LookupProcess = (from m in db.LookupProcessIds
    where descr = "example" select m).FirstOrDefault()

Could cause you problems later on because if there is no LookupProcessId with a process_description of "example", you are going to get null. From MSDN:

The default value for reference and nullable types is null.

Because of this, if p.LookupProcess is null when you insert the entity, you will get the exception:

Entities in 'AmpCUEngEntities.Processes' participate in the 'FK_Process_LookupProcess' relationship. 0 related 'LookupProcess' were found. 1 'LookupProcess' is expected.

To avoid this kind of problem, you will need to check that p.LookupProcess is not null before it goes in the database.

If Not p.LookupProcess Is Nothing Then 
    db.AddToProcesses(p)        
    db.SaveChanges()        
End If

EntityFramework using with database foreign key

3 Answers3