CODEWITHSUNDEEP
c#asp.net-mvcmodel
tonyd24601
tonyd24601

Reputation: 205

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

I'm new to using MVC and I'm trying to make a model that uses other models.

Say I have in my database 2 tables, i'll just call them Table1 and Table2 for the sake of this question. In my MVC project these correspond to Model1 and Model2. Now I'd like to have Model 3 that's like:

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

Which 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'm using the technique described at http://www.asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/advanced-entity-framework-scenarios-for-an-mvc-web-application

The relevant code is:

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

where I feed the above query in.

The problem (finally!) is that Entity Framework wants a key. This wouldn't be 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 entity framework code first, how to use KeyAttribute on multiple columns

That is, the one using HasKey, 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 the error:

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

Is this something that's fixable? Or am I doing this the complete wrong way? I suppose I could just list out all the members of Model1 and Model2 in Model3, but that's introducing a redundancy that I really don't want, but if it's the only way, then I'll do it.

Sorry if any of that was unclear, I'll try to clear things up if need be. Thanks in advance for any help!

Upvotes: 4

Views: 556

Answers (1)

Asif Mushtaq
Asif Mushtaq

Reputation: 13150

I think you need to define the composite key, if you are using Entity Framework code first you can do this in dbContext.cs in OnModelCreating method like this

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

Upvotes: 4

Related Questions