ASP.NET MVC model that uses more than one db table

Question

I am new to using MVC and I am trying to create a model that uses other models.

Let's say I have two tables in my database, I just call them in table1 and table2 for the sake of this question. In my MVC project, they correspond to Model1 and Model2. Now I would like to have a model 3 that I like:

public class Model3 { public Model1 model1 { get; set; } public Model2 model2 { get; set; } public string someString { get; set; } } 

What I want to create with a T-SQL query that looks like this:

 SELECT * FROM Table1 t1 LEFT JOIN Table2 t2 ON t1.fk = t2.pk 

Now, to run this query, I use the method described at http://www.asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/advanced-entity-framework-scenarios-for-an -mvc-web-application

Relevant Code:

 public virtual IEnumerable<TEntity> GetWithRawSql(string query, params object[] parameters) { return dbSet.SqlQuery(query, parameters).ToList(); } 

where I pass the above request.

The problem (finally!) Is that the Entity Framework wants a key. This is not a problem, but in this case the key is Table1.table1KeyPart and Table2.table2KeyPart, corresponding to Model1.model1Keypart and Model2.model2keypart.

So, I tried using the second technique here: in the entity structure code, how to use KeyAttribute for multiple columns

That is the one that HasKey uses, with u.Model1.model1KeyPart , u.Model2.model2KeyPart instead of userID and userName. Like this:

 protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<Model3>().HasKey(u => new { u.Model1.model1KeyPart, u.Model2.model2keyPart }); } 

I get an error message:

Property expression '[u => new <> f__AnonymousType1`2 (model1KeyPart = u.Model1.model1KeyPart, model2KeyPart = u.Model2.model2KeyPart)]' is invalid. The expression should represent the property: C #: 't => t.MyProperty' VB.Net: 'Function (t) t.MyProperty'. When specifying several properties, they use an anonymous type: C #: 't => new {t.MyProperty1, t.MyProperty2}' VB.Net: 'Function (t) New From {t.MyProperty1, t.MyProperty2}'.

Is this something fixable? Or am I doing this completely wrong? I suppose I could just list all the members of Model1 and Model2 in Model3, but this represents redundancy that I really don't want, but if this is the only way, then I will do it.

Sorry, if any of this is unclear, I will try to figure it out if necessary. Thanks in advance for your help!

Answer 1

I think you need to define a composite key, if you use Entity Framework code, you can do it first in dbContext.cs in an OnModelCreating method like this

 modelBuilder.Entity<Model3>().HasKey(s => new { s.Model1Key, s.Model2Key });