CODEWITHSUNDEEP
c#sql-serverentity-framework
usr4896260
usr4896260

Reputation: 1507

Error no mapping column in Entity 6 DB first design

I have a legacy mvc application which I cannot rewrite. There's a stored procedure which all the returns column names of table A except for ID. (Note, it actually does not select from table A, but just happens to return the same column names). I wish to call the stored procedure (via SQL query), and assign the results to a variable of class A. However, I get a System.Data.Entity.Core.CommandExecutionException saying A member of the type 'ID' does not have a corresponding column in the data reader with the same name.

My code is as follows:

Class A

namespace LegacyApp.Models
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations.Schema;

    public partial class A
    {
        [NotMapped]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ID { get; set; }
        public Nullable<int> SOId { get; set; }
        public string SODesc { get; set; }
        //... 
    }
}

My Controller

public class MyController : Controller
{
    private MyDBContext db = new MyDBContext();
    //...

    [HttpPost]
    [ValidateInput(false)]
    [OutputCache(NoStore = true, Duration = 0)]
    public ActionResult Index(ViewModel rb)
    {
        var query = this.buildQuery().ToString();

        // The results returned by the query contain all members of A except for ID
        IEnumerable<A> goals = db.Database.SqlQuery<A>(query);

        // Exception occurs here
        List<A> goalList = goals.ToList();
        rb.goalList;
        return View(rb);
    }
}

Properties of ID in .edmx designer

enter image description here

I was under the assumption that the [NotMapped] or [DatabaseGenerated(DatabaseGeneratedOption.Identity)] annotation should ignore the ID? The reason I'm doing this is because the user would be filtering the queried dataset, in which the filtered results would then be inserted into table A. As table A using has a PK ID which is auto incremented, I would like the IDs to be NULL upon insertion. I've updated my Model from the entity designer but I still get the error. What is the proper way to do this?

Upvotes: 1

Views: 598

Answers (2)

usr4896260
usr4896260

Reputation: 1507

With the help of Lanorkin and this post, I was able to come up with a solution that worked. I created base class which contains the subset of the fields called by the stored procedure. The class generated by Entity then extends the base class adding ID. To aid with adding to the database, I created a helper constructor to initialize all the members of the base class with the Entity. My code is as follows:

namespace LegacyApp.Models
{
    using System;
    using System.Collections.Generic;

    // My base class
    public partial class A
    {
        public Nullable<int> SOId { get; set; }
        public string SODesc { get; set; }
        //... 
    }
}

My class generated by Entity

namespace LegacyApp.Models
{
    using System;
    using System.Collections.Generic;

    public partial class B : A
    {
        /// <summary>
        /// Used to assign all members of base class A to B
        /// </summary>
        /// <param name="baseClass">the base class</param>
        public B(A baseClass) : base()
        {
            foreach (var prop in typeof(A).GetProperties())
            {
                this.GetType().GetProperty(prop.Name).SetValue(this, prop.GetValue(baseClass, null), null);
            }
        }
        public int? ID { get; set; }
    }
}

My Controller

public class MyController : Controller
{
    private MyDBContext db = new MyDBContext();
    //...

    [HttpPost]
    [ValidateInput(false)]
    [OutputCache(NoStore = true, Duration = 0)]
    public ActionResult Index(ViewModel rb)
    {
        var query = this.buildQuery().ToString();

        // The results returned by the query contain all members of B except for ID
        IEnumerable<A> goals = db.Database.SqlQuery<A>(query);

        IList<B> goalsToInsert = new List<B>();
        // Create a new List of Objects of type B. 
        // Constructor allows for all members of A to be assigned to B 
        goals.ToList().ForEach(x => {goalsToInsert.Add(new B(x));});
        db.B.AddRange(goalsToInsert);
        db.SaveChanges();

        rb.goalList = goalsToInsert;
        return View(rb);
    }
}

Upvotes: 0

Lanorkin
Lanorkin

Reputation: 7504

When you read entity from database, you can use projection - a class which contains just the fields you select.

When you insert / update entity, this entity should be mapped in model, always have key etc.

Technically projections can be any classes and not required to be included in model.

I see following choices for your case:

  1. Create absolutely separate Dto class and use it for SELECTing using stored procedure you have
  2. If stored procedure selects subset of original fields and do not introduces new fields - you can organize classes hierarchy like Entity : Dto, and use Dto for SELECTing, and "whole" Entity class for updating
  3. If fields mostly match, but there are differences - you can introduce Base class with common fields, and then create Entity : Base and Dto : Base for appropriate cases.

Upvotes: 1

Related Questions