EntityFramework: How to load List of objects from database?

Question

I have two entities:

public class Booking
{
    [Key]
    public int Id { get; set; }

    public int RoomId { get; set; }
    [ForeignKey("RoomId")]
    public Room Room { get; set; }

    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string DocumentNumber { get; set; }
    public string ContactPhone { get; set; }
}

public class Room
{
    [Key]
    public int RoomId { get; set; }
    public int Number { get; set; }

    public int Size { get; set; }
    public bool HasBalcony { get; set; }

    public int Beds_1 { get; set; }
    public int Beds_2 { get; set; }
    public double DayPrice { get; set; }

    public List<Booking> Bookings { get; set; }
    ...

    public int BookingsCount()
    {
        return Bookings.Count;
    }

    public bool IsFree(DateTime dateTime)
    {
       MessageBox.Show(BookingsCount().ToString());
       return true;
    }
}

and DbContext:

public class HotelContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }
    public DbSet<Room> Rooms { get; set; }
    public DbSet<Booking> Bookings { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Booking>()
        .HasRequired(b => b.Room)
        .WithMany(r => r.Bookings)
        .HasForeignKey(b => b.RoomId);
    }
}

When MessageBox.Show is called I'm getting exception: An unhandled exception of type 'System.NullReferenceException' occurred in Hotel.exe

When I'm trying to access Room::Bookings, the list is always null. There is one row in Bookings table and multiple rows in Rooms table. How can I load all of Bookings into Room object?

Can you add the code you are using to access the data? – Rhumborl Feb 12 '18 at 11:23 — Rhumborl, Feb 12 '18 at 11:23

TheGeneral · Accepted Answer · 2018-02-12T11:43:48.807

Depends where you are in the learning curve, however some things stand out

Firstly

You either want to create a relationship via FluentApi or Annotations, not both

Ie. you have this on your Room entity

[ForeignKey("RoomId")]

And this in fluent

 modelBuilder.Entity<Booking>()
    .HasRequired(b => b.Room)
    .WithMany(r => r.Bookings)
    .HasForeignKey(b => b.RoomId);

You need to pick one or the other, otherwise you may end-up with multiple Ids in your Booking i.e RoomId and Room_Id

Secondly

If you want to be able to Lazy Load bookings you need to make Bookings collection Virtual

public virtual List<Booking> Bookings { get; set; }

Lastly

To access your data (presuming your connection string is correct)

using(var db = new HoteContext())
{
    var rooms = db.Rooms.Include(x => x.Bookings).ToList();
}

Note : Although EF Lazy loads relationships, you might want to make sure you have included the Room->Booking relationship

score 1 · Answer 2 · answered Feb 12 '18 at 12:26

Consider the following code.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;

namespace ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            using (MyDbContext dbContext = new MyDbContext())
            {
                dbContext.Departments.Add(new Department()
                {
                    Name = "Some Department1",
                    Employees=new List<Employee>()
                    {
                        new Employee() { Name = "John Doe" }
                    }
                });

                dbContext.SaveChanges();

                var department = dbContext.Departments.FirstOrDefault(d => d.Name == "Some Department1");

                if (department.Employees != null)
                {
                    foreach (var item in department.Employees)
                    {
                        Console.WriteLine(item.Name);
                    }
                }
            }
        }
    }

    public class Department
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public List<Employee> Employees { get; set; }
    }

    public class Employee
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class MyDbContext : DbContext
    {
        public DbSet<Department> Departments { get; set; }
        public DbSet<Employee> Employees { get; set; }
    }
}

If you have the code in above way, the control will not go into if condition, because department.Employees is null. Now, change the Department entity as follows.

public class Department
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual List<Employee> Employees { get; set; }
}

And now you should be able to see control go into if condition and outputs the employees name.

That is called Lazy Loading.

If you want to eagerly load, you don't have to put virtual to the property. You can Include the properties as follows.

 var department = dbContext.Departments.Include(d => d.Employees).FirstOrDefault(d => d.Name == "Some Department1");

Now you can see the employees names are getting outputted.

score 0 · Answer 3 · answered Feb 12 '18 at 12:01

You will absolutely run into performance trouble with your design here.

The temptation with EF is to completely map your object model to the DB and have EF do all the magic for you behind the scenes. But you need to think about it in terms of only getting specifically what you need from the db at any point in time. Otherwise you will get all kinds of cartesian product issues. I highly suggest you get yourself a copy of Hibernating Rhino's EF Profiler or similar so you can analyze your code statically and at runtime for EF performance issues (and see what SQL it is generating). For this what you want is a purpose built call to the DB to get the count. Otherwise what will happen is you will pull the entire table of Bookings and then have C# give you the count. That only makes sense if you want to do something with the whole list. Two options would be:

1) Create a VIEW against the Bookings table and map that to EF. The view would look something like SELECT ROOMS.ROOMID, COUNT(*) - you map this view to your model and voila now you have a list of counts by room (id) and you can use them individually or sum it up to get your total count for all rooms. If you have 1,000 bookings in 10 rooms, you are getting back only 10 rows from the DB. Whereas with your design, you are pulling back all 1,000 bookings with all their fields and then filtering down in C#. Bad juju.

2) The architecturally and conceptually simpler approach is going to be to do a direct query as such (obviously this returns only a single int from the db):

public int BookingsCount()
{
    int count = 0;

    try
    {
        using (var context = new HotelContext()) 
        { 
            var sql ="SELECT COUNT(*) FROM Bookings WHERE ROOMID=" + this.RoomId;    
            count = context.Database.SqlQuery<int>(sql).First(); 
        }
    } catch (Exception ex)
    {
        // Log your error, count will be 0 by default
    }

    return count;
}

score -1 · Answer 4 · answered Feb 12 '18 at 11:25

-1

A simple solution would be making the Bookings property virtual.

public class Room
{
    [Key]
    public int RoomId { get; set; }
    public int Number { get; set; }

    public int Size { get; set; }
    public bool HasBalcony { get; set; }

    public int Beds_1 { get; set; }
    public int Beds_2 { get; set; }
    public double DayPrice { get; set; }

    public virtual List<Booking> Bookings { get; set; }
}

More information on Entity Framework Loading Related Entities, https://msdn.microsoft.com/en-us/library/jj574232(v=vs.113).aspx

answered Feb 12 '18 at 11:25

Jaliya Udagedara

730
6
11

false: virtual means that you want to use lazy loading on the collection, and then in the query you need to use the .Include() instruction to load realted entities – Embri Feb 12 '18 at 11:29
In EF, when we use Include(), it's Eagerly Loading. – Jaliya Udagedara Feb 12 '18 at 11:36
yes, but it's not the solution adding the virtual, virtual + .Include() could be – Embri Feb 12 '18 at 11:48
If you have virtual, you don't need to Include. I have added another descriptive answer with sample code, you can run it and see. – Jaliya Udagedara Feb 12 '18 at 12:28

EntityFramework: How to load List of objects from database?

4 Answers4