SQL Server How to pass list of values as Sql Param with FromSql in Entity Framework Core

omvjsjqw 于 2023-11-16 发布在其他

关注(0)|答案(1)|浏览(76)

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

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

// Returns => "'id1', 'id2'"
private string GetCommaSeparatedStringValueForDbQuery(IEnumerable<string> values)
{
    string queryFilter = string.Empty;

    values.ToList().ForEach(v =>
         {
            queryFilter = string.IsNullOrEmpty(queryFilter) ? $"'{v}'" : $"{queryFilter},'{v}'";
         });

    return queryFilter;         
}

sql-server

来源：https://stackoverflow.com/questions/61076208/how-to-pass-list-of-values-as-sql-param-with-fromsql-in-entity-framework-core

1条答案

按热度按时间

beq87vna1#

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

foreach (var shipper in shipperList)
{
    Console.WriteLine(shipper.CompanyName);
}

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server How to pass list of values as Sql Param with FromSql in Entity Framework Core

1条答案

相关问题

热门标签

最新问答