Type 'sp' is mapped as a complex type. The Set method, DbSet objects, and DbEntityEntry objects can only be used with entity types, not complex types

Question

I have a stored procedure as:

public partial class StoredProcedureReport_Result
{
        public string Contract_QuoteNo { get; set; }
        public System.DateTime Contract_StartDate { get; set; }
        public System.DateTime Contract_EndDate { get; set; }
        public string Contract_AgencyName { get; set; }
        public int ContractService_Id { get; set; }
        public string Description { get; set; }
        public Nullable<System.DateTime> OrderStartDate { get; set; }
        public Nullable<System.DateTime> OrderEndDate { get; set; }
        public Nullable<int> OrderTermMonths { get; set; }
        public Nullable<decimal> MonthlyUnitPrice { get; set; }
        public Nullable<decimal> Quantity { get; set; }
        public Nullable<decimal> TotalPrice { get; set; }
        public System.Guid ContractId { get; set; }
}

I am using the repository pattern to execute the Entity Framework and here is the screen short of stored procedure result from SQL Server

Here is the code for the Repository to call the generic repo

public class ReportService : IReportService
{
        private readonly IGenericRepository<StoredProcedureReport_Result> _iGenericReportProcRepository;

        public ReportService(IGenericRepository<StoredProcedureReport_Result> iGenericReportProcRepository)
        {
            _iGenericReportProcRepository = iGenericReportProcRepository;
        }

        public List<DataExtractionViewModel> GetReportResultByFilter(ReportFilterViewModel filter)
        {
            List<DataExtractionViewModel> list = new List<DataExtractionViewModel>();
            var reportFilterDb =
                _iGenericReportProcRepository.ExecuteStoredProcedureFunction("StoredProcedureReport @QuotationNo, @AgencyName, @ContractStartDate, @ContractEndDate, @contractTerm",
                    new SqlParameter("QuotationNo", SqlDbType.VarChar) { Value = filter.QuotationNo },
                    new SqlParameter("AgencyName", SqlDbType.VarChar) { Value = filter.AgencyName },
                    new SqlParameter("ContractStartDate", SqlDbType.DateTime) { Value = filter.ContractStartDate },
                    new SqlParameter("ContractEndDate", SqlDbType.DateTime) { Value = filter.ContractEndDate },
                    new SqlParameter("contractTerm", SqlDbType.Int) { Value = filter.Term }
                    ).ToList();

            reportFilterDb.ForEach(item =>
            {
            });

            return list;
        }
}

Here is the generic code

public class GenericRepository<T> : IGenericRepository<T> where T : class 
{
        private readonly DATAEXTRACTION_DEVEntities _entities;

        public GenericRepository(DATAEXTRACTION_DEVEntities dbContext)
        {
            _entities = dbContext;
        }

        public virtual IEnumerable<T> ExecuteStoredProcedureFunction(string query, params object[] parameters)
        {
            return _entities.Set<T>().SqlQuery(query, parameters).ToList();
        }
}

I am getting an error

Type 'sp' is mapped as a complex type. The Set method, DbSet objects, and DbEntityEntry objects can only be used with entity types, not complex types

Is this is the best way to exec the stored procedure in generic repo I found the solution from Using Generic Repository and Stored Procedures

But is is not working for me.

I am **guessing** EF complains that you are calling `DbContext.Set` but the DbContext does not contain a `DbSet` for StoreProcReport_Result. Add `DbSet StoreProcReport { get; set; }` to the DbContext. — Georg Patscheider, Dec 20 '17 at 14:47

score 0 · Answer 1 · edited Aug 17 '19 at 08:16

0

You didn't followed the article your mentioned.

Your method

public virtual IEnumerable<T> ExecuteStoredProcedureFunction(string query, params object[] parameters)
{
    return _entities.Set<T>().SqlQuery(query, parameters).ToList();
}

should be rewritten as as:

return _entities.Database.SqlQuery<T>(query, parameters).ToList();

Having Set<T>() means that you are working on an entity class defined in your database context but it is not true for StoredProcedureReport_Result. That is what the error message said.

edited Aug 17 '19 at 08:16

marc_s

732,580
175
1,330
1,459

answered Dec 20 '17 at 14:58

Sergey L

1,402
1
9
11

I change as you have mention But I am getting a new error as "The parameterized query '(@QuotationNo varchar(8000),@AgencyName varchar(8000),@ContractS' expects the parameter '@QuotationNo', which was not supplied." can you please tell me – San Jaisy Dec 20 '17 at 15:08

Type 'sp' is mapped as a complex type. The Set method, DbSet objects, and DbEntityEntry objects can only be used with entity types, not complex types

1 Answers1