EF4.1 - Fluent API - SqlQuery - configuration mappings when calling sproc - Data reader is incompatible with specified entity type

Question

The scenario - legacy application with 10 year history, has always used procedure calls for all data access - needs to be overhauled from a hybrid classic ASP and .NET set of pages.

The goal - migrate to .NET 4.0 using EF 4.1 with Fluent API and continue using existing database sprocs as much as possible.

Key classes:

public class EntityBase
{
    public int Id { get; set; }
}

public class User : EntityBase
{
    public string UserName { get; set; }
...
}

Configurations:

internal class ConfigurationBase<T> : EntityTypeConfiguration<T> where T : EntityBase
{
    protected ConfigurationBase()
    {
        HasKey(t => t.Id);
    }
}

internal class UserConfiguration : ConfigurationBase<User>
{
    internal UserConfiguration()
    {
        Property(p => p.Id)
            .HasColumnName("Person_Id")
        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity)
            .IsRequired();
        Property(p => p.UserName)
            .HasMaxLength(64);
        ToTable("Person");
    }
}

The context is all set up in DbContext.OnModelCreating as:

    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new UserConfiguration());
    }

And all is fine when I access the data directly via the context eg:

    public override IQueryable<User> GetAll()
    {
        return UnitOfWork.Context.Users;
    }

But when I attempt to use an existing sproc which contains the following:

SELECT  p.Person_Id,
        p.IsUser,
        p.FirstName,
        p.LastName,
        p.UserName,
        p.Email,
        p.CreatedBy,
        p.CreatedDate,
        p.IsActive,
        p.ModifiedBy,
        p.ModifiedDate
FROM    Person p 
WHERE   p.UserName = @UserName
AND     p.IsActive = 1

I execute the following:

    public User AuthorizeUser(string userName)
    {
        SqlParameter p = new SqlParameter { 
            DbType = DbType.String,
            ParameterName = "UserName",
            Size = 64,
            Value = userName
        };
        object[] parameters = new object[] {p};

        return UnitOfWork.Context.Users.SqlQuery(CPODSStoredProcedures.User_AuthorizeUser, parameters).FirstOrDefault();
    }

And I get: The data reader is incompatible with the specified 'User'. A member of the type, 'Id', does not have a corresponding column in the data reader with the same name.

I have traced the execution and the configurations are being read, so am I doing something wrong, or is the sproc execution by SqlQuery not paying attention to how the base Id is remapped to Person_Id in this case.

Thanks in advance! G

Answer 1

EF 4.1 Code First (ie: the fluent API you're trying to use) doesn't support Stored Procedures. See here.

If you want to do this with EF, you have no choice but to create a model (or maybe wait until the next release). Personally I don't think that's a problem, because since the database already exists creating a model using DB First is really easy (point it at those tables and say "build this"). You can then switch EF to use the DbContext generator and get nice clean POCO classes to work with your data. Here's a simple explanation on how to do that.

I know the fluent API is the buzzword compliant shiny thing right now, but it's also brand new in this release of EF and not everything is there yet. With a legacy app it's going to save you a tremendous amount of headaches to simply use the DB First model and POCO class generators instead.