C# entity framework fromsqlraw query with includes (in) instead of where clause

Question

What is the correct syntax for varname to make this query work? I can get it to work with a single variable like

string varname = "TOTAL_NORWAY"

However, if I want to have a few variables in there, I get an empty array returned:

    string varname = "'TOTAL_NORWAY', 'TOTAL_SWEDEN'";
    
   return await _Context.theDataModel.FromSqlRaw(@"
                        select data
                            from data_table 
                            where Variable in ({0})
                            
    
                    ", varname).ToListAsync();

becuase it is translated to select data from data_table where Variable in ('''TOTAL_NORWAY'', ''TOTAL_SWEDEN''') ... google for "ef core" where in array — Selvin
– Selvin, Commented Sep 15, 2020 at 14:31
string varname = "'TOTAL_NORWAY', 'TOTAL_SWEDEN'"; is a single string, not two values. Parameters are that - parameters to a function, not string substitution placeholders. That's why they protect from SQL injection attacks. Whatever is in those parameters is never included in the query itself, it's sent as separate parameters to the RPC call to the server — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 15, 2020 at 14:37
See my blog post here: erikej.github.io/efcore/sqlserver/2020/04/20/… — ErikEJ
– ErikEJ, Commented Sep 15, 2020 at 16:28
That's very helpful ErikEJ, thanks. My issue now is that I have additional where clauses that need to be included. — blomster
– blomster, Commented Sep 15, 2020 at 18:55

ESG · Accepted Answer · 2020-09-15 19:11:41Z

2

Remember that you can combine FromSqlRaw with Linq:


string varnames = new [] { "TOTAL_NORWAY", "TOTAL_SWEDEN" };

var query = _Context.theDataModel.FromSqlRaw(@"
                        select data
                            from data_table");
    
query = query.Where(x => varnames.Contains(x.Variable));
// Add more where clauses as needed
return await query.ToListAsync();

answered Sep 15, 2020 at 19:11

ESG

9,4754 gold badges41 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

blomster · Accepted Answer · 2020-09-15 19:18:23Z

ErikEJ's post was very helpful. The solution is not so trivial for someone who doesn't dabble in EF Core regularly.

I also had an extra where clause to consider, and this was done like so for anyone else wondering.

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

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

sqlParameters.Add(new SqlParameter("@userid", "userXYZ123"));


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

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

Collectives™ on Stack Overflow

C# entity framework fromsqlraw query with includes (in) instead of where clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related