Query returning wrong value?

Question

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

Just to clarify - does the statement "Select UserId FROM aspnet_Users where UserName LIKE '%" + userName + "%'" return the right GUID when run from the console? — meisen99, Sep 08 '17 at 00:59
yes when I run Select UserId FROM aspnet_Users where UserName LIKE '%JimBob%' in SQL it returns 31E62355-8AE2-4C44-A270-2F185581B742 — John, Sep 08 '17 at 01:13
You're using EF incorrectly - you should avoid `ExecuteStoreQuery` and use the strongly-typed model-based API instead, and if you absolutely must use `ExecuteStoreQuery` then **USE PARAMETERISED QUERIES!** - your code is vulnerable to SQL injection attacks. — Dai, Sep 08 '17 at 01:23
Is it possible to just leave the GUID as a string value, rather than trying to turn it into a Guid type (which you then convert back to a string)? I'm wondering if there is a problem with casting between strings and GUIDs between SQL and C#. — meisen99, Sep 08 '17 at 01:24
@Dai Im working on existing code created by another user I'm trying to fix a bug... — John, Sep 08 '17 at 01:45
Change the `LIKE %%` operator to an equality operator - I'm guessing you're using a username substring that matches multiple users - or you're set-up to allow non-unique usernames. — Dai, Sep 08 '17 at 01:51
Ah - elementType is "string" and {296afbff-1b0b-3ff5-9d6c-4e7e599f8b57} is the GUID for a string. What is "s"? — meisen99, Sep 08 '17 at 01:59
That is, does string aspUserIdToRemove = Context.ExecuteStoreQuery("Select UserId FROM aspnet_Users where UserName LIKE '%" + userName + "%'"); return what you want? — meisen99, Sep 08 '17 at 02:01
@Dai ive added the domain the UN which is how they are stored 'BlackR\SodaBread123' this flags - Additional information: Incorrect syntax near '\'. why cant it search with a \? — John, Sep 08 '17 at 02:04
@meisen99 I had to write string aspUserIdToRemove = dnnEntitiesDbContext.ExecuteStoreQuery("Select UserId FROM aspnet_Users where UserName LIKE '%" + userName + "%'").ToString(); returns System.Data.Objects.ObjectResult`1[System.String] — John, Sep 08 '17 at 02:07
@dai ive just tried this and it is return the wrong GUID value again...any ideas? — John, Sep 08 '17 at 02:30
@John Run the query in SSMS and look at **all** the results - not just the first row. Get the query so it works in SSMS first before making trial-and-error changes to your program source code. And if you're meant to be doing exact-match searches don't use the `LIKE` operator. — Dai, Sep 08 '17 at 02:41
the query works in SSMS it always has that was the firs thing I tested before posting a Q and there is only one result is the query — John, Sep 08 '17 at 02:46

score 2 · Answer 1 · answered Sep 08 '17 at 02:48

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)

Query returning wrong value?

1 Answers1

Linked