实体框架GetOrCreate？ [英] Entity Framework GetOrCreate?
问题描述
CREATE TABLE [dbo]。[Providers]（
[id] BIGINT IDENTITY 1，1）NOT NULL，
[name] NVARCHAR（850）NOT NULL，
CONSTRAINT [PK_Providers] PRIMARY KEY CLUSTERED（[id] ASC），
CONSTRAINT [UQ_Providers_name] UNIQUE NONCLUSTERED [名] ASC）
）;
CREATE TABLE [dbo]。[Templates]（
[id] BIGINT IDENTITY（1，1）NOT NULL，
[provider] BIGINT NOT NULL，
[repositoryId] NVARCHAR（842）NOT NULL ，
CONSTRAINT [PK_Templates] PRIMARY KEY CLUSTERED（[id] ASC），
CONSTRAINT [UQ_Templates_repositoryId] UNIQUE NONCLUSTERED（[provider] ASC，[repositoryId] ASC），
CONSTRAINT [FK_Templates_ToProviders] FOREIGN KEY（[provider]）参考[dbo]。[Providers]（[id]）
）;
使用实体框架，我想在模板中插入一行。但是，我不知道插入时是否存在 Providers 中的匹配行。如果许多人同时插入，我想防范竞争条件（两个模板与提供者）。
$ b $我相信为了完成这个工作，我需要先获取或者创建提供者，然后才能持续（至少一个完整的DB往返）创建模板。
我已经提出了一个我认为可以工作的解决方案，但是似乎令人难以置信的复杂，我想象的是一个很常见的情况。我希望有人能告诉我我做错了什么，或者我可以做得更好。
以下代码位于我的 DbContext 实现。
public async任务< Provider> GetOrCreateProvider（String name）
{
var provider = new Provider {name = name};
返回等待GetOrCreateEntity（提供者，提供者）;
}
公共异步任务<模板> GetOrCreateTemplate（Provider provider，String repositoryId）
{
var template = new Template {Provider = provider，repositoryId = repositoryId};
返回等待GetOrCreateEntity（模板，模板）;
}
私有异步任务< T> GetOrCreateEntity< T>（DbSet< T> dbSet，T newEntity）其中T：class
{
var maybeEntity = await dbSet.Where（entity => entity.Equals（newEntity））。FirstOrDefaultAsync ;
if（maybeEntity！= null）
return maybeEntity;
try
{
dbSet.Add（newEntity）;
等待SaveChangesAsync（）;
return newEntity; （（异常.InnerException as SqlException））数组== 2601 ||（异常的异常异常作为SqlException）？数组== 2627）
{
return await dbSet.Where（entity => entity.Equals（newEntity））。FirstOrDefaultAsync（）;
}
}
实体类由VS从我的数据库自动生成添加了 .Equals 覆盖的表。这里是提供者，模板是类似的，但引用单个提供者而不是一个集合：
public partial class Provider
{
public Provider（）
{
Templates = new HashSet< Template>（）;
}
public long id {get;组;
[必需]
[StringLength（850）]
public string name {get;组; }
public virtual ICollection< Template>模板{get;组;
public override Boolean Equals（Object otherObject）
{
if（Object.ReferenceEquals（this，otherObject））
return true;
if（otherObject == null）
return false;
var other = otherObject as Provider;
if（other == null）
return false;
返回名称== other.name;
}
public override Int32 GetHashCode（）
{
return name.GetHashCode（）;
}
}
使用情况如下所示：
var provider = await GetOrCreateProvider（foo）;
var template = await GetOrCreateTemplate（provider，bar）;
这个主要问题（除了进行这样简单的操作所需的样板量）在最佳情况下（当提供者和模板都已存在时），我必须对服务器进行两次全面的访问。在最坏的情况下，我必须进行6次往返行程：
- 无法获得提供者
- 约束违规创建提供者
- 获取提供者
- 无法获取模板
- 约束违规创建模板
- 获取模板
我不是非常关心最坏的情况，但我担心随着我的对象图的复杂性，往返次数的增加，情况最好。如果我有另外一些参考模板表中的内容，我现在可以进行三次往返。
存储过程将在单个调用数据库和单个事务中执行所有步骤。看看 merge 命令，例如介绍或参考。
您可以使用此方法从EF执行存储过程： p>
private static int ExecuteSqlCount（string statement，SqlParameter [] paramsSql）
{
using（Entities dbContext = new Entities（））
{
var total = dbContext.Database.SqlQuery< int>（statement，paramsSql）.First（）;
返回总数;
}
}
返回值是USP返回的值。该方法称为（对于返回int的USP）：
var parameters = new List< SqlParameter>（）;
string statement =exec uspPersonAdd @personName = @name，@activeFlag = @active;
parameters.Add（new SqlParameter（@ name，person.PersonName））;
parameters.Add（new SqlParameter（@ active，person.Active））;
int id = ExecuteSqlCount（statement，parameters.ToArray（））;
您需要使用System.Data.SqlClient
To start here are my tables. Not very interesting, just a primary key, data and foreign key connecting them. Everything with a unique constraint.
CREATE TABLE [dbo].[Providers] (
[id] BIGINT IDENTITY (1, 1) NOT NULL,
[name] NVARCHAR (850) NOT NULL,
CONSTRAINT [PK_Providers] PRIMARY KEY CLUSTERED ([id] ASC),
CONSTRAINT [UQ_Providers_name] UNIQUE NONCLUSTERED ([name] ASC)
);
CREATE TABLE [dbo].[Templates] (
[id] BIGINT IDENTITY (1, 1) NOT NULL,
[provider] BIGINT NOT NULL,
[repositoryId] NVARCHAR (842) NOT NULL,
CONSTRAINT [PK_Templates] PRIMARY KEY CLUSTERED ([id] ASC),
CONSTRAINT [UQ_Templates_repositoryId] UNIQUE NONCLUSTERED ([provider] ASC, [repositoryId] ASC),
CONSTRAINT [FK_Templates_ToProviders] FOREIGN KEY ([provider]) REFERENCES [dbo].[Providers] ([id])
);
Using Entity Framework, I want to insert a row into Templates. However, I don't know at the time of insert whether the matching row in Providers exists yet. I want to be defensive against race conditions if many people are inserting at the same time (two templates with the same Provider).
I believe that in order to accomplish this I need to first get-or-create the provider, then after that is persisted (at least one full DB round trip) create the template.
I have come up with a solution that I think will work, but it seems incredibly complicated for what I have to imagine is a pretty common scenario. I'm hoping someone can show me what I am doing wrong or how I can do it better.
The following code lives inside my DbContext implementation.
public async Task<Provider> GetOrCreateProvider(String name)
{
var provider = new Provider { name = name };
return await GetOrCreateEntity(Providers, provider);
}
public async Task<Template> GetOrCreateTemplate(Provider provider, String repositoryId)
{
var template = new Template { Provider = provider, repositoryId = repositoryId };
return await GetOrCreateEntity(Templates, template);
}
private async Task<T> GetOrCreateEntity<T>(DbSet<T> dbSet, T newEntity) where T : class
{
var maybeEntity = await dbSet.Where(entity => entity.Equals(newEntity)).FirstOrDefaultAsync();
if (maybeEntity != null)
return maybeEntity;
try
{
dbSet.Add(newEntity);
await SaveChangesAsync();
return newEntity;
}
catch (UpdateException exception) when ((exception.InnerException as SqlException)?.Number == 2601 || (exception.InnerException as SqlException)?.Number == 2627)
{
return await dbSet.Where(entity => entity.Equals(newEntity)).FirstOrDefaultAsync();
}
}
Entity classes are auto-generated by VS from my DB tables with a .Equals override added to them. Here is Provider, Template is similar but with a reference to a single Provider rather than a collection:
public partial class Provider
{
public Provider()
{
Templates = new HashSet<Template>();
}
public long id { get; set; }
[Required]
[StringLength(850)]
public string name { get; set; }
public virtual ICollection<Template> Templates { get; set; }
public override Boolean Equals(Object otherObject)
{
if (Object.ReferenceEquals(this, otherObject))
return true;
if (otherObject == null)
return false;
var other = otherObject as Provider;
if (other == null)
return false;
return name == other.name;
}
public override Int32 GetHashCode()
{
return name.GetHashCode();
}
}
Usage would look like this:
var provider = await GetOrCreateProvider("foo");
var template = await GetOrCreateTemplate(provider, "bar");
The primary problem with this (aside from the amount of boilerplate necessary to do such a simple operation) is that I have to make two full round trips to the server in the best case scenario (when both the provider and the template already exist). In the worst case scenario I have to make 6 round trips:
- fail to get provider
- constraint violation creating provider
- get provider
- fail to get template
- constraint violation creating template
- get template
I'm not terribly concerned about the worst case scenario but I am concerned about the best case scenario as the number of round trips goes up with the complexity of my object graph. If I have something else that references something in the Template table I am now up to 3 round trips.
A stored procedure would do all steps in a single call to the database, and in a single transaction. Have a look at the merge command, for example introduction or reference.
You can use this method to execute a stored procedure from EF:
private static int ExecuteSqlCount(string statement, SqlParameter[] paramsSql)
{
using (Entities dbContext = new Entities())
{
var total = dbContext.Database.SqlQuery<int>(statement, paramsSql).First();
return total;
}
}
The return value is whatever the USP returns. The method is called like (for a USP that returns an int):
var parameters = new List<SqlParameter>();
string statement = "exec uspPersonAdd @personName = @name, @activeFlag = @active";
parameters.Add(new SqlParameter("@name", person.PersonName));
parameters.Add(new SqlParameter("@active", person.Active));
int id = ExecuteSqlCount(statement, parameters.ToArray());
You need using System.Data.SqlClient;
这篇关于实体框架GetOrCreate？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
