JSON序列化需要很长时间

I have two ViewModels (Product and Part):

我有两个视图模型(产品和部分):

public class ProductViewModel
{
    public int Id { get; set; }

    public string Name { get; set; }

    public string Description { get; set; }       

    public bool IsActive { get; set; }

    public IEnumerable<PartViewModel> Parts { get; set; }
}

public class PartViewModel
{
    public int Id { get; set; }

    public string Name { get; set; }

    public bool IsActive { get; set; }
}

I run an EF Core query that returns 1727 Products with at least 1 Part per product. An example of a return of 1 of these would serialize to JSON as such:

我运行一个EF核心查询，它返回1727个产品，每个产品至少有1个部分。返回其中一个的示例将序列化为JSON:

[  
   {  
      "Id":8761,
      "Name":"Product Name 1",
      "Description":"This is a product",
      "IsActive":true,
      "Parts":[  
         {  
            "Id":103767,
            "Name":"Name 1"
            "IsActive":true
         },
         {  
            "Id":156436,
            "Name":"Name 2",
            "IsActive":true
         },
         {  
            "Id":109436,
            "Name":"Name 3",
            "IsActive":true
         }
      ]
   }
]

Now, this works well with a query where I .Take(10), albeit seemingly slow, but when I try and serialize 1727 records, it bogs down and a five minute wait doesn't even complete the serialization process.

现在，对于一个查询，我. take(10)，虽然看起来很慢，但是当我尝试序列化1727条记录时，它陷入了困境，5分钟的等待甚至不能完成序列化过程。

I tried using Json.Net as such:

我试着使用Json。网络是这样的:

var ret = JsonConvert.SerializeObject(products, new JsonSerializerSettings { Formatting = Formatting.Indented });

I only decided to try and use JsonConvert from Json.Net because, in my Controller Action, when attempting to return a JsonResult, the following code to convert my object to JSON had the same efficiency issue:

我只是决定尝试使用Json格式的JsonConvert。Net，因为在我的控制器操作中，当尝试返回一个JsonResult时，下面的代码将我的对象转换为JSON，同样存在效率问题:

return Json(products);

I get the products via EF Core as such:

我通过EF Core获得产品如下:

var products = _context.Products.OrderBy(o => o.Name).Where(w => w.IsActive  //all products are active
            && (w.Parts.Count(c => c.IsActive) > 0)) //remove parts that are
            .Select(pr => new ProductViewModel
            {
                Id = pr.Id,
                Name = pr.Name,
                Description = pr.Description,
                IsActive = pr.IsActive,
                Parts = pr.Parts.OrderBy(o => o.Name).Where(w => w.IsActive)  //all parts are active
                .Select(prt => new PartViewModel
                {
                    Id = prt.Id,
                    Name = prt.Name,
                    IsActive = prt.IsActive,
                })
            }).ToList();

What can I do?

我能做什么?

1 个解决方案

#1

Serialization is not the big deal, it's easy to notice now that you have added the LINQ query that the issue is the poor SQL Entity Framework will generate from it.

序列化不是什么大问题，现在很容易注意到，您已经添加了LINQ查询，这个问题是由糟糕的SQL实体框架生成的。

First of all, you should be using eager loading to join your products table with the parts table. You can do that simply by adding an Include method call.

首先，您应该使用eager loading将产品表与parts表连接起来。您可以通过添加一个Include方法调用来实现这一点。

_context.Products.Include(p => p.Parts)

If this is not done the query is actually doing N + 1 queries. You should use a simple trick to watch the actual SQL queries your query is doing adding this code to your DbContext. (Do this only if you are using EF6, EF Core does query logging for you.)

如果没有这样做，查询实际上是在执行N + 1查询。您应该使用一个简单的技巧来观察查询将此代码添加到DbContext的实际SQL查询。(只有在使用EF6时才这样做，EF Core为您进行查询日志记录。)

public YourDBContext()
{
   #if DEBUG   
   this.Database.Log = msg =>
   {
       Debugger.Log(1, "ALL", "EF DB SQL: " + msg + Environment.NewLine);
   };
   #endif
}

Another condition that is doing the query take long is .Where(w => w.IsActive && (w.Parts.Count(c => c.IsActive) > 0)). I guess Entity Framework is generating a HAVING Clause, but it would help if you posted the generated SQL to optimize the query.

执行查询的另一个条件是. where (w => w)。IsActive & &(w.Parts。计数(c => c. isactive) > 0)我猜实体框架正在生成一个have子句，但如果您发布生成的SQL来优化查询，这将有所帮助。

Finally, a microoptimization in your Select method, would be to change the Parts property fetch expression by.

最后，在您的选择方法中进行一个微优化，将会更改部件属性获取表达式。

// other properties ...
Parts = pr.Parts.Where(w => w.IsActive).OrderBy(o => o.Name),
// other properties ...

This would prevent your database to fetch and sort the inactive parts.

这将阻止数据库获取和排序不活动的部分。

#1