Why is my DbModelBuilder configuration ignored when matching Entity from DbSet <T> .SqlQuery?

Question

I have a DbModel configuration, for example:

 modelBuilder.Entity<WishlistLine>() .HasKey(w => w.PersistenceKey) .Property(w => w.PersistenceKey) .HasColumnName("WishlistLineId"); 

My request is executed using the following two methods:

 public IEnumerable<WishlistLine> FetchWishlistLinesUsingLogonName(string logonName) { return GetFromRawSql(@" SELECT wl.* FROM WishlistLines wl INNER JOIN Accounts a ON wl.AccountId = a.AccountId LEFT JOIN Users u ON u.AccountId = a.AccountId WHERE u.LogonName = @p0", logonName); } protected IEnumerable<TEntity> GetFromRawSql(string sqlQuery, params object[] parameters) { return _dbSet.SqlQuery(sqlQuery, parameters).ToList(); } 

I can “save” WishlistLines to the database through EF without any problems. When I run this request, although I get this error:

The data reader is incompatible with the specified 'DataAccessLayer.DatabaseContext.WishlistLine'. A member of the type, 'PersistenceKey', does not have a corresponding column in the data reader with the same name.

I realized that using DbSet<T>.SqlQuery() will display the returned data in entities, but it seems to ignore the DbModel configurations. Judging by the error message, the wrong data reader is being used.

So:

A) Am I doing something wrong?

B) is there a way to use the EF DbModel -aware entity mapper?

Answer 1

Indeed, column name matching is ignored when executing a raw SQL query. Here are two links: This rather unsatisfactory stream is just for fun, but the following with a serious response from the EF team:

Quote from http://entityframework.codeplex.com/workitem/233 :

The SqlQuery method is designed to ignore any mapping, including a mapping that is applied using attributes. It just matches the column names from the results with the property names in the object. If the column names do not match, you will need to use the column alias (AS keyword in SQL Server) to rename the column in the results.

We agree that it would be useful to be able to make SqlQuery honor the attributes of the column, so we keep this problem open and this is our lag for future consideration.

Thus, the only workaround is to express the alias AS instead of * in your SQL query, which indicates your property name as a column alias:

 return GetFromRawSql(@" SELECT wl.WishlistLineId AS PersistenceKey, wl.SomeOtherColumn AS SomeOtherProperty, ... ..." // ... 

Answer 2

I found another pretty clean solution. In my model, I have public properties with good names that I want to use, and private properties with the same name as in the database, and return a private value in the public recipient, for example:

 public class KeywordsStatistic { public string Keyword { get { return lib_motcle; } } public int NumActions { get { return nbrActions; } } private string lib_motcle { get; set; } private int nbrActions { get; set; } } 

Of course, this will need to be changed if the values ​​need to be updated, but the principles are the same ...

NTN