CODEWITHSUNDEEP
c#asp.net-mvc-3
user1102621
user1102621

Reputation: 21

MVC 3, C# - invalid column name generated by code

I am new to asp.net C# and trying to learn by building a simple web app based on MVC 3 Music application. So far i have had a decent run but i am running into this this and i am not able to figure out the root cause. plz help

I am building a simple website where Projects are listed, then clicking on projects you see all the tables and then clicking on table you see all the columns. Projects/Tables/Column are being fetched from SQL db which has valid data and PK/FK keys defined. i am able to navigate from projects to tables and can see all columns under tables but when I click on column link, i get error as described below.

ERROR: "Invalid column name 'Tables_Id'." SQL profiler shows this column in the query but i do not understand where is it coming from as I do not have any such columm.

CONTROLLER CLASS

public class ProjectController : Controller  // Inherit from base class Controller
{
    DbEntities storeDB = new DbEntities();   //Create Object/instance of class //StorDB is  reference to an object

    public ActionResult Index()
    {
        var Name = storeDB.ProjectNM.ToList(); //Use 'var' coz we may have any type returned, 'var' is determined at run time
        return View(Name); 
    }

    public ActionResult BrowseTables(string Projects) 
    {
        var ProjectModel = storeDB.ProjectNM.Include ("Tabless")
        .Single(g => g.Name == Projects);

        return View(ProjectModel);
    }

    public ActionResult BrowseColumns(string TableIs)
    {
        var ProjectModel1 = storeDB.TableNM.Include("Columnss")
        .Single(g => g.Tbl_Name == TableIs);

        return View(ProjectModel1);

        //var ColumnModel = storeDB.TableNM.Find(TableIs);
       // return View(ColumnModel);

    }
}

Other Classes

public partial class Projects //Partial class, see comment below
{
    public int Id { get; set; }
    public string Name { get; set; }

    public List<Tables> Tabless { get; set; }  //Navigation Property, required so that we can include tables under projects

}

public class Tables
{
    public int Id { get; set; }
    public int ProjectId { get; set; }
    public string Tbl_Name { get; set; }
    public Projects Project { get; set; }   //Class table can have (belong) only one project

    public List<Columns> Columnss { get; set; } //Table can have more than one column
  }

 public class Columns
{
    public int Id { get; set; }
    [ForeignKey("Tables")] 
    public int TblId { get; set; }
    public string Column_Name { get; set; }
    public string IncludeFlag { get; set; }
  }

View 

<ul> 
@foreach (var Tables in Model.Tabless) 
{ 
    <li> 
      @Html.ActionLink(Tables.Tbl_Name, "BrowseColumns", new { TableIs = Tables.Tbl_Name }) 
    </li> 
}

Query from SQL profiler

[Project2].[Tables_Id] AS [Tables_Id]

As you can seethe query has a column [Tables_Id] and I do nto understand why it is there as i do nto have any such column. Please help!

Upvotes: 1

Views: 4362

Answers (2)

CodingWithSpike
CodingWithSpike

Reputation: 43698

The convention for EF4.1 and foreign keys is to name them {TableName}_{ColumnName} so your foreign key to Tables is expecting a column names Table_Id (because Table is the name of the table, and Id is the name of the PK column.

This might help: EF 4.1 messing things up. Has FK naming strategy changed?

Upvotes: 2

one.beat.consumer
one.beat.consumer

Reputation: 9504

Basically MVC3 and EF4 do a lot of things on convention.

My suggestion to make things a little clearer for yourself is read up on EF 4.1 a little, and let it pluralize your table names for you, and use the data annotations (or property mapping if you don't like the attributes in your model) to mark your object's Id properties...

This is not necessarily the cause of your problem, but I think you will find it a lot easier to see what is going on in your profiler and models when the names/values make more logical sense.

Start by singularizing your objects: Table, Column, etc. or even using a more descriptive name... again if for no other reason it will be easier for you to read and debug, or even to get better answers here.

Upvotes: 2

Related Questions