Entity Framework data reader incompatible with column names with spaces (database first)

user_4449777 0
Viewed 167

I'm trying to wrap Entity Framework (6.4.0) around a SQL View (SQL Server) where a couple of columns have spaces in the column names. For example, one column in SQL is "Badge ID", when I wrap EF around this view it renames the column "Badge_ID"

When I try to query, EF throws exception that the data reader is incompatible (makes sense):

System.Data.Entity.Core.EntityCommandExecutionException: The data reader is incompatible with the specified 'Model.vwEmpOrg'. A member of the type, 'Badge_ID' does not have a corresponding column in the data reader with the same name.'

I tried a solution from a code-first approach where in the class definition you can annotate the column name like so:

namespace EXAMPLE.Models
{
    using System.ComponentModel.DataAnnotations.Schema;
    

    public partial class vwEmpOrg
    {   
        [Column("Badge ID")] 
        public string Badge_ID { get; set; }

However the same is exception is still thrown. What am I missing? Why doesn't the annotation on the column name work?

model-view-controller view entity-framework tsql sql-server
1 Answers

You will need to post more code specific to your Entity, especially anything that has been auto-generated, and the underlying View because EF6 + SQL Server have no issues with Spaces in column names using DB First.

AFAIK you cannot leverage Code-First with SQL Views. Code First would expect to create a Table called vwEmpOrg. You can certainly map an EF Entity to an existing view using annotations or explicit entity configurations.

For example I have a Table called Persons with PersonId & Name, I create a View called vwPersons with:

SELECT PersonId AS [Person ID], Name
FROM dbo.Persons

Then for an entity:

[Table("vwPersons")]
public class ViewPersons
{
    [Key, Column("Person ID")]
    public int PersonId { get; set; }
    public string Name { get; set; }
}

This works perfectly fine. For argument's sake to ensure that EF wasn't pulling any defaulting to conventions, I renamed the view's column alias to "Some ID", and renamed the key in the Entity to "Fudgesicle" pointed at "Some ID", and it was fine pulling that. (Nothing linking back to a "PersonId" in the underlying table)

I'd check that your code is actually using that entity definition for vwEmpOrg or the mapped BadgeId and not that the DbContext is actually using a generated model class in a different namespace. (If you had been using EDMX's or similar)

user_4234970
Related