如何使用EF FromSql在子句中编写？ [英] How to Write In Clause with EF FromSql?

问题描述

我的参数中包含未知数量的参数。如何编写它使其与EF Core兼容？

I have an unknown amount of parameters for my inclause. How can I write it so it works with EF Core?

  var formattedValues = String.Join(",", values.Select(s => "'" + s + "'"));
            var identifierParam = new SqlParameter("jsonColumn", $"$.{identifierKey}");
            var filterValueParam = new SqlParameter("value", $"%{formattedValues}%");

            var items = dbContext.Items.FromSql("select * FROM Items WHERE  AND JSON_VALUE(Attributes, @jsonColumn) in (@value)", filterValueParam, identifierParam).ToList();

我认为这是因为in子句中的每个值都需要它自己的参数。

I think it is because each value in the in clause needs it's own Parameter.

推荐答案

由于使用的是SQL Server，因此可以将值列表作为JSON传递。 EG

Since you're using SQL Server, you can pass the list of values as JSON. EG

  var jsonValues = JsonConvert.SerializeObject(values.ToList());
  var filterValueParam = new SqlParameter("@values", jsonValues );
  var identifierParam = new SqlParameter("@jsonColumn", $"$.{identifierKey}");

  var sql = "select * FROM Items WHERE  AND JSON_VALUE(Attributes, @jsonColumn) in (select value from openjson(@values))";

  var items = dbContext.Items.FromSql(sql, filterValueParam, identifierParam).ToList();

这篇关于如何使用EF FromSql在子句中编写？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！