首页 \ 问答 \ 实体框架数据库连接问题(Entity Framework database connection question)

实体框架数据库连接问题(Entity Framework database connection question)

 我们正在使用.Net Entity Framework来完成与数据库相关的工作。 我们的数据库是Sybase SQL Anywhere 。  
using (AndeDBEntities db = new AndeDBEntities(decryptConnStr()))
{

}
 
 我们使用大量上述语句来访问数据库。 我的问题是，我们是否需要在访问完成后每次关闭连接以及如何执行此操作？  
 有一次我看到“超出数据库服务器连接限制”错误。 我想知道我们的数据库连接代码一定有问题。 

We are using .Net Entity Framework to do our database related work. Our database is Sybase SQL Anywhere. 
using (AndeDBEntities db = new AndeDBEntities(decryptConnStr()))
{

}
 
We use a lots of above statements to access database. My questions are do we need to close the connection each time after the access is done and how to do that?  
At one time I saw "Database server connection limit exceeded" error. I am wondering there must be something wrong in our database connection code.

原文：https://stackoverflow.com/questions/3150565

更新时间：2023-09-23 12:09

最满意答案

 这是一个艰难的转变，我建议用两遍来表演。  
 首先，将DriverId FK保留在DriverWork ，并使DriverId中的WorkPage可选（可为空）：  
public class WorkPage
{
    [Key]
    public int Id { get; set; }

    public byte CommissionPercentage { get; set; }

    public bool IsClosed { get; set; }

    public DateTime? DateClosed { get; set; }

    public int? DriverId { get; set; }

    [ForeignKey(nameof(DriverId))]
    public Driver Driver { get; set; }
}

public class DriverWork
{
    [Key]
    public int Id { get; set; }

    public string FromLocation { get; set; }

    public string ToLocation { get; set; }

    public int Price { get; set; }

    public DateTime Date { get; set; }

    public int DriverId { get; set; }

    [ForeignKey(nameof(DriverId))]
    public Driver Driver { get; set; }

    public int WorkPageId { get; set; }

    [ForeignKey(nameof(WorkPageId))]
    public WorkPage WorkPage { get; set; }
}
 
 生成新的迁移。 它应该是这样的：  
public override void Up()
{
    CreateTable(
        "dbo.WorkPage",
        c => new
            {
                Id = c.Int(nullable: false, identity: true),
                CommissionPercentage = c.Byte(nullable: false),
                IsClosed = c.Boolean(nullable: false),
                DateClosed = c.DateTime(),
                DriverId = c.Int(nullable: false),
            })
        .PrimaryKey(t => t.Id)
        .ForeignKey("dbo.Driver", t => t.DriverId, cascadeDelete: true)
        .Index(t => t.DriverId);

    AddColumn("dbo.DriverWork", "WorkPageId", c => c.Int());
    CreateIndex("dbo.DriverWork", "WorkPageId");
    AddForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPage", "Id");
}
 
 执行迁移。 然后通过从DriverWork删除Driver并在DriverId中WorkPage （非null）来恢复所需的实体模型：  
public class WorkPage
{
    [Key]
    public int Id { get; set; }

    public byte CommissionPercentage { get; set; }

    public bool IsClosed { get; set; }

    public DateTime? DateClosed { get; set; }

    public int DriverId { get; set; }

    [ForeignKey(nameof(DriverId))]
    public Driver Driver { get; set; }
}

public class DriverWork
{
    [Key]
    public int Id { get; set; }

    public string FromLocation { get; set; }

    public string ToLocation { get; set; }

    public int Price { get; set; }

    public DateTime Date { get; set; }

    public int WorkPageId { get; set; }

    [ForeignKey(nameof(WorkPageId))]
    public WorkPage WorkPage { get; set; }
}
 
 生成第二个新迁移。 它应该看起来像这样：  
public override void Up()
{
    DropForeignKey("dbo.DriverWork", "DriverId", "dbo.Driver");
    DropForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPages");
    DropIndex("dbo.DriverWork", new[] { "DriverId" });
    DropIndex("dbo.DriverWork", new[] { "WorkPageId" });
    AlterColumn("dbo.DriverWork", "WorkPageId", c => c.Int(nullable: false));
    CreateIndex("dbo.DriverWork", "WorkPageId");
    AddForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPage", "Id", cascadeDelete: true);
    DropColumn("dbo.DriverWork", "DriverId");
}
 
 使用Sql方法填充WorkPage表数据并在需要之前更新WorkTableId FK。 例如，在Up方法的开头插入以下内容：  
Sql(@"insert into dbo.WorkPage (CommissionPercentage, IsClosed, DateClosed, DriverId) select 0, 0, null, DriverId from dbo.DriverWork");
Sql(@"update dbo.DriverWork set WorkPageId = WP.Id from dbo.DriverWork DW join dbo.WorkPage WP on DW.DriverId = WP.DriverId");
 
 执行迁移，您就完成了。  
 实际上， Sql调用哪些转换数据可以在第一次迁移Up方法的末尾。  
 事先了解所有这些，您可以按原样保留新模型（跳过第一步），只需将生成的迁移Up方法替换为上述两者的并集，例如  
public override void Up()
{
    CreateTable(
        "dbo.WorkPage",
        c => new
            {
                Id = c.Int(nullable: false, identity: true),
                CommissionPercentage = c.Byte(nullable: false),
                IsClosed = c.Boolean(nullable: false),
                DateClosed = c.DateTime(),
                DriverId = c.Int(nullable: false),
            })
        .PrimaryKey(t => t.Id)
        .ForeignKey("dbo.Driver", t => t.DriverId, cascadeDelete: true)
        .Index(t => t.DriverId);

    AddColumn("dbo.DriverWork", "WorkPageId", c => c.Int());
    CreateIndex("dbo.DriverWork", "WorkPageId");
    AddForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPage", "Id");

    Sql(@"insert into dbo.WorkPage (CommissionPercentage, IsClosed, DateClosed, DriverId) select 0, 0, null, DriverId from dbo.DriverWork");
    Sql(@"update dbo.DriverWork set WorkPageId = WP.Id from dbo.DriverWork DW join dbo.WorkPage WP on DW.DriverId = WP.DriverId");

    DropForeignKey("dbo.DriverWork", "DriverId", "dbo.Driver");
    DropForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPages");
    DropIndex("dbo.DriverWork", new[] { "DriverId" });
    DropIndex("dbo.DriverWork", new[] { "WorkPageId" });
    AlterColumn("dbo.DriverWork", "WorkPageId", c => c.Int(nullable: false));
    CreateIndex("dbo.DriverWork", "WorkPageId");
    AddForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPage", "Id", cascadeDelete: true);
    DropColumn("dbo.DriverWork", "DriverId");
}

Sine it's a tough transformation, I would suggest performing it with two passes. 
First, keep the DriverId FK in the DriverWork and make DriverId in WorkPage optional (nullable): 
public class WorkPage
{
    [Key]
    public int Id { get; set; }

    public byte CommissionPercentage { get; set; }

    public bool IsClosed { get; set; }

    public DateTime? DateClosed { get; set; }

    public int? DriverId { get; set; }

    [ForeignKey(nameof(DriverId))]
    public Driver Driver { get; set; }
}

public class DriverWork
{
    [Key]
    public int Id { get; set; }

    public string FromLocation { get; set; }

    public string ToLocation { get; set; }

    public int Price { get; set; }

    public DateTime Date { get; set; }

    public int DriverId { get; set; }

    [ForeignKey(nameof(DriverId))]
    public Driver Driver { get; set; }

    public int WorkPageId { get; set; }

    [ForeignKey(nameof(WorkPageId))]
    public WorkPage WorkPage { get; set; }
}
 
Generate new migration. It should be something like this: 
public override void Up()
{
    CreateTable(
        "dbo.WorkPage",
        c => new
            {
                Id = c.Int(nullable: false, identity: true),
                CommissionPercentage = c.Byte(nullable: false),
                IsClosed = c.Boolean(nullable: false),
                DateClosed = c.DateTime(),
                DriverId = c.Int(nullable: false),
            })
        .PrimaryKey(t => t.Id)
        .ForeignKey("dbo.Driver", t => t.DriverId, cascadeDelete: true)
        .Index(t => t.DriverId);

    AddColumn("dbo.DriverWork", "WorkPageId", c => c.Int());
    CreateIndex("dbo.DriverWork", "WorkPageId");
    AddForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPage", "Id");
}
 
Execute the migration. Then restore the desired entity model by removing the Driver from DriverWork and making DriverId in WorkPage required (non null): 
public class WorkPage
{
    [Key]
    public int Id { get; set; }

    public byte CommissionPercentage { get; set; }

    public bool IsClosed { get; set; }

    public DateTime? DateClosed { get; set; }

    public int DriverId { get; set; }

    [ForeignKey(nameof(DriverId))]
    public Driver Driver { get; set; }
}

public class DriverWork
{
    [Key]
    public int Id { get; set; }

    public string FromLocation { get; set; }

    public string ToLocation { get; set; }

    public int Price { get; set; }

    public DateTime Date { get; set; }

    public int WorkPageId { get; set; }

    [ForeignKey(nameof(WorkPageId))]
    public WorkPage WorkPage { get; set; }
}
 
Generate a second new migration. It should look something like this: 
public override void Up()
{
    DropForeignKey("dbo.DriverWork", "DriverId", "dbo.Driver");
    DropForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPages");
    DropIndex("dbo.DriverWork", new[] { "DriverId" });
    DropIndex("dbo.DriverWork", new[] { "WorkPageId" });
    AlterColumn("dbo.DriverWork", "WorkPageId", c => c.Int(nullable: false));
    CreateIndex("dbo.DriverWork", "WorkPageId");
    AddForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPage", "Id", cascadeDelete: true);
    DropColumn("dbo.DriverWork", "DriverId");
}
 
Use Sql methods to populate the WorkPage table data and update the WorkTableId FK before making it required. For instance, insert the following at the beginning of the Up method: 
Sql(@"insert into dbo.WorkPage (CommissionPercentage, IsClosed, DateClosed, DriverId) select 0, 0, null, DriverId from dbo.DriverWork");
Sql(@"update dbo.DriverWork set WorkPageId = WP.Id from dbo.DriverWork DW join dbo.WorkPage WP on DW.DriverId = WP.DriverId");
 
Execute the migration and you are done.  
Actually the Sql calls which transform the data can be at the end of the first migration Up method. 
Knowing all that in advance, you can keep your new model as is (skip the first step) and simply replace the generated migration Up method with the union of the above two, e.g. 
public override void Up()
{
    CreateTable(
        "dbo.WorkPage",
        c => new
            {
                Id = c.Int(nullable: false, identity: true),
                CommissionPercentage = c.Byte(nullable: false),
                IsClosed = c.Boolean(nullable: false),
                DateClosed = c.DateTime(),
                DriverId = c.Int(nullable: false),
            })
        .PrimaryKey(t => t.Id)
        .ForeignKey("dbo.Driver", t => t.DriverId, cascadeDelete: true)
        .Index(t => t.DriverId);

    AddColumn("dbo.DriverWork", "WorkPageId", c => c.Int());
    CreateIndex("dbo.DriverWork", "WorkPageId");
    AddForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPage", "Id");

    Sql(@"insert into dbo.WorkPage (CommissionPercentage, IsClosed, DateClosed, DriverId) select 0, 0, null, DriverId from dbo.DriverWork");
    Sql(@"update dbo.DriverWork set WorkPageId = WP.Id from dbo.DriverWork DW join dbo.WorkPage WP on DW.DriverId = WP.DriverId");

    DropForeignKey("dbo.DriverWork", "DriverId", "dbo.Driver");
    DropForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPages");
    DropIndex("dbo.DriverWork", new[] { "DriverId" });
    DropIndex("dbo.DriverWork", new[] { "WorkPageId" });
    AlterColumn("dbo.DriverWork", "WorkPageId", c => c.Int(nullable: false));
    CreateIndex("dbo.DriverWork", "WorkPageId");
    AddForeignKey("dbo.DriverWork", "WorkPageId", "dbo.WorkPage", "Id", cascadeDelete: true);
    DropColumn("dbo.DriverWork", "DriverId");
}

实体框架数据库连接问题(Entity Framework database connection question)

最满意答案

相关问答

实体框架4的示例：将POCO映射到EAV数据库？(Examples for Entity Framework 4: Mapping POCOs to EAV database?)[2023-02-11]

模拟实体框架数据库(Mock Entity Framework database)[2023-11-12]

在实体框架中更改数据库结构和移动数据(Changing structure of database and moving data in Entity Framework)[2021-11-01]

如何使用Entity Framework提高数据库插入性能(How to increase database insert performance with Entity Framework)[2021-09-30]

实体框架代码首先迁移到多个数据库(Entity framework code first migration to multiple database)[2022-07-25]

实体框架数据库优先方法(Entity Framework Database First Approach)[2023-10-19]

使用Entity Framework在运行时更改数据库结构？(Changing database structure at runtime with Entity Framework?)[2023-03-02]

将数据库移动到新服务器后，无法在visual studio 2010的实体框架设计器中看到SQL Server 2008表(Unable to see SQL Server 2008 tables within entity framework designer of visual studio 2010 after moving database to new server)[2022-04-14]

MVC C＃在不删除数据库的情况下更改数据库结构(MVC C# Changing Database Structure Without Dropping Database)[2022-01-28]

实体框架按类更改数据库物理位置(entity framework change database physical location by class)[2024-03-05]

相关文章

最新问答