CODEWITHSUNDEEP
c#sql-serverasp.net-mvcentity-framework
Michael G. Workman
Michael G. Workman

Reputation: 375

Issue with ASP.NET MVC 5 Web Application Entity Framework

I am working on MIT open source license example ASP.NET MVC web applications, and adding them as github public repos, I am also planning to have private github repos for my applications I plan to make money with in the future. I have a developer account with github.com.

I created a BOOKS MVC 5 web application using a TSQL script I was provided during a previous job interview some years ago, and am using GUID for the primary key ID fields with a default value of NEWID(), instead of an INT with IDENTITY, the solution is an ASP.NET MVC 5 web application with database first Entity Framework. I am using LocalDB for my SQL Server with this project, the script to create and populate the database is in my SQL-Server repo and is called BOOKS_Create.sql

I published the solution to my GitHub at the following URL:

https://github.com/ABION-Technology/Books

The TSQL scripts are available in the following repo:

https://github.com/ABION-Technology/SQL-Server

I added links the the shared layout view to show the index view for all Authors in the database, and also links to Index views for the TITLE and SALE EF models.

THe AUTHORS link works just fine, and lists all the authors in the database. But when I click the TITLES link, I get a SQL Exception of 'Author_ID' invalid column name, I did a search through my entire solution and did not find any variable named 'Author_ID' and did not find a C# class with a property called 'Author_ID". So this issue has me very confused there does not appear to be a good way to debug this issue. Thanks.

Upvotes: 0

Views: 159

Answers (2)

Steve Py
Steve Py

Reputation: 34653

EF will follow some default conventions to work out FK relationships. The error you are seeing is due to Author having a Titles collection and EF is attempting to automatically set up the 1-to-many between the two. It's expecting to find an "Author_ID" on the Title, which doesn't exist because your schema is set up with a joining table called TitleAuthor.

To resolve this, you will need to map the TitleAuthor entity, in which the Author will contain a collection of TitleAuthors which refer themselves to an Author and Title entity. EF can automatically map joining tables given those tables consist of just the two FKs. As soon as you want to introduce additional fields, then you need to define the joining entity.

public class TitleAuthor
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ID { get; internal set;}
    public virtual Title Title { get; internal set;}
    public virtual Author Author { get; internal set;}
    // add other properties as needed..
}

So from your Author entity:

public virtual ICollection<TitleAuthor> Authors {get; internal set;} = new List<TitleAuthor>();

To access the titles for the author:

author.Titles.Select(x => x.Title);

I would recommend reading up on many-to-many mapping with EF. I invariably use deliberate mapping with EF rather than relying on it's conventions. It just helps make it more predictable.

If you are using defaults for PKs then you need to tell EF via the DatabaseGenerated attribute. This isn't needed for read operations, but it will be needed when you go to insert records.

Also, with SQL Server, consider using NewSequentialId() as the default for your UUID PKs. These are more index-friendly than NewId().

The above example using internal (private works too) setters to promote DDD style use of entities. Public setters can lead to misuse/abuse of entities in the sense that the context will diligently attempt to save whatever you set. It's generally a good idea to restrict functionality that would alter an entity's state to a method in the entity with required arguments to be validated, or a repository. I use internal scoping to allow unit tests to still initialize entities. (leveraging InternalsVisibleTo between domain and unit test assemblies)

Upvotes: 1

cdev
cdev

Reputation: 5351

Reason is you are getting that Author ID error is, you have list of Titles in Author Class. Then there should be relationship between Author and Title entities, which is not exists in your data context. Comment public virtual ICollection<Title> Titles { get; set; } . And it should work.

Reason for you cant search this attribute is, its automatically generated by entity framework. (TableName_PrimaryKey)

If you want to keep this, create relationship in database using foreign keys and add that to your data context also. You may refer this

Upvotes: 0

Related Questions