CODEWITHSUNDEEP
c#entity-framework
Bill Software Engineer
Bill Software Engineer

Reputation: 7782

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

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;

Upvotes: 2

Views: 5134

Answers (2)

Bill Software Engineer
Bill Software Engineer

Reputation: 7782

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;
        }
    }
}

Upvotes: 1

pollirrata
pollirrata

Reputation: 5286

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);
}

Upvotes: 1

Related Questions