In MVC Model, how do I lookup a field in a different table?

Question

So I have a model:

[Table("Site")]
public class Store : MPropertyAsStringSettable {
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }
    public string Name { get; set; }
    public int CompanyID { get; set; }
    public bool IsActive { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    [Display(Name = "Province")]
    public int ProvinceID { get; set; }
    public string Postal { get; set; }
    public string Phone { get; set; }
    public int StoreNumber { get; set; }
    public bool visible { get; set; }
    public DateTime lastShift { get; set; }
}

The field lastShift is from a different table called "Shifts", how do I get it from that table?

EDIT: The lookup will have to be something like this:

  select top 1 shiftDate as lastShift from [Shifts] where SiteID = Store.ID

This is how I load my data:

public class MyDbContext: DbContext {
    public MyDbContext()
        : base("name=DefaultConnection") {
    }
    public DbSet<UserAccount> UserAccounts { get; set; }
    public DbSet<Company> Companies { get; set; }
    public DbSet<Store> Stores { get; set; }
    public DbSet<ProvinceModel> Provinces { get; set; }
}

And this is how I use it:

MyDbContext database = new MyDbContext();
var myStores = from database.Stores select ID;

FYI - this has nothing to do with MVC, this is Entity Framework. — jrummell, Apr 10 '13 at 15:23
What about this problem? Could you have a look at please? Thanks in advance... http://stackoverflow.com/questions/29333787/how-to-create-lookup-table-and-define-relationships — Jack, Mar 29 '15 at 19:38

pollirrata · Answer 1 · 2013-04-10T15:00:24.723

1

EDIT according to your last edit, this is not the case

~~It will need to be a "navigation property" which means that you'll need to have an explicit (Foreing Key) relationship between Site and Ship~~

Then you'll have something like this

Store.Shift.LastShift

But if it is a one to many relationship (and LastShift field is not part of Shift table) then

you'll need to do it manually, and use a view model or a custom property that it is not mapped directly to the table and do the assignment somewhere in your code

If you're using a Repository, then you'll need to add a method there to get the last shift

Then (or if you are using ORM directly) you use the code that @Cristi posted, just remember to add the sorting

public ActionResult MyAction(){
var store = db.Stores.Where(x => x.ID == objId).Select(x => new StoreModel(){
    Name = x.Name,
    ID = x.ID,
    lastShift = db.Shifts.FirstOrDefault(y => y.SiteID == x.ID).OrderByDescending(shift => shift.Date);
}).FirstOrDefault();

return View(store);
}

edited Apr 10 '13 at 15:00

answered Apr 10 '13 at 14:38

pollirrata

5,188
2
32
50

Can you provide an example of custom property? – Bill Software Engineer Apr 10 '13 at 14:43
It is the same that you have already `public DateTime lastShift { get; set; }` but you need to do the assignment manually. There is no way that MVC can know that `LastShift` means `select top 1 shiftDate as lastShift from [Shifts] where SiteID = Store.ID` – pollirrata Apr 10 '13 at 14:45
Can you provide some code of how I would be able to run that query either in linq or sql to get that field? – Bill Software Engineer Apr 10 '13 at 14:47
Not sure what you mean. I am using database context, which connect to a MS SQL database. – Bill Software Engineer Apr 10 '13 at 14:56

score 1 · Accepted Answer · answered Apr 10 '13 at 16:09

Here is how I solved the problem.

TL,DR: I shared my dbcontext in my controller so I have access to it in my models!

I manually load the lastShiftTime in my Store Model with the following code:

public class Store : MPropertyAsStringSettable {
    .......
    public DateTime lastShiftTime { 
        get{
            MyDbContext curContext = MyWebProject.Controllers.MyBaseController.database;
            if (curContext != null) {
                return (from shift in curContext.Shifts where shift.SiteID == ID select shift.ShiftDate).First();
            } else {
                return new DateTime(0);
            }
        }
    }
}

Here is my Shift Model I created for this, very standard:

[Table("Shifts")]
public class Shift : MPropertyAsStringSettable {
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int ID { get; set; }
    public int SiteID { get; set; }
    public string ShiftID_In_POS { get; set; }
    public DateTime ShiftDate { get; set; }
}

And here is how I am sharing the context in controller:

public class MyBaseController : Controller {
    .........
    private static MyDbContext _database;
    public static MyDbContext database {
        get {
            if (_database == null) {
                _database = new MyDbContext();
            }
            return _database;
        }
    }
}

In MVC Model, how do I lookup a field in a different table?

2 Answers2