Efcore: Generic FromSQL() method does not work when we configure a column to map with a different property name

[swastiks]

Message = "The required column 'BlogId' was not present in the results of a 'FromSql' operation."

I have a generic method ExecuteStoreQuery method which looks like following:

```C#
public override IEnumerable ExecuteStoreQuery(String commandText, params object [] parameters)
{
return _context.Set().FromSql(query, parameters);

}

The model looks like following:
```C#
public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}

and the mappings look like following:

```C#
modelBuilder.Entity().ToTable("Blog");
modelBuilder.Entity().Property(t => t.blog_id).HasColumnName("BlogId");
modelBuilder.Entity().Property(t => t.Url ).HasColumnName("Url");

and whenever I call the ExecuteStoreQuery Method, I get a exception.

                    queryResult = repository.ExecuteStoreQuery<Blog>("SELECT blogid as blog_id, url from blog", parameters).ToList();

Exception message:
The required column 'BlogId' was not present in the results of a 'FromSql' operation.
at Microsoft.EntityFrameworkCore.Query.Sql.Internal.FromSqlNonComposedQuerySqlGenerator.CreateValueBufferFactory(IRelationalValueBufferFactoryFactory relationalValueBufferFactoryFactory, DbDataReader dataReader)
at Microsoft.EntityFrameworkCore.Query.Internal.ShaperCommandContext.b__11_0(FactoryAndReader s)
at Microsoft.EntityFrameworkCore.Internal.NonCapturingLazyInitializer.EnsureInitialized[TParam,TValue](TValue& target, TParam param, Func2 valueFactory) at Microsoft.EntityFrameworkCore.Query.Internal.ShaperCommandContext.NotifyReaderCreated(DbDataReader dataReader) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.BufferlessMoveNext(Boolean buffer)
at Microsoft.EntityFrameworkCore.Storage.Internal.SqlServerExecutionStrategy.ExecuteTState,TResult
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.MoveNext() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__172.MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.MoveNext() at System.Collections.Generic.List1.AddEnumerable(IEnumerable1 enumerable) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)
```

Further technical details

EF Core version: 2
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 7
IDE: Visual Studio 2017

Please let me know how if there is work around of this issue.

The EF6 function however works
((System.Data.Entity.Infrastructure.IObjectContextAdapter)_context).ObjectContext.ExecuteStoreQuery(query, parameters);

[ralmsdeveloper]

you have created an alias blog_id where your HasColumnName("BlogId") expects BlogId

[ajcvickers]

@swastiks We're having some difficulty putting all the snippets above together into something coherent that would allow us to reproduce what you are seeing. Can you create a full code listing or project that demonstrates the issue?

[swastiks]

@ralmsdeveloper I have corrected the exception message. @ajcvickers I will try to upload a project itself.

[ralmsdeveloper]

@swastiks, Everything was just to make sure the course had not been named incorrect!

This should, in fact, be seen by the changes in the name of the property!

I believe that @ajcvickers will have a more adequate response!

[smitpatel]

```C#
modelBuilder.Entity().Property(t => t.blog_id).HasColumnName("BlogId");
modelBuilder.Entity().Property(t => t.Url ).HasColumnName("Url");

By using above code you are saying that `Blog` has column `BlogId` & `Url` in database.
`queryResult = repository.ExecuteFunction<Blog>("SELECT blogid as blog_id, url from blog", parameters).ToList()`
But in your FromSql you are selecting columns named "blog_id", "url".
Essentially your FromSql query does not have the columns you said you entity has hence it is throwing exception that column is not found.

Furthermore,
Given Blog definition to be 
```C#
public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}

I am not certain how following code would compile
C# modelBuilder.Entity<Blog>().ToTable("Blog"); modelBuilder.Entity<Blog>().Property(t => t.blog_id).HasColumnName("BlogId"); modelBuilder.Entity<Blog>().Property(t => t.Url ).HasColumnName("Url");

If your CLR property is named BlogId and you want to name it as "blog_id" in database then the way to configure it is
modelBuilder.Entity<Blog>().Property(t => t.BlogId).HasColumnName("blog_id");

Make sure, you are mapping your entity to correct database column names & those column names are appearing in your FromSql query string. Then everything will work as expected.
If you still face issues feel free to reopen issue.

[swastiks]

Thank you @smitpatel , @ralmsdeveloper and @ajcvickers for taking time to look at my issue. I have attached my solution along with this comment.

Background: Blog is a .net core api wheras BlogEF6 is a .net framework api. E6 is a .net framework class library, which uses Entity Framework 6 to access the data. EFcore2 is a .net core 2 library which uses Entity Framework Core 2 to access the data. EFcore 2 poject uses (IObjectContextAdapter)_context).ObjectContext.ExecuteStoreQuery(commandText,parameters)[Repository.cs in E6 project] function to run the query whereas E6 project uses _context.Set().FromSql(commandText,parameters)[Repository.cs in EFcore2 project] function to run the query. Both the project E6 and EFCore2 uses the same mapping in the file MyContextEF6.cs and MyContextEFCore.cs respectively.

Steps to reproduce

1. Create the database, tables and insert the data using the Sql.sql file in a local database.
2. Update the connection string in the files MyContextEFCore.cs(E6 project) file and MyContextEF6.cs(EFCore2 project) file.
3. First set the BlogEf6 as a startup project. Run it. and hit the url localhost:port/api/blogs . The the api request succeeds without any issues.
4. Then set the Blog as a startup project. Run it. hit the url localhost:port/api/blogs . The the api request fails. It gives the issue "The required column 'BlogId' was not present in the results of a 'FromSql' operation"

My Question: Could you please let me know why step 3 is succeeding whereas step 4 is failing? Is my mapping wrong? Is there any reason FromSQL function in EF Core 2 is different from ExecuteStoreQuery in EF6?

Please let me know is you have any questions regarding my clarification.
TestWebApp.zip

[swastiks]

@smitpatel Can you please reopen this issue? I am unable to do so.

[smitpatel]

@swastiks - already did. We will investigate the project you shared and let you know findings. Thanks.

[ralmsdeveloper]

Hello @swastiks

I have identified what can be!

In the file: BlogsController.cs

var resultWithEFCore  = efCoreRepo.ExecuteStoreQuery<Models.Blog>
    ("SELECT blogid, url as Url1 from blog", parameters).ToList();

You are crafting an alias from the "url" column to "Url1",
and in your context you are mapping the "url1" property to "Url",
so the EF Core waits for URL and not URL1.

So you ask why does EF6 work?

I'll explain it to you.

In the OnModelCreating file: "MyContextEFCore" you access the mapping twice, lines 18 and 26.

In "EF Core" you replace the mapping as many times as you like.

In EF6, you do the same, but line 27 of MyContextEF6 is discarded because of the accessibility of the property on line 19.

If you remove line 19 from MyContextEF6, you will get the same error!

So the correct one is:

SELECT blogid, url from blog

rather than:

SELECT blogid, url as Url1 from blog

Sorry if you don't understand, I'm trying to improve my English.

c/ @smitpatel @ajcvickers

[swastiks]

Hello @ralmsdeveloper ,

Thank you for taking time to reply to my comment. I really appreciate your comment. But I have some more questions on it.

1. Are you inferring EF Core mappings work differently than EF6?

2. You mentioned "line 27 of MyContextEF6 is discarded because of the accessibility of the property on line 19.". Are you inferring that the property mapping in line 19 overrides the property on line 27 and this does not take place in EFCore's property mapping?

Also, I have to use alias in the query because in my practical case the table name and column name is same in the database whereas class name and property name cannot be same in C#. So, I have to rename property different from class name. I actually desired the property mapping to work the same in EF6 and EFCore.

Please let me know if you have any questions.

Thank you!

[ralmsdeveloper]

1. Are you inferring EF Core mappings work differently than EF6?
   _Yes, actually, it's another structure, but that's not the case here.
   The EF6 was not prepared for that._

Note that EF6 simply discards the column mapping below!

modelBuilder.Entity<Blog>().Property(t => t.Url1).HasColumnName("Url");

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{ 
    modelBuilder.Entity<Blog>().Property(t => t.Url1)
        .IsRequired()
        .HasColumnType("varchar(max)");

    modelBuilder.Entity<Blog>().ToTable("Blog");
    modelBuilder.Entity<Blog>().HasKey(t => t.BlogId);

    modelBuilder.Entity<Blog>().Property(t => t.BlogId).HasColumnName("BlogId");


>>>> modelBuilder.Entity<Blog>().Property(t => t.Url1).HasColumnName("Url");

}

1. You mentioned "line 27 of MyContextEF6 is discarded because of the accessibility of the property on line 19.". Are you inferring that the property mapping in line 19 overrides the property on line 27 and this does not take place in EFCore's property mapping?
   _I said it mistakenly in a quick analysis. looking now I understood better.
   But the fact that the EF Core handle mapping is a bit different._

The fact is that EFCore waits for Url and not Url1 as it was informed in the query, as mapped in its context.
The only certainty I have is that you need to change your query, your property can have any name, when you use the HasColumnName, it will do the mapping for you.

In relation to EF6, it simply ignores the mapping of the column name.

Example:

var resultWithEFCore = efCoreRepo.ExecuteStoreQuery <Models.Blog> (" SELECT blogid, url as url1 from blog ", parameters) .ToList ();

Using this:

modelBuilder.Entity<Blog>().Property(t => t.Url1).HasColumnName("Url");

The EFCore is waiting for URL and Not URL1 as already quoted!

@smitpatel and @ajcvickers I actually tested with EF6 and it works, the point is that EF6 does not recognize the mapping of the "Url" column, so it works.

Already the EFCore as @smitpatel quoted above, the exception is thrown by the fact of mepear "Url" where in your query and created the alias "Url1".

I see @swastiks need to review these concepts and make the necessary changes.

SELECT blogid, url as Url1 from blog

to:

SELECT blogid, url from blog

[swastiks]

Hi @ralmsdeveloper , I agree with you. The EF 6 ignores the HasColumnName. I tested this on my end as well. I will be closing this issue. But, Before that I have one more question. But, why does the EF6 ignores the mappings of the column name? I am sorry but I am trying to improve my understanding here. Thank you!

[ralmsdeveloper]

Hi @swastiks,
Sorry, I do not know why. Nor can I simply say that it is a failure. My answers are limited to EF Core.
If you have any questions regarding EF Core we can clarify.

More certainty, someone on the team will give you a more adequate answer.

c/ @ajcvickers @smitpatel

[swastiks]

I am closing this issue.

[ajcvickers]

@swastiks There is some background on EF6 SqlQuery column mapping in this issue: http://entityframework.codeplex.com/workitem/233 Might help explain some of what you are seeing on EF6.