EntityFramework使用数据库外键

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)

实际上我整天花在EntityFramework上的外键上。假设我们有两张桌子。进程(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

你可以理解两个带有名称的表,第一个表,使用process_id来表示每个应用程序,描述在seoncd表中。实际上我尝试了很多次,并弄清楚如何做查询:它就像

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.

从外观看起来很好,但它给我错误说'AmpCUEngEntities.Processes'中的实体参与'FK_Process_LookupProcess'关系。找到了0个相关的'LookupProcess'。 1'LookupProcess'是预期的。

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

请问是插错了吗?这是我的查询正确吗?

3 个解决方案

#1

For your first question:

对于你的第一个问题:

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

#2

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.

实际上,您错过了DataEntityModel及其Framework的一些概念。要操纵数据,您必须从上下文的角度调用对象。这些允许您向ObjectStateManager指定DataObject的状态。在您的情况下,如果您具有来自FK的依赖数据,则必须从叶到根添加/更新任何链接数据。

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

如果您想了解有关ObjectStateManager操作的更多信息,请访问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!

我希望这将有所帮助。祝你今天愉快!

#3

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

关于第一个问题,请扩展@jeroenh建议的内容:

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.

添加Include语句将使LookupProcess实体保持水合,以便您可以查询它们。如果没有Include,x.LookupProcess将为null,这可能解释了为什么你得到了错误。

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.

如果使用文字字符串作为Include的参数并不理想,请参阅从DbSet 3表中返回,而不会出现“无法从查询中推断”的错误,以获取使用嵌套实体执行此操作的示例。

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:

可能会在以后引起您的问题,因为如果没有带有“示例”的process_description的LookupProcessId,您将获得null。来自MSDN:

The default value for reference and nullable types is null.

reference和nullable类型的默认值为null。

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

因此,如果插入实体时p.LookupProcess为null,您将获得异常:

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

'AmpCUEngEntities.Processes'中的实体参与'FK_Process_LookupProcess'关系。找到了0个相关的'LookupProcess'。 1'LookupProcess'是预期的。

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

要避免此类问题,您需要检查p.LookupProcess在进入数据库之前是否为空。

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

#1