3

I just want to select data for a range of values from the db, e.g.:

SELECT * from VwCampaigns where PmcId in (1,2,3)

This is my code in Entity Framework Core

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

How can I pass a range of integers in a raw query?

jps
  • 20,041
  • 15
  • 75
  • 79
RK001
  • 161
  • 2
  • 10

2 Answers2

2

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:

Given a list of IDs: 1,2,3

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.

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:

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

My working code:

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();

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

Ivan Agrenich
  • 2,221
  • 2
  • 10
  • 13
  • Thanks Ivan, Your query was returning only index's of PmcIds array so I just made small change and it worked. var sqlQuery = $"SELECT * dbo.FROM VwCampaigns WHERE PmcId in ({string.Join(',', pmcIds.Select(x => $"{x}"))}); once again thanks for your help – RK001 Nov 21 '19 at 05:38
  • 1
    No problem! I had the sql query returning indexes on purpose as the substitution works on an index basis. For example {0} would be substituted by the first item in the object array passed as the second argument to the .FromSql() method – Ivan Agrenich Nov 21 '19 at 09:36
  • If you are using .net framework 3.5+, first line can be replaced by `.OfType().ToArray()` – n0099 Apr 29 '22 at 20:10
0

Alternative orm tool ? For Visit https://dapper-tutorial.net/querymultiple

azorlua
  • 1
  • 3