public static T GetById(int id)
{
using (var context = new TourDbContext())
{
return(context.Set <T>().Find(id));
}
}
public static IEnumerable <T> Get(
Expression <Func <T, bool> > filter = null,
Func <IQueryable <T>, IOrderedQueryable <T> > orderBy = null,
List <Expression <Func <T, object> > > includeProperties = null,
int?page = null,
int?pageSize = null)
{
using (var context = new TourDbContext())
{
IQueryable <T> query = context.Set <T>();
if (includeProperties != null)
{
includeProperties.ForEach(i => query = query.Include(i));
}
if (filter != null)
{
query = query.Where(filter);
}
if (orderBy != null)
{
query = orderBy(query);
}
if (page != null && pageSize != null)
{
query = query.Skip((page.Value - 1) * pageSize.Value).Take(pageSize.Value);
}
return(query.AsNoTracking().ToList());
}
}
public static List <CostStatistic> GetCostStatisticsByTourId(int tourId, DateTime?startDate, DateTime?endDate)
{
string query = string.Format(@"
SELECT CostTypes.Id, CostTypes.Name, SUM(Costs.Price) as Price
FROM Groups, Costs, CostTypes
WHERE Groups.Id = Costs.GroupId
AND Costs.CostTypeId = CostTypes.Id
AND Groups.TourId = {0}
", tourId);
if (startDate != null && endDate != null)
{
query += string.Format(@"
AND Groups.StartDate >= CAST('{0}' as date)
AND Groups.StartDate <= CAST('{1}' as date)
", startDate.ToString(), endDate.ToString());
}
query += "Group BY CostTypes.Id, CostTypes.Name";
using (var context = new TourDbContext())
{
return(context.Database.SqlQuery <CostStatistic>(query).ToList());
}
}
public static IEnumerable <T> Find(Expression <Func <T, bool> > predicate)
{
using (var context = new TourDbContext())
{
return(context.Set <T>().Where(predicate).ToList());
}
}
public static void Update(T entity)
{
using (var context = new TourDbContext())
{
context.Entry(entity).State = EntityState.Modified;
context.SaveChanges();
}
}
public static void Add(T entity)
{
using (var context = new TourDbContext())
{
context.Entry(entity).State = EntityState.Added;
context.Set <T>().Add(entity);
context.SaveChanges();
}
}
public static void Remove(T entity)
{
using (var context = new TourDbContext())
{
context.Entry(entity).State = EntityState.Deleted;
context.Set <T>().Remove(entity);
context.SaveChanges();
}
}
public static void RemoveRange(IEnumerable <T> entities)
{
using (var context = new TourDbContext())
{
foreach (T entity in entities)
{
context.Entry(entity).State = EntityState.Deleted;
}
context.Set <T>().RemoveRange(entities);
context.SaveChanges();
}
}
private void Form1_Load(object sender, EventArgs e)
{
TourDbContext context = new TourDbContext();
var query = context.Tourists.Where(x => x.Gender.GenderDescription == "Woman").Select(x => new { x.FirstName, x.LastName, x.Gender.GenderDescription }).ToList();
var q1 = context.Guides.Join(context.TouristGuidePlaces,
x => x.GuideID,
a => a.GuideID,
(x, a) => new
{
ad = x.FirstName,
soyad = x.LastName,
tID = a.TouristID
}).Join(context.Tourists,
x => x.tID,
g => g.TouristID,
(x, g) => new
{
x.ad,
x.soyad
}).ToList();
var q2 = context.Tourists.Join(context.Nationalities,
t => t.NationalityID,
n => n.NationalityID,
(t, n) => new
{
ad = t.FirstName + " " + t.LastName,
Uyruk = n.NationalityDescription,
n1ID = t.NationalityID,
n2ID = n.NationalityID
}).Where(x => x.n1ID == x.n2ID).ToList();
dataGridView1.DataSource = q2;
dataGridView1.Columns[2].Visible = false;
dataGridView1.Columns[3].Visible = false;
label1.Text = dataGridView1.Rows.Count.ToString();
}
public static List <TourCountOfStaff> GetTourCountOfStaffs(DateTime?startDate, DateTime?endDate)
{
string query = @"
SELECT Staffs.Id AS StaffId, Staffs.Name AS StaffName, COUNT(GroupId) As TourCount
FROM Staffs LEFT JOIN Assignments ON Assignments.StaffId = Staffs.Id
LEFT JOIN Groups ON Groups.Id = Assignments.GroupId
";
if (startDate != null && endDate != null)
{
query += string.Format(@"
AND Groups.StartDate >= CAST('{0}' as date)
AND Groups.StartDate <= CAST('{1}' as date)
", startDate.ToString(), endDate.ToString());
}
query += "Group BY Staffs.Id, Staffs.Name";
using (var context = new TourDbContext())
{
return(context.Database.SqlQuery <TourCountOfStaff>(query).ToList());
}
}
public AdminController(TourDbContext context, IWebHostEnvironment hostEnv)
{
_context = context;
_hostEnv = hostEnv;
}
/// <summary>
/// Create new instance of tour repository
/// </summary>
/// <param name="context">The tour db context</param>
public TourRepository(TourDbContext context) : base(context)
{
}
public HomeController(ILogger <HomeController> logger, iTourRepository repository, TourDbContext context)
{
_logger = logger;
_repository = repository;
_context = context;
}
/// <summary>
/// Create new instance of client repository
/// </summary>
/// <param name="context">The client db context</param>
public ClientRepository(TourDbContext context) : base(context)
{
}
public TourRepository(TourDbContext context)
{
_context = context;
}
//Constructor
public HomeController(TourDbContext con, ITourRepo repository)
{
context = con;
_repository = repository;
}
public HomeController(TourDbContext context, ILogger <HomeController> logger)
{
_context = context;
_logger = logger;
}
/// <summary>
/// Create new instance of booking repository
/// </summary>
/// <param name="context">The booking db context</param>
public BookingRepository(TourDbContext context) : base(context)
{
}
//Constructor for the controller
public HomeController(ILogger <HomeController> logger, TourDbContext context)
{
_logger = logger;
_context = context;
}
//constructor that gets the repository and context db's
public HomeController(ILogger <HomeController> logger, ITourRepository repository, TourDbContext ctx)
{
_logger = logger;
_repository = repository;
context = ctx;
}
