how can I pass an array to a execute store command?

Question

How to pass an array of integer separated by comma to an ExecuteStoreCommandin the entities as a parameter I am not able to execute this :

this.ObjectContext.ExecuteStoreCommand("INSERT INTO SurveyPatientListMrns 
  (UserData, MrnId) SELECT DISTINCT '{0}' , MrnId 
FROM PatientVisits WHERE (FacilityId = {1})
AND (UnitId IN ({2}))", userData, facilityId, (string.Join(",", unitIds)));

Here (string.Join(",", unitIds)) is a string and i can not cast it as integer because of the commas. How can i pass the parameter then?

FYI, unitIds is a array of integers

akatakritos · Accepted Answer · 2012-11-01 12:40:00Z

Though it looks like a string.Format operation, ExecuteStoreCommand is internally building a parameterized query to increase performance and help protect you from SQL injection attacks. (MSDN)

When you do your string.Join as a parameter to ExecuteStoreCommand, it treats that result not as a list of values for the IN clause, but a string that just happens to look like one. Basically it will generate an IN clause that looks like this:

(UnitId IN ('1,2,3'))

Which is obviously not what you want.

You're going to have to build the SQL command with the string.Join-ed list of uinitIds BEFORE passing it ExecuteStoreCommand:

string query = @"INSERT INTO SurveyPatientListMrns  (UserData, MrnId) 
    SELECT DISTINCT '{0}' , MrnId 
    FROM PatientVisits WHERE (FacilityId = {1}) AND 
    (UnitId IN (" + string.Join(",", unitIds) + "))";
this.ObjectContext.ExecuteStoreCommand(query, userData, facilityId);

Normally one should avoid dynamically building SQL queries because of the possibility of a SQL injection attack, but in this case, you know that unitIds is a list of integers, and therefore you should be OK.

Tore Aurstad · Accepted Answer · 2020-03-06 11:28:21Z

Same approach as the answer, just demonstrating using strongly typed result set.

void Main()
{
    int[] operationIds = { 1000, 1001 };
    var result = ObjectContext.ExecuteStoreQuery<EncyptedPatientInfoDataContract>(
                        $@"SELECT OperationId, Name, OfficialId, IsPatientEncrypted FROM Patient WHERE OperationId IN ({string.Join(",", operationIds)})");
                        result.Dump();
}

Dump method is a method in Linqpad. Screenshot of working sample in Linqpad 5:

As the accepted answer said:

Build a query string against the store where you build up the IN clause using for example string.Join to build up the comma separated array of string values
- When using ExecuteStoreQuery you can in case you use a SELECT project the data into a strongly typed entity of your choice - as long as its properties and respective property types match up with database contents such that Entity Framework can successfully materialize the contents into the POCO object you use as a generic argument.

Collectives™ on Stack Overflow

how can I pass an array to a execute store command?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related