Error converting string to GUID in c#

Question

I am using the following code for performing a delete operation via EF code first using a inline query internally

 void IRepository<T>.Delete(params Guid[] ids)
    {
        var sql = string.Format("UPDATE {0} SET [IsDeleted] = 1 WHERE [Id] IN (@ids) ", GetTableName());
        string sep = String.Join(", ", ids.Select(x => "'" + x + "'"));
        var sqlParams = new Object[]
        {
            new SqlParameter("ids", string.Join(",",sep)), 
        };
        DataContext.Database.ExecuteSqlCommand(sql, sqlParams);
    }

Now when I execute the command it gives me

conversion failed when converting from a character string to uniqueidentifier

error.

Hiwever when I run the query in sql say.

   UPDATE [dbo].[Table] SET [IsDeleted] = 1 WHERE [Id] IN ('20Cr0BCA-6EBB-E411-A04B-BC305BA8C713','506c79c1-6ebb-e411-a04b-bc305ba8c733')

it works fine.

Is this possible to do this way ?Or what am I doing wrong ?

You can't use `IN` with a variable. See http://stackoverflow.com/questions/337704/parameterize-a-sql-in-clause for more. — Jeroen Mostert, Feb 24 '15 at 13:14
I think the problem ist this GUID: `20Cr0BCA-6EBB-E411-A04B-BC305BA8C713`. A GUID mustn't contain a "r" since it is composed of hexadecimal digits — Thomas Lielacher, Feb 24 '15 at 14:08
@Targarian Did it solve the problem or was the typo just in the question and not in the actual code? — Thomas Lielacher, Feb 24 '15 at 14:39

TYY · Answer 1 · 2015-02-24T14:05:34.260

1

I would do something like this

void IRepository<T>.Delete(params Guid[] ids)
{
    if (ids == null || !ids.Any())
        return;
    var idParams = ids.Select((x, cnt)=> new { ParamName ="@ids"+ cnt, Param = x});
    var sql = string.Format("UPDATE {0} SET [IsDeleted] = 1 WHERE [Id] IN ("+ String.Join(", ",idParams.Select(x =>  x.ParamName)) + ") ", "Table");
    var sqlParams = idParams.Select(x=> new SqlParameter(x.ParamName, x.Param)).ToArray(); 
    DataContext.Database.ExecuteSqlCommand(sql, sqlParams);
}

edited Feb 24 '15 at 14:05

answered Feb 24 '15 at 13:48

TYY

2,702
1
13
14

I still get Conversion failed when converting from a character string to uniqueidentifier. executing it :( @tyy – wickjon Feb 24 '15 at 14:03

Error converting string to GUID in c#

1 Answers1