实体框架将参数传递给Where IN子句原始查询的核心

问题描述：

我只想从db中选择值范围的数据.例如从VwCampaigns中选择*，其中(1,2,3)中的PmcId"

I just want to select data for range of values from db. e.g. "SELECT * from VwCampaigns where PmcId in (1,2,3)"

这是我在Entity Core中的代码

This is my code in Entity Core

_dbContext.CampaignsView.FromSql("SELECT * from VwCampaigns where PmcId in ({1})", pmcIds).ToList()

如何在原始查询中传递整数范围?请帮助

How to pass range of integer in raw query? Please Help

答

我认为没有任何方法可以通过将Ids列表作为参数传递，因为EF试图将其解释为单个参数.对我有用的是:

I do not think there is any way to do it by passing a list of Ids as a parameter because EF tries to interpret it as a single parameter. What worked for me was the following:

给出ID列表:1、2、3

Given a list of IDs: 1,2,3

将列表转换为对象数组，以便您可以将其作为参数传递给FromSql，因为它接受params object []，并且EF核心会将它们视为单独的参数.

Convert the list into an array of objects so that you can pass as an argument into FromSql as that accepts params object[] and EF core will treat them as separate parameters.

构造一个查询，该查询将为ID列表中的每个项目创建单独的参数，从而使您最终获得如下查询:

Construct a query that will create separate parameters for each item in the list of IDs so that you end up with a query that looks like the following:

从VwCampaigns中选择*，其中({0}，{1}，{2})中的PmcId"

"SELECT * from VwCampaigns where PmcId in ({0},{1},{2})"

我的工作代码:

var pmcIds = new List<int> { 1,2,3 }.Select(x => (object)x).ToArray();
var sqlQuery = $"SELECT * FROM dbo.VwCampaigns WHERE Id in ({string.Join(',', pmcIds.Select(x => $"{{{Array.IndexOf(pmcIds, x)}}}"))})";

var results = _dbContext.CampaignsView.FromSql(sqlQuery, pmcIds).ToList();

或者，您也可以像这样使用SQL命令:马克·拜尔斯的答案

Alternatively you can use a SQL command like so: Mark Byers' Answer

实体框架将参数传递给Where IN子句原始查询的核心

相关推荐