Help with SQL/LINQ Debugging

Question

I'm having trouble with the following statement, which is returning the error "Sequence contains no elements":

var vUser = (from u in this.dcLAUNCHOnline.aspnet_Users
                             where u.UserName.Equals(this.wCreateUser.UserName)
                             select u).Single();

The SQL being generated:

SELECT [t0].[ApplicationId],
 [t0].[UserId],
 [t0].[UserName],
 [t0].[LoweredUserName],
 [t0].[MobileAlias],
 [t0].[IsAnonymous],
 [t0].[LastActivityDate],
 [t0].[FirstName],
 [t0].[LastName],
 [t0].[Address_Street],
 [t0].[Address_City],
 [t0].[Address_Province],
 [t0].[Address_Country],
 [t0].[Address_PostalCode],
 [t0].[Telephone_Main_AreaCode],
 [t0].[Telephone_Main_Prefix],
 [t0].[Telephone_Main_LineNumber],
 [t0].[Telephone_Main_Extension],
 [t0].[Telephone_Mobile_AreaCode],
 [t0].[Telephone_Mobile_Prefix],
 [t0].[Telephone_Mobile_LineNumber],
 [t0].[Telephone_Mobile_Extension],
 [t0].[Telephone_Other_AreaCode],
 [t0].[Telephone_Other_Prefix],
 [t0].[Telephone_Other_LineNumber],
 [t0].[Telephone_Other_Extension],
 [t0].[Gender],
 [t0].[BirthDate]
FROM [dbo].[aspnet_Users] AS [t0]
WHERE [t0].[UserName] = @p0
-- @p0: Input NVarChar (Size = 20; Prec = 0; Scale = 0) [[email protected]]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4918

When run in SQL server management studio, the script does return the row I expect (which is in the table)

I defined p0 with this line:

DECLARE @p0 NVarChar(20) = '[email protected]'

Any ideas why this is failing? Thanks!

Answer 1

.Single() always fails if the collection it is called upon is empty or contains more than one element. SQL Server does not return any rows, which must be the case here.

You could either use .FirstOrDefault() or .SingleOrDefault() and check the return value against null, depending on whether you expect a single element to be returned by your query.

E.g. you have a unique constraint on the row "UserName" you filter upon, you should use .SingleOrDefault(). If null is returned, no row has been found. Multiple rows wil never be returned.

Answer 2

The InvalidOperationException that you are getting is thrown only when the query yielded no results.

The only thing that comes to my mind, (since you say that you are sure that the row exists on the database), is that you are maybe connecting to other database.

Check your DataContext's connection string and make sure you are querying the same database as in Management Studio.

Edit: BTW, you are querying directly the SqlMembershipProvider aspnet_Users table, to find users by UserName, you might want to give a look to the Membership.FindUsersByName method.