How to map Table Valued Functions in EF6 Code First with different column names?

c# entity-framework entity-framework-6 sql-server user-defined-functions

Question

It is possible to map a table valued functions in EF6 without edmx? The problem is that the columns in the database are named differently than in the application.

My application as a simplified example.

Database

The table user on SQL Server 2016:

User
Id   (int, Identity, PK)
Name (nvarchar(255)

The table valued function GetUser(id) on SQL Server 2016:

CREATE FUNCTION [dbo].[GetUser](@ID int)
RETURNS TABLE
AS
RETURN 
(
    SELECT *
    FROM dbo.User
    WHERE Id = @ID
);

Application

I use the following nuget package:

Install-Package EntityFramework.CodeFirstStoreFunctions

The related object in C#:

public class User
{
    [Key]
    public int Id { get; set; }

    [Column("Name")]
    public string UserName { get; set; }
}

The database set in my DbContext class:

public DbSet<User> Users { get; set; } 

My user configuration with column mapping:

private void ConfigureUser(EntityTypeConfiguration<User> configuration)
{
    configuration.ToTable("User");

    configuration.HasKey(user => user.Id);
    configuration.Property(user => user.Id).HasColumnName("Id").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    configuration.Property(user => user.UserName).HasColumnName("Name");
}

My stored procedure (TVF):

[DbFunction(nameof(ApplicationDb), "GetUser")]
    public virtual IQueryable<User> GetUser(Nullable<int> id)
    {   
        var idParameter = id.HasValue ?
        new ObjectParameter("ID", id) :
        new ObjectParameter("ID", typeof(int));
        return ((IObjectContextAdapter)this).ObjectContext.CreateQuery<User>("[GetUser](@ID)", idParameter);
    }

The OnModelCreating method:

protected override void OnModelCreating(DbModelBuilder modelBuilder) 
{
    if (modelBuilder == null)
    {
        throw new ArgumentNullException(nameof(modelBuilder));
    }

    base.OnModelCreating(modelBuilder);

    modelBuilder.Conventions.Add(new CodeFirstStoreFunctions.FunctionsConvention<MyDbContext>("dbo"));

    this.ConfigureUser(modelBuilder.Entity<User>());

    // ...
}

Problem

My problem is the following line:

return ((IObjectContextAdapter)this).ObjectContext.CreateQuery<User>("[GetUser](@ID)", idParameter);

The table valued function expects a type with the properties like the table column. The correct spelling is necessary, so I need a mapping. It is an external database, so I can not change the columns in SQL Server. The project does not allow to use the edmx model.

I do not want to create the following type:

public class GetUserResult
{
    public int Id { get; set; }

    public string Name { get; set; }
}

Exception: Invalid column name 'UserName'.

StackTrace:

   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   System.Data.SqlClient.SqlDataReader.get_MetaData()
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
   System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

How is it possible?

A centrales manual mapping between table column name and property name of entities would not be a problem. Thanks

1
5
1/23/2017 12:54:51 PM

Popular Answer

You do not want to modify your object and you cannot modify the database, but you can still modify your query. Try naming the columns in the result of the table valued function:

return ((IObjectContextAdapter)this).ObjectContext.CreateQuery<User>(
    "SELECT Id, Name AS UserName FROM [GetUser](@ID)", idParameter
);
0
3/19/2020 3:01:38 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow