how to use linq with two related entities?

Question

Hello i got a problem while using two related entities in a dotnet project
i have two entities table and reservation and i need to get the table that is reserved for tomorrow but the date is seted in the reservation table here's the code

public class Table
{
    public int Id { get; set; }
    public bool isAvailable { get; set; }
    public int Numero { get; set; }
    public virtual ICollection<Reservation> IReservation { get; set; }
}

public class Reservation
{
    public DateTime DateReservation { get; set; }
    public int Id { get; set; }
    public string Nom { get; set; }
    public virtual Table table { get; set; }
}
public class RestaurantContext :DbContext
{
    public DbSet<Table> tTable { set; get; }
    public DbSet<Reservation> tReservation { set; get; }
    public RestaurantContext() : base("RestaurentDB") {     
    }
}
class TableRepository
{
    RestaurantContext rc = null;

    public TableRepository()
    {
        rc = new RestaurantContext();
    }
    public void Commit()
    {
        rc.SaveChanges();
    }
    public void AddTable(Table m)
    {
        rc.tTable.Add(m);
    }

    public IEnumerable<Table> GetAllTables() {
        return rc.tTable.ToList();
    }
    public IEnumerable<Table> GetTablesReserverdTomorrow() {
       ....
    }

Here i need to get the table which are reserved for tomorrow i tried

var res=rc.tReservation.Where(r => (r.DateReservation == DateTime.Today.AddDays(1))).ToList();
            var res1 = rc.tTable.Select(r => res.Contains(r.Id));
            return res1;

but it seems theres an error

Argument1: Cannot convert from int to Reservation

Answer 1

you could try to use navigation within your query, like:

return rc.tReservation
    .Include(reservation => reservation.Table)
    .Where(r => (r.DateReservation == DateTime.Today.AddDays(1)))
    .Select(reservation => reservation.table).ToList();

Answer 2

In your case res is IEnumerable, it contains Reservation instances, not int values. By the logic of your code, it seems that table and resrvation should have the same id to get a result. I think you should change your code to:

var res=rc.tReservation.Where(r => (r.DateReservation == DateTime.Today.AddDays(1))).ToList();
        var res1 = rc.tTable.Where(r => res.Any(resItem=>resItem.Id == r.Id));
        return res1;

Answer 3

I am assuming you are receiving a linq to entities sql exception. this means that you are trying to use methods not available in sql server.

I have taken a different approach to your problem:

step #1: the repository method introduced which al

/// <summary>
/// A method that allows you to get all tables reserved in a specific period. tables are only returned if they are reserverd.
/// </summary>
/// <param name="start"></param>
/// <param name="end"></param>
/// <returns></returns>
public IEnumerable<Table> GetAllReservedTables(DateTime start, DateTime end)
{
    return this.rc.tReservation
        // filter by date range
        .Where(x => x.DateReservation >= start && x.DateReservation <= end)

        // ensure table is returned
        .Select(x => x.table);
}

Step 2: calling the repository method and ensuring you have the right date range, which is tomorrow:

    TableRepository repo = new TableRepository();

    // figure out the 24 hour period you need to find reserved tables for
    // it is advisible when searching by date to use a date range instead of once specific date
    // the start date and end date will satisfy the 24 hour period of tomorrow.

    // get start tomorrow
    DateTime startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).AddDays(1);

    // get end of tomorrow
    DateTime endDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).AddDays(2);

    // call the repository method with the date range (the 24 hour period for tomorrow)
    var tablesForTomorrow = repo.GetAllReservedTables(startDate, endDate);

    // dispaly data in console
    foreach(var table in tablesForTomorrow)
    {
        Console.WriteLine("Table Number: #{0}", table.Numero);
    }

please see the below complete solution:

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ReservationTableIssue
{
    class Program
    {
        static void Main(string[] args)
        {

            TableRepository repo = new TableRepository();

            // step #1 - figure out the 24 hour period you need to find reserved tables for
            // it is advisible when searching by date to use a date range instead of once specific date
            // the start date and end date will satisfy the 24 hour period of tomorrow.

            // get start tomorrow
            DateTime startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).AddDays(1);

            // get end of tomorrow
            DateTime endDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).AddDays(2);

            // call the repository method with the date range (the 24 hour period for tomorrow)
            var tablesForTomorrow = repo.GetAllReservedTables(startDate, endDate);

            // dispaly data in console
            foreach(var table in tablesForTomorrow)
            {
                Console.WriteLine("Table Number: #{0}", table.Numero);
            }

            Console.WriteLine("press any key to exit");
            Console.ReadKey();

        }
    }

    public class Table
    {
        public int Id { get; set; }
        public bool isAvailable { get; set; }
        public int Numero { get; set; }
        public virtual ICollection<Reservation> IReservation { get; set; }
    }

    public class Reservation
    {
        public DateTime DateReservation { get; set; }
        public int Id { get; set; }
        public string Nom { get; set; }
        public virtual Table table { get; set; }
    }
    public class RestaurantContext :DbContext
    {
        public DbSet<Table> tTable { set; get; }
        public DbSet<Reservation> tReservation { set; get; }
        public RestaurantContext() : base("RestaurentDB") {     
        }
    }

    public class TableRepository
    {
        RestaurantContext rc = null;

        public TableRepository()
        {
            rc = new RestaurantContext();
        }
        public void Commit()
        {
            rc.SaveChanges();
        }
        public void AddTable(Table m)
        {
            rc.tTable.Add(m);
        }

        public IEnumerable<Table> GetAllTables()
        {
            return rc.tTable.ToList();
        }

        /// <summary>
        /// A method that allows you to get all tables reserved in a specific period. tables are only returned if they are reserverd.
        /// </summary>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public IEnumerable<Table> GetAllReservedTables(DateTime start, DateTime end)
        {
            return this.rc.tReservation
                // filter by date range
                .Where(x => x.DateReservation >= start && x.DateReservation <= end)

                // ensure table is returned
                .Select(x => x.table);
        }
    }

}