FromSql always require an ID key column to be returned when fetching data from stored procedure

[k-krupka]

When running FromSql to get results from stored procedure, ID (key) of some sort is always required. This is not always true. Simple use case below to proove.

System.InvalidOperationException : The entity type 'GetValuesWithoutIdsResult' requires a primary key to be defined.
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.ValidateNonNullPrimaryKeys(IModel model)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.Validate(IModel model)
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.Validate(IModel model)
   at Microsoft.EntityFrameworkCore.Internal.SqlServerModelValidator.Validate(IModel model)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder, IModelValidator validator)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.<>c__DisplayClass5_0.<GetModel>b__0(Object k)
   at System.Collections.Concurrent.ConcurrentDictionary`2.GetOrAdd(TKey key, Func`2 valueFactory)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, IConventionSetBuilder conventionSetBuilder, IModelValidator validator)
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel()
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
   at Microsoft.EntityFrameworkCore.Infrastructure.EntityFrameworkServicesBuilder.<>c.<TryAddCoreServices>b__7_1(IServiceProvider p)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitFactory(FactoryCallSite factoryCallSite, ServiceProvider provider)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProvider provider)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProvider provider)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProvider provider)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(IServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceProvider.<>c__DisplayClass22_0.<RealizeService>b__0(ServiceProvider provider)
   at Microsoft.Extensions.DependencyInjection.ServiceProvider.GetService(Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
   at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
   at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.get_Model()
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.get_EntityType()
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.get_EntityQueryable()
   at Microsoft.EntityFrameworkCore.Internal.InternalDbSet`1.System.Linq.IQueryable.get_Provider()
   at Microsoft.EntityFrameworkCore.RelationalQueryableExtensions.FromSql[TEntity](IQueryable`1 source, RawSqlString sql, Object[] parameters)
   at IntegrationTests.StoredProcedureTests.test1_withIds() in D:\ (...) IntegrationTests\test.cs:line 52

Steps to reproduce

I am pasting a sample representation of table in DB. Two cases of C# code, one with problem, one without.

CREATE TABLE [dbo].[persons](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nchar](10) NOT NULL,
	[surname] [nchar](10) NOT NULL
) 
INSERT [dbo].[persons] ([name], [surname]) VALUES (N'aaa       ', N'bbb       ')
INSERT [dbo].[persons] ([name], [surname]) VALUES (N'ccc       ', N'ddd       ')

CREATE PROCEDURE [dbo].[GetValuesWithIds]
AS BEGIN
	SELECT id, name, surname FROM persons
END

CREATE PROCEDURE [dbo].[GetValuesWithoutIds]
AS BEGIN
	SELECT name, surname FROM persons
END

The below will work just fine:

public partial class TestDbContext : DbContext
{
	public class GetValuesWithIdsResult
	{
		public int Id { get; set; }
		public string Name { get; set; }
		public string SurName { get; set; }
	}

	public virtual DbSet<GetValuesWithIdsResult> ValuesWithIds { get; set; }

	public TestDbContext(DbContextOptions<TestDbContext> options)
		: base(options)
	{
	}
}

[Fact]
public void test1_withIds()
{
	List<TestDbContext.GetValuesWithIdsResult> result = DbContext.ValuesWithIds.FromSql("GetValuesWithIds").AsNoTracking().ToList();

	result.Count.Should().Be(2);
}

The below will throw error mentioned above.

public partial class TestDbContext : DbContext
{
	public class GetValuesWithoutIdsResult
	{
		public string Name { get; set; }
		public string SurName { get; set; }
	}

	public virtual DbSet<GetValuesWithoutIdsResult> ValuesWithoutIds { get; set; }

	public TestDbContext(DbContextOptions<TestDbContext> options)
		: base(options)
	{
	}
}

[Fact]
public void test2_withoutIds()
{
	List<TestDbContext.GetValuesWithoutIdsResult> result = DbContext.ValuesWithoutIds.FromSql("GetValuesWithoutIds").AsNoTracking().ToList();

	result.Count.Should().Be(2);
}

Further technical details

EF Core version: 2.0.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 Enterprise 64bit
IDE: Visual Studio 2017 Professional

[ajcvickers]

Duplicate of #9290

[Yawarmurtaza]

AsNoTracking() fixed this exact same issue for me. Thank you @xagon