Error no mapping column in Entity 6 DB first design

Question

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

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?

I meant do return ID, but use Identity attribute to indicate that this is something which shoud be touched by DB. — Lanorkin
– Lanorkin, Commented Mar 22, 2017 at 11:23
1) you need to return ID from sql 2) you do NOT need [NotMapped] attribute 3) you DO need Identity attribute. Having all these, db will return you whole entity with ID (and entity framework needs key for each object), database will update ID with autoincrement, and EF will then fetch that ID. — Lanorkin
– Lanorkin, Commented Mar 22, 2017 at 11:37
Unfortunately, the sp doesn't return an ID (and I'm unable to modify it). That being said, is there an alternative approach? — usr4896260
– usr4896260, Commented Mar 22, 2017 at 11:40

Lanorkin · Accepted Answer · 2017-03-22 12:17:27Z

1

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:

Create absolutely separate Dto class and use it for SELECTing using stored procedure you have
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
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.

answered Mar 22, 2017 at 12:17

Lanorkin

7,5742 gold badges47 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:00:35Z

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);
    }
}

Collectives™ on Stack Overflow

Error no mapping column in Entity 6 DB first design

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related