I am struggling to properly fetch data from a SQL view using the DbContext and the entity below:
public class StagingDbContext : ApplicationDbContext
{
private const string AssignmentQuery =
@"SELECT DISTINCT " +
@"[Region], " +
@"[Technician], " +
@"[Email], " +
@"[On Call Group], " +
@"[Manager], " +
@"[Manager Email] " +
@"FROM [Staging].[dbo].[Assignment] " +
@"WHERE " +
@"[Technician] IS NOT NULL AND " +
@"[Technician] NOT LIKE 'DECOMMISSIONED' " +
@"AND [Technician] NOT LIKE 'N/A' " +
@"AND [Technician] NOT LIKE '4242NAH%' " +
@"AND [Region] IS not Null " +
@"AND [On Call Group] NOT LIKE 'N/A' " +
@"AND [On Call Group] NOT LIKE 'Decommisioned'";
public IEnumerable<Assignment> Assignments => Database.SqlQuery<Assignment>(AssignmentQuery);
public StagingDbContext ()
: base(nameof(StagingDbContext ))
{
}
}
[Table("Assignment")]
public class Assignment
{
[Column("Region")]
public string Region { get; set; }
[Column("SiteID")]
public string SiteId { get; set; }
[Column("Technician")]
public string Technician { get; set; }
[Column("Email")]
public string Email { get; set; }
[Column("On Call Group")]
public string OnCallGroup { get; set; }
[Column("Manager Email")]
public string ManagerEmail { get; set; }
}
My issue arises when it comes to the property names that does not match exactly the ones provided in the query, I thought that the ColumnAttribute was taken in account but apparently this is not really the case.
My current workaround is to basically rename columns in my SQL query ([Column Name] AS ColumnName in order to make them match directly to the property names of my entity (e.g. removing white space characters) but I found it a bit awkward to rewrite the Query exclusively for that particular purpose.
Is there anything that can be done to force the SqlQuery to the names provided by my ColumnAttributes instead of the properties?
[EDIT] It was more or less already answered somewhere else:
- Entity framework Code First - configure mapping for SqlQuery
- Entity Framework using Database.SqlQuery() where column names are invalid C# member names
- Object Mapping from stored procedure using the columnname attribute in EntityFramework CodeFirst
- How to use Entity Framework to map results of a stored procedure to entity with differently named parameters
- getting Entity Framework raw query to respect attributes
