如何使用EF FromSql在子句中编写？

本文介绍了如何使用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();