How to pass list of values as Sql Param with FromSql in Entity framework core

entity-framework-core sql-server

Question

I need to pass list of ids to IN . I tried with below code but does not works. What is the issue with below code

    //SampleDBQueryType- is DbQuery
     databaseContext.SampleDBQueryType.FromSql(@"
        SELECT *
        From SampleTable
        WHERE UserObjectId IN (@userIds)", new SqlParameter("userIds", 
        GetCommaSeparatedStringValueForDbQuery(thirdPartyUserIds)))
        .ToList();

     //Reuturns => "'id1', 'id2'"
     private string GetCommaSeparatedStringValueForDbQuery(IEnumerable<string> values)
       {
         string qureryFilter = string.Empty;
         values.ToList().ForEach(v =>
         {
            qureryFilter = string.IsNullOrEmpty(qureryFilter) ? $"'{v}'" : $"{qureryFilter},'{v}'";
         });

        return qureryFilter;         
       }
1
0
4/7/2020 12:20:47 PM

Popular Answer

You will have to do something like this:

var items = new int[] { 1, 2, 3 };

var parameters = new string[items.Length];
var sqlParameters = new List<SqlParameter>();
for (int i = 0; i < items.Length; i++)
{
    parameters[i] = string.Format("@param_{0}", i);
    sqlParameters.Add(new SqlParameter(parameters[i], items[i]));
}

var rawCommand = string.Format("SELECT * from dbo.Shippers WHERE ShipperId IN ({0})", string.Join(", ", parameters));

var shipperList = db.Set<ShipperSummary>()
    .FromSqlRaw(rawCommand, sqlParameters.ToArray())
    .ToList();

foreach (var shipper in shipperList)
{
    Console.WriteLine(shipper.CompanyName);
}
0
4/7/2020 12:17:10 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow