Database in Oracle - View:

     COLUMN_NAME | DATE_TYPE
___________________________________________________
 UMOWA           | VARCHAR2(30)
 RODZ_LEAS       | VARCHAR2(1)
  KTH            | NUMBER(10)
  SKRÓT          | VARCHAR2(50)
 NAZWA           | VARCHAR2(240)
 AMORTYZ         | NUMBER(10,6)
 WALUTA          | VARCHAR2(3)
WARTOSC_UMW      | NUMBER
  LICZBA_RAT     | NUMBER
WK               | NUMBER
 WK%             | NUMBER
Rodzaj stopy baz.|  VARCHAR2(2000)
Wart.stopy baz.  | NUMBER
REFI             | NUMBER
RA_NR_RATY       | NUMBER(10)
RA_TYP           | VARCHAR2(10)
RA_DATA_SPLATY   | DATE
RA_KWOTA_NETTO   | NUMBER(12,2)
RA_KAPITAL_POZOSTALY   | NUMBER(12,2)

Procedure in SQL Server - it works - I get the data from Oracle.

CREATE PROCEDURE [egeria].[ContractData_P] (@ContractNumberPar varchar(20))
AS
    EXECUTE(
        'Select 
            Umowa ContractNumber
            , Rodz_leas TypeOfLeasing
            , Kth CodeClient
            , skrót Short
            , Nazwa NameOfClient
            , amortyz Amortization
            , Waluta Currency
            , Wartosc_Umw ContractValue
            , Liczba_rat NumberOfInstallments
            , WK FinalValue
            , "WK%" FinalValuePercent
            , "Rodzaj stopy baz." TypeOfBaseRate
            , "Wart.stopy baz." ValueOfBaseRate
            , REFI
            , ra_nr_raty InstallmentNumber
            , ra_typ Type
            , ra_data_splaty MaturityDate
            , ra_kwota_netto NetAmount
            , ra_kapital_pozostaly CapitalRemaining
        from RAPADM.XLS_SKROCENIA_UMOW_V 
        where UMOWA  = ?'
        , @ContractNumberPar) AT EF_EG5PROD
GO

Model

public class ContractData
{
    public string ContractNumber { get; }
    public string TypeOfLeasing { get; }
    public int CodeClient { get; }
    public string Short { get; }
    public string NameOfClient { get; }
    public decimal Amortization { get; }
    public string Currency { get; }
    public decimal ContractValue { get; }
    public decimal NumberOfInstallments { get; }
    public decimal FinalValue { get; }
    public decimal FinalValuePercent { get; }
    public string TypeOfBaseRate { get; }
    public decimal ValueOfBaseRate { get; }
    public decimal REFI { get; }
    public decimal InstallmentNumber { get; }
    public string Type { get; }
    public DateTime MaturityDate { get; }
    public decimal NetAmount { get; }
    public decimal CapitalRemaining { get; }
}

Context

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) 
        : base(options)
    {
    }

    public virtual DbSet<ContractData> ContractData { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<ContractData>().HasNoKey();          
    }
}

ContractDataService -- HERE I HAVE ERROR

private readonly AppDbContext _context;

public ContractDataService(AppDbContext context)
{
    _context = context;
}

public async Task<IEnumerable<ContractData>> ExecAsync(string contractNumberPar)
{
    var data = _context.ContractData.FromSqlRaw("EXECUTE [egeria].[ContractData_P] @ContractNumberPar", contractNumberPar).AsEnumerable();
    return data;
}

Error:

system.InvalidOperationException: Sequence contains no elements at System.Linq.ThrowHelper.ThrowNoElementsException() at System.Linq.Enumerable.Max(IEnumerable`1 source) at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at System.Dynamic.Utils.ExpressionVisitorUtils.VisitBlockExpressions(ExpressionVisitor visitor, BlockExpression block) at System.Linq.Expressions.ExpressionVisitor.VisitBlock(BlockExpression node) at System.Linq.Expressions.BlockExpression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.VisitExtension(Expression extensionExpression) at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor) at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node) at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.ShaperProcessingExpressionVisitor.ProcessShaper(Expression shaperExpression, RelationalCommandCache& relationalCommandCache, LambdaExpression& relatedDataLoaders, Int32& collectionId) at

Does anyone have an idea how to do this? Should I go another way?

Please check if what I am doing has any sense, or is it not better to go a better way?

CodePudding user response：

EF Core cannot create projection expression because all properties of ContractNumber are readonly. Add setters to solve your issue:

public class ContractData
{
    public string ContractNumber { get; set; }
    public string TypeOfLeasing { get; set; }
    public int CodeClient { get; set; }
    public string Short { get; set; }
    public string NameOfClient { get; set; }
    public decimal Amortization { get; set; }
    public string Currency { get; set; }
    public decimal ContractValue { get; set; }
    public decimal NumberOfInstallments { get; set; }
    public decimal FinalValue { get; set; }
    public decimal FinalValuePercent { get; set; }
    public string TypeOfBaseRate { get; set; }
    public decimal ValueOfBaseRate { get; set; }
    public decimal REFI { get; set; }
    public decimal InstallmentNumber { get; set; }
    public string Type { get; set; }
    public DateTime MaturityDate { get; set; }
    public decimal NetAmount { get; set; }
    public decimal CapitalRemaining { get; set; }
}