data reader is incompatible with the specified. does not have a corresponding column in the data reader with the same name

Question

I keep getting the below exception. The exception baffles me because I am not selecting UserID.

I have tried changing the select to SELECT * but this just causes a result.Count of 0 regardless if the data exists or not.

I have a dummy record in the Database called bob tob.

I have noticed that if I hover over the Users part of db.Users.SqlQuery.

The text inside is

{SELECT 
[Extent1].[userID] AS [userID], 
[Extent1].[userFirstName] AS [userFirstName], 
[Extent1].[userLastName] AS [userLastName], 
[Extent1].[userName] AS [userName], 
[Extent1].[userEmail] AS [userEmail], 
[Extent1].[userPassword] AS [userPassword], 
[Extent1].[userStatus] AS [userStatus], 
[Extent1].[userEmailVerificationStatus] AS [userEmailVerificationStatus], 
[Extent1].[userActivationCode] AS [userActivationCode]
FROM [dbo].[Users] AS [Extent1]}

I am guessing it's trying to SELECT all of the User class items regardless? If that's true Then how do I stop this? What am I missing?

This is the exception:

The data reader is incompatible with the specified 'UserRegistrationPasswordsModel.User'. A member of the type, 'userID', does not have a corresponding column in the data reader with the same name.

Below is the code that calls EmailExists. I added the ToString() falsely hoping that maybe was the issue.

#region EmailExist
// Email already exists?
if (Utilities.EmailExists(user.userEmail.ToString()))
{
    // A pretty awesome way to make a custom exception
    ModelState.AddModelError("EmailExist", "Email already exists");

    // Bounce back
    return View(user);
}
#endregion

Below is the procedure that is supposed to check if the email exists or not.

#region EmailExists
    // Confirm if the Email exists or not
    public static bool EmailExists(string Email)
    {
        bool result = false;
        Models.UserRegistrationPasswordsEntities1 db = new Models.UserRegistrationPasswordsEntities1();
        var queryResult = db.Users.SqlQuery("SELECT userEmail FROM Users WHERE userEmail = @1;", 
                                                                          new SqlParameter("@1", Email)).FirstOrDefault();

        // the ternary opertor is pretty awesome
        result = queryResult.Result.GetType() == null ? false : true;

        return result;
    }
    #endregion

This is the table structure:

CREATE TABLE [dbo].[Users]
(
    [userID]                      INT              IDENTITY (1, 1) NOT NULL,
    [userFirstName]               VARCHAR (50)     NOT NULL,
    [userLastName]                VARCHAR (50)     NOT NULL,
    [userName]                    VARCHAR (50)     NOT NULL,
    [userEmail]                   VARCHAR (50)     NOT NULL,
    [userPassword]                VARCHAR (100)    NOT NULL,
    [userStatus]                  BIT              DEFAULT ((0)) NOT NULL,
    [userEmailVerificationStatus] BIT              DEFAULT ((0)) NOT NULL,
    [userActivationCode]          UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
    [userDateOfBirth]             DATETIME         NOT NULL,

    PRIMARY KEY CLUSTERED ([userID] ASC)
);

And this is the User model class:

public partial class User
{
    public int userID { get; set; }

    [Display(Name = "First Name")]
    [DataType(DataType.Text)]
    [Required(AllowEmptyStrings = false, ErrorMessage ="First name required")]
    public string userFirstName { get; set; }

    [Display(Name = "Last Name")]
    [DataType(DataType.Text)]
    [Required(AllowEmptyStrings = false, ErrorMessage = "Last name required")]
    public string userLastName { get; set; }

    [Display(Name = "Username")]
    [DataType(DataType.Text)]
    [Required(AllowEmptyStrings = false, ErrorMessage = "Username required")]
    public string userName { get; set; }

    [Display(Name = "Email")]
    [DataType(DataType.EmailAddress)]
    [Required(AllowEmptyStrings = false, ErrorMessage = "email is required")]
    public string userEmail { get; set; }

    [Display(Name = "Date Of Birth")]
    [DataType(DataType.DateTime)]
    [Required(AllowEmptyStrings = false, ErrorMessage = "Date of Birth is required")]
    [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy}")]
    public DateTime userDateOfBirth { get; set;}


    [Display(Name = "Password")]
    [DataType(DataType.Password)]
    [Required(AllowEmptyStrings = false, ErrorMessage = "Password is required")]
    [MinLength(6, ErrorMessage = "Minimum of 6 characters required")]
    public string userPassword { get; set; }

    [Display(Name = "Confirm Password")]
    [DataType(DataType.Password)]
    [Required(AllowEmptyStrings = false, ErrorMessage = "Confirm password is required")]
    [Compare("userPassword", ErrorMessage = "Confirm password and password do not match")]
    public string userConfirmPassword { get; set; }

    public bool userStatus { get; set; }
    public bool userEmailVerificationStatus { get; set; }

    public System.Guid userActivationCode { get; set; }
}

I have spent the better part of 2 hours trying to figure this out.

Below are resources I have accessed trying to find a solution.

Any and all help is appreciated.

https://learn.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/advanced-entity-framework-scenarios-for-an-mvc-web-application

https://forums.asp.net/t/1991176.aspx?The+data+reader+is+incompatible+with+the+specified+model

Incompatible Data Reader Exception From EF Mapped Objects

The data reader is incompatible with the specified Entity Framework

https://forums.asp.net/t/1980259.aspx?I+get+this+error+The+data+reader+is+incompatible+with+the+specified+DBLaxmiTatkalModel+TblAttendence+A+member+of+the+type+AttendenceId+does+not+have+a+corresponding+column+in+the+data+reader+with+the+same+name+

What can cause an EntityCommandExecutionException in EntityCommandDefinition.ExecuteStoreCommands?

Answer 1

Error says:

A member of the type, 'userID', does not have a corresponding column in the data reader with the same name.

That means results returned by your query do not match type of your entity (User). And indeed they do not - your query return single column userEmail, while type User has much more columns (including userID mentioned in error message). Why results should match type User? Because you are querying this entity by doing db.Users.SqlQuery.

Your query is also wrong (because '@1' is not parameter but literal string), but that doesn't matter, because you don't need to use raw sql query here anyway. Just do:

public static bool EmailExists(string Email)
{        
    using (var db = new Models.UserRegistrationPasswordsEntities1()) {
        return db.Users.Any(c => c.userEmail == Email);
    }       
}

If you want to issue arbitrary sql queries (though I should say once again that in this case there is absolutely no reason to do this) - use db.Database.SqlQuery:

using (var db = new Models.UserRegistrationPasswordsEntities1()) {
    var email = db.Database.SqlQuery<string>(
        "SELECT userEmail FROM Users WHERE userEmail = @email",
        new SqlParameter("email", Email))
    .FirstOrDefault();
    ...
}