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

Mapping incompatible sproc (or transforming sproc result sets into new forms) to your object model is as easy as using a table var and sp_executesql as a batch provided in your call to DbContext.SqlQuery(). See my blog for a working example of this in action->

http://blogs.msdn.com/b/schlepticons/archive/2012/10/15/yes-you-can-execute-parameterized-sprocs-with-ef-fluent-api-code-only-and-more.aspx

Answer 2

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.

Answer 3

To execute a stored procedure in EF you need to create a function import. My company is in the same boat. We migrated to EF and have a billion procs that we need to keep using until some point when we deprecate them. EF actually makes it really easy to use stored procs through the use of function imports.

1. Go to Update Model from Database.

2. In the "Add" screen find the stored procedure you wish to use and check the box next to it.

3. Click finish.

4. Right-click the EF design surface and go to "Add" > "Function Import".

5. Choose the proc you wish to map.

6. Click "Get Column Information".

7. Click generate new "Complex Type". (or if you have an entity mapped, you can return an instance of the entity instead of the complex type.)

8. Click OK. You are done.

After adding a function import you will then have a method on your EF context object, e.g.:

EntityContainter context = new EntityContainer();
User user = context.AuthorizeUser(username);

I wrote a blog on this process, here: http://www.codetunnel.com/blog/post/53/how-to-map-a-stored-procedure-to-an-entity-in-entity-framework-4

Hope this helps!

Answer 4

I abandoned EntityFramework for nHibernate a couple versions ago, but I am pretty sure your properties Id and UserName need to be flagged virtual, as with most POCOs used in ORMs, no?