Entity Framework Core CosmosDB upsert (update/insert) recognition

huangapple go评论79阅读模式
英文:

Entity Framework Core CosmosDB upsert (update/insert) recognition

问题

我有一个包裹,我一段时间前构建的,可以很好地与EF创建的SQL数据库一起使用。最近,有人要求尝试在CosmosDB中使用它,并使用guid作为“主键”。这是我第一次尝试Cosmos。

在保存记录时,我检查主键的值:如果它是该类型的默认值,我认为它是插入操作;如果不是,默认为更新操作。

public virtual TModel Save(TModel data)
{
    _dynamic.Add(data);
    _context.Entry(data).State =
        data.Id.Equals(default(TKey))
            ? EntityState.Added 
            : EntityState.Modified;
    _context.SaveChanges();
    return data;
}

我模糊地记得出于性能原因这样做,所以在更新记录之前,我不会从数据库中拉取记录。看起来似乎没有内置的upsert功能?

我相信在这里对于CosmosDB的插入操作,Id属性是被填充的,因为CosmosDB不生成自己的Id,而是依赖于EF提供它。这是明智的,但破坏了我的约定。

我想最终的问题是,是否有更好的处理“upserts”的方法,不依赖于Id是否未设置?

更多信息
传递的TModel data是来自用户请求的,此时:

  1. 我们还没有从数据库中拉取任何数据
  2. 在上下文中没有其他待处理的更改记录
  3. 还有另一个几乎相同的函数,它添加了一系列数据,然后调用SaveChanges,所以我们在这里对工作负载有一定的注意。

在这里执行upsert模式的原因是用户有一个单一的API端点,数据保存到这个端点,无论是新记录还是现有记录。

我们基本上通过告诉数据库“这是更新”或“这是插入”来绕过DB upsert性能问题,因为对于CosmosDB,插入记录的Id是在这一点上生成的,而在SQL版本中这不是问题。

看起来我可能需要更深入地了解CosmosDB的内部和它们与EF的交互。

英文:

I have a package I built a while back that works with EF-created SQL databases just great. I've recently had a call to try to use with CosmosDB and guid "primary keys". My first foray into Cosmos.

When saving records I'm checking the value of the primary key: if it's the default value for the type, I consider it an insert; if it's not, then I consider it an update.

public virtual TModel Save(TModel data)
    {
        _dynamic.Add(data);
        _context.Entry(data).State =
            data.Id.Equals(default(TKey))
                ? EntityState.Added 
                : EntityState.Modified;
        _context.SaveChanges();
        return data;
    }

I vaguely recall doing this for a performance reason, so I'm not pulling the records down from the database before updating them. It doesn't appear that there's a built-in upsert?

I believe the Id property is populated here on inserts for CosmosDB because CosmosDB doesn't generate its own Ids and instead relies on EF to provide it. Which is sensible, but breaks my convention here.

I suppose the ultimate question is, is there a better way to handle "upserts" here that doesn't rely on the Id being not-set?

More Info
The TModel data passed in is from a user request, at this point we:

  1. Haven't pulled anything down from the database
  2. Don't have any other records with pending changes in the context
  3. There's another, nearly identical function that adds a range of data then calls SaveChanges so we are being somewhat diligent about work load here

The reason for doing the upsert pattern here is that the user has a single API endpoint that data gets saved to, whether it's a new record or an existing one.

We are essentially circumventing the DB upsert performance issues by telling it "this is an update" or "this is an insert" with this method, it's simply broken down because the Id for an inserted record is generated at this point for CosmosDB, where that wasn't an issue with the SQL version.

It looks as though I may just need to dig deeper into the CosmosDB internals and their EF interactions.

答案1

得分: 1

Entity Framework Core CosmosDB upsert (update/insert) recognition:

>下面是一种更好的处理“upserts”的方式,不依赖于Id未设置的情况

Explanation:

  • try块中创建了一个新的TModel类型的数据记录,并使用存储库执行了upsert操作。

  • upsert操作会创建新文档(如果不存在)或更新现有文档。

  • upsert方法中定义了一个查询,用于确定是否存在具有指定角色的任何现有数据。

  • 使用查询迭代器执行查询,并使用using.FirstOrDefault()获取第一个结果。

  • 如果没有找到现有数据,则在没有具有指定“Role”属性的现有数据的情况下,为文档生成新的“id”,否则更新现有数据。

var client = new CosmosClient(cosmosEndpoint, cosmosKey);
var cosmosContainer = client.GetContainer("upsertDb", "upsertCont");

var repository = new YourRepository(cosmosContainer);
try
{
    var newData = new TModel
    {
        Role = "FullStackDeveloper",
        Name = "Balaji"
    };
    Console.WriteLine($"Role: {newData.Role}");
    await repository.Upsert(newData);

    Console.WriteLine("Upsert operation completed successfully.");
}
catch (CosmosException ex)
{
    Console.WriteLine($"CosmosException: {ex.Message}");
    Console.WriteLine($"Status Code: {ex.StatusCode}");
}
catch (Exception ex)
{
    Console.WriteLine($"An unexpected error occurred: {ex.Message}");
}

public class YourRepository
{
    private readonly Container _container;
    public YourRepository(Container container)
    {
        _container = container;
    }
    public async Task Upsert(TModel data)
    {
        var partitionKey = new PartitionKey(data.Role);
        try
        {
            var query = new QueryDefinition("SELECT * FROM c WHERE c.Role = @role").WithParameter("@role", data.Role);
            var queryIterator = _container.GetItemQueryIterator<TModel>(query);
            var existingData = (await queryIterator.ReadNextAsync()).FirstOrDefault();

            if (existingData == null)
            {
                data.id = Guid.NewGuid().ToString();
                await _container.CreateItemAsync(data, partitionKey);
            }
            else
            {
                data.id = existingData.id; // 在upsert之前设置现有的'id'
                await _container.UpsertItemAsync(data, partitionKey);
            }
        }
        catch (CosmosException ex)
        {
            throw;
        }
    }
}

Output:
Entity Framework Core CosmosDB upsert (update/insert) recognition

Entity Framework Core CosmosDB upsert (update/insert) recognition

英文:

Entity Framework Core CosmosDB upsert (update/insert) recognition:

>Below is a better way to handle "upserts" here that doesn't rely on the Id being not-set

Explanation:

  • A new data record of type TModel is created inside the try block, and an upsert operation is carried out using the repository.

  • An upsert operation either creates a new document if it doesn't exist or updates an existing document.

  • A query is defined in the upsert method to determine whether any existing data with the specified Role exists.

  • A query iterator is used to perform the query, and the first result is obtained using.FirstOrDefault().

  • If no existing data is found, a new "id" is generated for the document in case there is no existing data with the specified "Role" property else it updates the existing data.

var client = new CosmosClient(cosmosEndpoint, cosmosKey);
var cosmosContainer = client.GetContainer(&quot;upsertDb&quot;, &quot;upsertCont&quot;);

var repository = new YourRepository(cosmosContainer);
try
 {
    var newData = new TModel
     {
       Role = &quot;FullStackDeveloper&quot;,
       Name = &quot;Balaji&quot;
     };
Console.WriteLine($&quot;Role: {newData.Role}&quot;);
await repository.Upsert(newData);

Console.WriteLine(&quot;Upsert operation completed successfully.&quot;);
 }
catch (CosmosException ex)
 {
   Console.WriteLine($&quot;CosmosException: {ex.Message}&quot;);
   Console.WriteLine($&quot;Status Code: {ex.StatusCode}&quot;);
 }
catch (Exception ex)
 {
   Console.WriteLine($&quot;An unexpected error occurred: {ex.Message}&quot;);
 }

public class YourRepository
{
  private readonly Container _container;
  public YourRepository(Container container)
  {
     _container = container;
  }
  public async Task Upsert(TModel data)
   {
     var partitionKey = new PartitionKey(data.Role);
	 try
     {
       var query = new QueryDefinition($&quot;SELECT * FROM c WHERE c.Role = @role&quot;).WithParameter(&quot;@role&quot;, data.Role);
       var queryIterator = _container.GetItemQueryIterator&lt;TModel&gt;(query);
       var existingData = (await queryIterator.ReadNextAsync()).FirstOrDefault();

       if (existingData == null)
       {
          data.id = Guid.NewGuid().ToString();
          await _container.CreateItemAsync(data, partitionKey);
       }
       else
       {
          data.id = existingData.id; // Set the existing &#39;id&#39; before upserting
          await _container.UpsertItemAsync(data, partitionKey);
       }
     }
     catch (CosmosException ex)
     {
       throw;
     }
   }
}

Output:
Entity Framework Core CosmosDB upsert (update/insert) recognition

Entity Framework Core CosmosDB upsert (update/insert) recognition

huangapple
  • 本文由 发表于 2023年8月9日 14:51:25
  • 转载请务必保留本文链接:https://go.coder-hub.com/76865261-2.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定