CODEWITHSUNDEEP
c#sql
John
John

Reputation: 3945

Query returning wrong value?

Thanks to the help Ive recieved from zambonee on write a query that applies to an entire db instead of a table

Using EF i'm writing a query that returns the userId from the aspNet_Users table. Then Im using this ID to delete the records in membership, userINroles & users....However the userId the query is returning is the wrong value..and Im connected to the correct DB iv checked the connectionString and tested other data

    using (DEntities Context = DAOHelper.GetObjectContext<DEntities>())
{
Guid aspUserIdToRemove = Context.ExecuteStoreQuery<string>("Select UserId FROM aspnet_Users where UserName LIKE '%" + userName + "%'").ElementType.GUID;

string aspUserId = aspUserIdToRemove.ToString();
aspUserId = aspUserId.Replace("{", string.Empty);
aspUserId = aspUserId.Replace("}", string.Empty);

Context .ExecuteStoreCommand("DELETE FROM aspnet_Membership where UserId = '" + aspUserId + "'");
Context .ExecuteStoreCommand("DELETE FROM aspnet_UsersInRoles where UserId = '" + aspUserId + "'");
Context .ExecuteStoreCommand("DELETE FROM aspnet_Users where UserId = '" + aspUserId + "'");

aspUserIdToRemove returns {296afbff-1b0b-3ff5-9d6c-4e7e599f8b57} when it should return {31E62355-8AE2-4C44-A270-2F185581B742}...

{296afbff-1b0b-3ff5-9d6c-4e7e599f8b57} doesnt even exist in the DB...does anyone have any idea whats wrong? thanks

Just to reinforce about being on the same DB im doing further delete commands on different tables and confirming that they are deleted

Following on from the comments -

var s = dnnEntitiesDbContext.ExecuteStoreQuery<string>("Select UserId FROM aspnet_Users where UserName LIKE '%" + userName + "%'");

s.elementtype.GUID holds the 296afbff-1b0b-3ff5-9d6c-4e7e599f8b57

but s.base.elementType.baseType.Guid returns a diff GUID '81c5f.... but no sign of the one I am looking

Upvotes: 1

Views: 389

Answers (1)

Prisoner
Prisoner

Reputation: 1857

You may have some misunderstanding about ExecuteStoreQuery<T>, it will return the Type you specified. In your case, it will return string back.

Guid aspUserIdToRemove = Context.ExecuteStoreQuery<string>("Select UserId FROM aspnet_Users where UserName LIKE '%" + userName + "%'").ElementType.GUID;

With this statement, ExecuteStoreQuery<string> will return a string type of UserId, and then get the GUID from ElementType, but not GUID of Users

To solve that, you just need to use

string aspUserIdToRemove = Context.ExecuteStoreQuery<string>("Select UserId FROM aspnet_Users where UserName LIKE '%" + userName + "%'");

More better you may want to avoid SQL injection, and use parameter

string aspUserIdToRemove = Context.ExecuteStoreQuery<string>("Select UserId FROM aspnet_Users where UserName LIKE '%{0}%'", userName);

Details you can check the API

As the aspUserIdToRemove is a string, you don't need to use .ToString() on it. However, I don't have enough data, you may need to check do you need to escape the '{}'.

Moreover, from your comment, the \ is an escape character, if you want to concat in a string, you need to escape that with \\ (API)

Upvotes: 2

Related Questions