How to get a list of objects from Entity Framework database?

Question

I am working on a Timesheet system. I'm using Entity Framework for the first time and am confused by how the relationships work.

I have the following class, Week:

public class Week
{
    public int WeekId { get; set; }
    public DateTime FirstDayOfWeek { get; set; }
    public List<TimeEntry> TimeEntries { get; set; }
    public Week()
    {
        TimeEntries = new List<TimeEntry>();
    }
}

and each week contains a number of TimeEntries:

public class TimeEntry
{
    public int TimeEntryID { get; set; }
    public double MonHours { get; set; }
    public double TueHours { get; set; }
    public double WedHours { get; set; }
    public double ThuHours { get; set; }
    public double FriHours { get; set; }
}

The way that EF has created the tables for these is as follows:

dbo.Weeks
WeekId (int)
FirstDayOfWeek (datetime)

dbo.TimeEntries
TimeEntryId (int)
MonHours (float)
TueHours (float)
WedHours (float)
ThuHours (float)
FriHours (float)
Week_WeekId (int)

In my tables I have the following:

WeekId---FirstDayOfWeek
1------------2017-07-31

TimeEntryId---MonHours---TueHours...-Week_WeekId
1-----------------1---------------2...-------------1
2-----------------0---------------1...-------------1

(Apologies for the badly drawn tables!)

The problem is when I try and retrieve the data from my controller using Linq, it doesn't pull back the list of TimeEntries.

This is the query I'm using:

Week SelectedWeek = (from w in db.Weeks  
  where w.WeekId == 1  
  select w).FirstOrDefault();

However, the count of SelectedWeek.TimeEntries is zero?

I also tried the opposite approach (which seemed more logical from a non-EF perspective):

var timeEntries = from te in db.TimeEntries
  where te.week_weekId == 1
  select te;

but this gives the error:

TimeEntry does not contain a definition for week_weekId...

What is the right way to do this?

Any reason why the question mentions MVC, and why it's tagged as such? If not, I suggest you remove that to make the question clearer. — Tipx
– Tipx, Commented Aug 2, 2017 at 20:43

Hasta Tamang · Accepted Answer · 2017-08-02 20:45:38Z

2

Week SelectedWeek = (from w in db.Weeks  
  where w.WeekId == 1  
  select w).FirstOrDefault();

Above query results in count of SelectedWeek.TimeEntries is zero because its not being loaded lazily or lazy loaded. You either need to add virtual keyword for TimeEntries or rely on eager loading

Lazy Loading
public virtual ICollection<TimeEntry> TimeEntries { get; set; }

Or

Eager Loading
Week SelectedWeek = db.Weeks.Include("TimeEntries").Where(w => w.WeekId == 1).FirstOrDefault();

Or 

Explicit Loading
var selectedWeek = db.Weeks.Find(1); 

// Load the TimeEntries related to a given Week
context.Entry(selectedWeek ).Collection(t => t.TimeEntries).Load();

Further reading about loading related entities in EF : https://msdn.microsoft.com/en-us/library/jj574232(v=vs.113).aspx

edited Aug 2, 2017 at 20:45

answered Aug 2, 2017 at 20:40

Hasta Tamang

2,2981 gold badge20 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jonathan M · Accepted Answer · 2017-08-02 20:35:53Z

1

You need to use the Include() clause when reading the data, in order to ask Entity Framework to perform a JOIN operation and retrieve the related records.

Week SelectedWeek = (from w in db.Weeks.Include(x => x.TimeEntries)  
  where w.WeekId == 1  
  select w).FirstOrDefault();

answered Aug 2, 2017 at 20:35

Jonathan M

1,88913 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to get a list of objects from Entity Framework database?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related