public static async Task <IEnumerable <ProductItemRow> > GetProductItemRowsAsync(int productId, int sectionId, int itemTypeId, string userId, ApplicationDbContext db = null)
{
if (db == null)
{
db = ApplicationDbContext.Create();
}
var today = DateTime.Now.Date;
var items = await(from i in db.Items
join it in db.ItemTypes on i.ItemTypeId equals it.Id
join pi in db.ProductItems on i.Id equals pi.ItemId
join sp in db.SubscriptionProducts on pi.ProductId equals sp.ProductId
join us in db.UserSubscriptions on sp.SubscriptionId equals us.SubscriptionId
where i.SectionId.Equals(sectionId) &&
//i.ItemTypeId.Equals(itemTypeId) &&
pi.ProductId.Equals(productId) &&
us.UserId.Equals(userId)
orderby i.PartId
select new ProductItemRow
{
ItemId = i.Id,
Description = i.Description,
Title = i.Title,
Link = it.Title.Equals("Download") ? i.Url : "/ProductContent/Content/" + pi.ProductId + "/" + i.Id,
ImageUrl = i.ImageUrl,
ReleaseDate = DbFunctions.CreateDateTime(us.StartDate.Value.Year, us.StartDate.Value.Month, us.StartDate.Value.Day + i.WaitDays, 0, 0, 0),
IsAvailable = DbFunctions.CreateDateTime(today.Year, today.Month, today.Day, 0, 0, 0) >= DbFunctions.CreateDateTime(us.StartDate.Value.Year, us.StartDate.Value.Month, us.StartDate.Value.Day + i.WaitDays, 0, 0, 0),
IsDownload = it.Title.Equals("Download")
}).ToListAsync();
return(items);
}
public void linqTest(DWH_REPLICAEntities db)
{
db.Database.CommandTimeout = 180;
var a =
from s
in db.FD_ACQ_D
where DbFunctions.CreateDateTime(s.DT_REG.Year, s.DT_REG.Month, s.DT_REG.Day, 00, 00, 00)
== DbFunctions.CreateDateTime(2016, 09, 01, 00, 00, 00)
group s by new
{
DATE = DbFunctions.CreateDateTime(s.DT_REG.Year, s.DT_REG.Month, s.DT_REG.Day, 01, 01, 01)
,
PAY_SYS = s.PAY_SYS
,
TRAN_TYPE = s.TYPE_TRANSACTION
,
MERCH = s.MERCHANT
} into g
select new
{
DATE = g.Key.DATE
,
TRAN_TYPE = g.Key.TRAN_TYPE
,
PAY_SYS = g.Key.PAY_SYS
,
MERCH = g.Key.MERCH
,
AMT = g.Sum(t => t.AMT)
};
var b = a.Count();
}
public IQueryable <table1> del_date()
{
return(from s in db.table1
where DbFunctions.CreateDateTime(s.date1.Value.Year, s.date1.Value.Month, 01, 00, 00, 00) >= DbFunctions.CreateDateTime(2016, 01, 01, 00, 00, 00) &&
DbFunctions.CreateDateTime(s.date1.Value.Year, s.date1.Value.Month, 01, 00, 00, 00) <= DbFunctions.CreateDateTime(2016, 02, 01, 00, 00, 00)
select s);
}
/// <summary>
/// Gets a LINQ-to-entities compatible expression to group <see cref="SessionActivityData" /> by status.
/// </summary>
internal static Expression <Func <SessionActivityData, SessionActivityGroupKey> > CreateGroupKey(SessionActivityGroupFields fields)
{
// Check the flags to see which fields should be grouped
bool groupByName = fields.HasFlag(SessionActivityGroupFields.ActivityName);
bool groupByType = fields.HasFlag(SessionActivityGroupFields.ActivityType);
bool groupByUser = fields.HasFlag(SessionActivityGroupFields.UserName);
bool groupByHost = fields.HasFlag(SessionActivityGroupFields.HostName);
bool groupByStartTime = fields.HasFlag(SessionActivityGroupFields.StartDateTime);
bool groupByEndTime = fields.HasFlag(SessionActivityGroupFields.EndDateTime);
bool groupByStatus = fields.HasFlag(SessionActivityGroupFields.Status);
bool groupByMessage = fields.HasFlag(SessionActivityGroupFields.ResultMessage);
bool groupByCategory = fields.HasFlag(SessionActivityGroupFields.ResultCategory);
// Create a new group key that includes or ignores fields depending on whether the flag was set.
// StartDateTime and EndDateTime use a DbFunction to create a new DateTime that rounds down to the nearest minute.
return(n => new SessionActivityGroupKey
{
ActivityName = groupByName ? n.ActivityName : null,
ActivityType = groupByType ? n.ActivityType : null,
UserName = groupByUser ? n.UserName : null,
HostName = groupByHost ? n.HostName : null,
Status = groupByStatus ? n.Status : null,
ResultMessage = groupByMessage ? n.ResultMessage : null,
ResultCategory = groupByCategory ? n.ResultCategory : null,
StartDateTime = groupByStartTime ? DbFunctions.CreateDateTime(n.StartDateTime.Value.Year, n.StartDateTime.Value.Month, n.StartDateTime.Value.Day, n.StartDateTime.Value.Hour, n.StartDateTime.Value.Minute, 0) : null,
EndDateTime = groupByEndTime ? DbFunctions.CreateDateTime(n.EndDateTime.Value.Year, n.EndDateTime.Value.Month, n.EndDateTime.Value.Day, n.EndDateTime.Value.Hour, n.EndDateTime.Value.Minute, 0) : null
});
}
public List <CompanyHolidayView> CompanyHolidays(int companyId)
{
List <CompanyHolidayView> companyHolidays;
using (UnitOfWork unitOfWork = new UnitOfWork())
{
companyHolidays = (from h in unitOfWork.HolidayRepository.Get()
join t in unitOfWork.HolidayTypeHolidayRepository.Get() on h.Id equals t.HolidayId
join ht in unitOfWork.HolidayTypeRepository.Get() on t.TypeId equals ht.Id
from ch in unitOfWork.CompanyHolidayRepository.Get(x => x.HolidayId == h.Id).DefaultIfEmpty()
where ch.CompanyId == companyId || ch.CompanyId == 0
select new CompanyHolidayView
{
CompanyId = ch.CompanyId,
HolidayId = h.Id,
HolidayName = h.Name,
HolidayType = ht.Name,
HolidayDate = DbFunctions.CreateDateTime(
DateTime.Now.Year,
h.HolidayMonth,
h.HolidayDay, 0, 0, 0)
}).ToList();
}
return(companyHolidays);
}
public void GetACQbyMonth(DWH_REPLICAEntities db, DateTime dateFrom, DateTime dateTo)
{
DateTime fromDate = dateFrom.Date;
DateTime toDate = dateTo.Date;
FD_ACQ = new List <FD_ACQ_DTO>();
var a =
from s in db.FD_ACQ_D
where DbFunctions.CreateDateTime(s.DT_REG.Year, s.DT_REG.Month, 01, 0, 0, 0) ==
DbFunctions.CreateDateTime(fromDate.Year, fromDate.Month, 01, 0, 0, 0)
group s by new
{
DT_REG = DbFunctions.CreateDateTime(s.DT_REG.Year, s.DT_REG.Month, 01, 0, 0, 0)
,
TYPE_TRANSACTION = s.TYPE_TRANSACTION,
PAY_SYSTEM = s.PAY_SYS
} into g
select new
{
DT_REG = g.Key.DT_REG,
TYPE_TRANSACTION = g.Key.TYPE_TRANSACTION,
PAY_SYSTEM = g.Key.PAY_SYSTEM,
AMT = g.Sum(s => s.AMT)
};
foreach (var b in a)
{
this.FD_ACQ.Add(new FD_ACQ_DTO {
DT_REG = b.DT_REG, TYPE_TRANSACTION = b.TYPE_TRANSACTION, AMT = b.AMT, PAY_SYS = b.PAY_SYSTEM
});
}
}
//
public void Lmbda()
{
DateTime dt = DateTime.Now;
BI_test db = new BI_test();
//var a = GetByRegionID(orcl_db.COUNTRIES, 1);
//var arr = del.Invoke(db, new DateTime(2016, 01, 01, 00, 00, 00), new DateTime(2016, 02, 01, 00, 00, 00));
IQueryable <table1DTO> arr3 =
from s in db.table1
where DbFunctions.CreateDateTime(s.date1.Value.Year, s.date1.Value.Month, 01, 00, 00, 00) >= DbFunctions.CreateDateTime(2016, 01, 01, 00, 00, 00) &&
DbFunctions.CreateDateTime(s.date1.Value.Year, s.date1.Value.Month, 01, 00, 00, 00) <= DbFunctions.CreateDateTime(2016, 02, 01, 00, 00, 00)
group s by new { DATE = DbFunctions.CreateDateTime(s.date1.Value.Year, s.date1.Value.Month, 01, 00, 00, 00), range = s.range1 } into g
select new table1DTO {
DATE = g.Key.DATE, RANGE = g.Key.range, AMT = g.Sum(s => s.value1)
};
var arr4 = del_table1_group();
int cnt = arr3.Count();
table1Condition.addProperties(arr3.GetType());
List <string> str = table1Condition.propertiesList;
}
public Visit GetLastVisit()
{
DateTime date = DateTime.Today;
return(Table.Include(v => v.Person).Where(v => v.Date >= date)
.OrderByDescending(v => DbFunctions.CreateDateTime(date.Year, date.Month, date.Day, v.Person.DateOfBirth.Hour, v.Person.DateOfBirth.Minute, v.Person.DateOfBirth.Second))
.FirstOrDefault());
}
public List <Visit> GetVisitsToday()
{
DateTime date = DateTime.Today;
return(Table.Include(v => v.Person).Where(v => v.Date >= date)
.OrderByDescending(v => DbFunctions.CreateDateTime(date.Year, date.Month, date.Day, v.Person.DateOfBirth.Hour, v.Person.DateOfBirth.Minute, v.Person.DateOfBirth.Second))
.ToList());
}
public IQueryable <table1DTO> del_table1_group()
{
del_table1 dt1 = del_date;
return(from s in dt1()
group s by new { DATE = DbFunctions.CreateDateTime(s.date1.Value.Year, s.date1.Value.Month, 01, 00, 00, 00), range = s.range1 } into g
select new table1DTO {
DATE = g.Key.DATE, RANGE = g.Key.range, AMT = g.Sum(s => s.value1)
});
}
public void QueryTestDbFunctionsCreateDateTime1()
{
using (var c = GetDbContext <DbFunctionsContext>())
{
var q = c.Quxs
.Where(x => x.QuxDateTime == DbFunctions.CreateDateTime(2020, 3, 19, 14, 12, 0))
.ToString();
StringAssert.Contains("CAST('2020-3-19 14:12:00' AS TIMESTAMP)", q.ToString());
}
}
public void QueryTestDbFunctionsCreateDateTime2()
{
using (var c = GetDbContext <DbFunctionsContext>())
{
var q = c.Quxs
.Where(x => x.QuxDateTime == DbFunctions.CreateDateTime(2020, 3, 19, 14, 12, 36))
.ToString();
StringAssert.Contains("DATEADD(SECOND, CAST(36 AS DOUBLE PRECISION), CAST('2020-3-19 14:12:00' AS TIMESTAMP))", q.ToString());
}
}
public void QueryTestDbFunctionsCreateDateTime4()
{
using (var c = GetDbContext <DbFunctionsContext>())
{
var q = c.Quxs
.Where(x => x.QuxDateTime == DbFunctions.CreateDateTime(x.QuxYear, x.QuxMonth, x.QuxDay, null, null, null))
.ToString();
StringAssert.Contains("DATEADD(DAY, -1, DATEADD(MONTH, -1, DATEADD(YEAR, -1, DATEADD(DAY, \"B\".\"QuxDay\", DATEADD(MONTH, \"B\".\"QuxMonth\", DATEADD(YEAR, \"B\".\"QuxYear\", CAST('0001-01-01 00:00:00' AS TIMESTAMP)))))))", q.ToString());
}
}
internal List <ShiftChangeRequest> ShiftChangeRequestList(List <Guid> mgrBusIdList)
{
DateTime datetimeNow = WebUI.Common.Common.DateTimeNowLocal();
return((from sr in db.ShiftChangeRequests
where sr.Status == RequestStatus.Pending &&
mgrBusIdList.Contains(sr.Shift.InternalLocation.BusinessLocation.Id) &&
DbFunctions.CreateDateTime(sr.Shift.StartTime.Year, sr.Shift.StartTime.Month, sr.Shift.StartTime.Day, sr.Shift.StartTime.Hour, sr.Shift.StartTime.Minute, sr.Shift.StartTime.Second) >= datetimeNow //Only get change requests which are in the future.
select sr).ToList());
}
public void DbFunctionsTests_CreateDateTime_Test()
{
var result = this.GetOrderQuery().Select(x => (DateTime)DbFunctions.CreateDateTime(YEAR, MONTH, DAY, HOUR, MINUTE, SECOND)).First();
Assert.AreEqual(YEAR, result.Year);
Assert.AreEqual(MONTH, result.Month);
Assert.AreEqual(DAY, result.Day);
Assert.AreEqual(HOUR, result.Hour);
Assert.AreEqual(MINUTE, result.Minute);
Assert.AreEqual(SECOND, result.Second);
}
public IEnumerable <EpisodeDTO> FutureEpisodes()
{
using (var context = new TvShowOrganizerContext())
{
var date = DateTime.Now;
return(context
.Episodes
.Include("TvShow")
.Where(e => DbFunctions.CreateDateTime(e.FirstAired.Value.Year, e.FirstAired.Value.Month, e.FirstAired.Value.Day, 0, 0, 0) >= DbFunctions.CreateDateTime(date.Year, date.Month, date.Day, 0, 0, 0) || !e.FirstAired.HasValue).ToList()
.Select(e => new EpisodeDTO(e)));
}
}
public IEnumerable <EpisodeDTO> NotDownloadedEpisodes()
{
var date = DateTime.Now;
using (var context = new TvShowOrganizerContext())
{
return(context.Episodes
.Include("TvShow")
.Where(e => !e.Downloaded && DbFunctions.CreateDateTime(e.FirstAired.Value.Year, e.FirstAired.Value.Month, e.FirstAired.Value.Day, 0, 0, 0) < DbFunctions.CreateDateTime(date.Year, date.Month, date.Day, 0, 0, 0)).ToList()
.Select(e => new EpisodeDTO(e)));
}
}
public Repository <T> SQLFilterByMonth(DateTime DateFrom_, DateTime DateTo_)
{
QueryResult = from s in QueryResult
where DbFunctions.CreateDateTime(s.DATE.Value.Year, s.DATE.Value.Month, 01, 00, 00, 00) >=
DbFunctions.CreateDateTime(DateFrom_.Year, DateFrom_.Month, 01, 00, 00, 00)
&&
DbFunctions.CreateDateTime(s.DATE.Value.Year, s.DATE.Value.Month, 01, 00, 00, 00) <=
DbFunctions.CreateDateTime(DateTo_.Year, DateTo_.Month, 01, 00, 00, 00)
select s;
return(this);
}
public ApiResult GetAll(ClassTypeEnums?ClassType = 0, TableTypeEnums?TableType = 0, DateTime?BeginDate = null, DateTime?EndDate = null, int pageindex = 1, int pagesize = 10)
{
ApiResult result = new ApiResult();
string message = string.Empty;
try
{
using (FLDbContext db = new FLDbContext())
{
BaseUtils bu = new BaseUtils();
BeginDate = bu.InitDate(BeginDate, true);
EndDate = bu.InitDate(EndDate, false);
int total = db.BaseDateFlow.Where(t => t.BeginDate > DbFunctions.CreateDateTime(t.BeginDate.Year, t.BeginDate.Month, t.BeginDate.Day, BeginDate.Value.Hour, BeginDate.Value.Minute, BeginDate.Value.Second) &&
t.EndDate < DbFunctions.CreateDateTime(t.EndDate.Year, t.EndDate.Month, t.EndDate.Day, EndDate.Value.Hour, EndDate.Value.Minute, EndDate.Value.Second) &&
(ClassType == 0 || t.ClassType == ClassType) &&
(TableType == 0 || t.TableType == TableType)).Count();
List <BaseDateFlow> bdfList = db.BaseDateFlow.Where(t => t.BeginDate > DbFunctions.CreateDateTime(t.BeginDate.Year, t.BeginDate.Month, t.BeginDate.Day, BeginDate.Value.Hour, BeginDate.Value.Minute, BeginDate.Value.Second) &&
t.EndDate < DbFunctions.CreateDateTime(t.EndDate.Year, t.EndDate.Month, t.EndDate.Day, EndDate.Value.Hour, EndDate.Value.Minute, EndDate.Value.Second) &&
(ClassType == 0 || t.ClassType == ClassType) &&
(TableType == 0 || t.TableType == TableType)).OrderBy(t => t.TableType).ThenBy(t => t.FlowID).Skip((pageindex - 1) * pagesize).Take(pagesize).ToList();
List <object> returnlist = new List <object>();
foreach (BaseDateFlow bdf in bdfList)
{
returnlist.Add(new
{
bdf.ID,
bdf.Name,
TableType = Enum.GetName(typeof(TableTypeEnums), bdf.TableType),
ClassType = Enum.GetName(typeof(ClassTypeEnums), bdf.ClassType),
BeginDate = bdf.BeginDate.ToString("HH:mm"),
EndDate = bdf.EndDate.ToString("HH:mm"),
RemindDate = bdf.RemindDate.ToString("HH:mm"),
});
}
result = ApiResult.NewSuccessJson(new
{
Total = total,
List = returnlist
});
}
}
catch
{
result = ApiResult.NewErrorJson("请检查网络状态或联系系统管理员");
}
if (!string.IsNullOrEmpty(message))
{
result = ApiResult.NewErrorJson(message);
}
return(result);
}
public static int ObtenerPeriodoCalendario445(TAT001Entities db, string sociedad_id, string tsol_id, string usuario_id = null)
{
//tipo
//PRE = PreCierre
//CI = Cierre
DateTime fechaActual = DateTime.Now;
short ejercicio = short.Parse(fechaActual.Year.ToString());
CALENDARIO_AC calendarioAc = null;
CALENDARIO_EX calendarioEx = null;
calendarioAc = db.CALENDARIO_AC.FirstOrDefault(x =>
x.ACTIVO &&
x.SOCIEDAD_ID == sociedad_id &&
x.TSOL_ID == tsol_id &&
x.EJERCICIO == ejercicio &&
(fechaActual >= DbFunctions.CreateDateTime(x.PRE_FROMF.Year, x.PRE_FROMF.Month, x.PRE_FROMF.Day, x.PRE_FROMH.Hours, x.PRE_FROMH.Minutes, x.PRE_FROMH.Seconds) &&
fechaActual <= DbFunctions.CreateDateTime(x.PRE_TOF.Year, x.PRE_TOF.Month, x.PRE_TOF.Day, x.PRE_TOH.Hours, x.PRE_TOH.Minutes, x.PRE_TOH.Seconds)));
if (calendarioAc != null)
{
return(calendarioAc.PERIODO);
}
if (usuario_id != null)
{
calendarioEx = db.CALENDARIO_EX.FirstOrDefault(x =>
x.ACTIVO &&
x.SOCIEDAD_ID == sociedad_id &&
x.TSOL_ID == tsol_id &&
x.USUARIO_ID == usuario_id &&
x.EJERCICIO == ejercicio &&
(fechaActual >= DbFunctions.CreateDateTime(x.EX_FROMF.Year, x.EX_FROMF.Month, x.EX_FROMF.Day, x.EX_FROMH.Hours, x.EX_FROMH.Minutes, x.EX_FROMH.Seconds) &&
fechaActual <= DbFunctions.CreateDateTime(x.EX_TOF.Year, x.EX_TOF.Month, x.EX_TOF.Day, x.EX_TOH.Hours, x.EX_TOH.Minutes, x.EX_TOH.Seconds)));
if (calendarioEx != null)
{
return(calendarioEx.PERIODO);
}
}
calendarioAc = db.CALENDARIO_AC.FirstOrDefault(x =>
x.ACTIVO &&
x.SOCIEDAD_ID == sociedad_id &&
x.TSOL_ID == tsol_id &&
x.EJERCICIO == ejercicio &&
(fechaActual >= DbFunctions.CreateDateTime(x.CIE_FROMF.Year, x.CIE_FROMF.Month, x.CIE_FROMF.Day, x.CIE_FROMH.Hours, x.CIE_FROMH.Minutes, x.CIE_FROMH.Seconds) &&
fechaActual <= DbFunctions.CreateDateTime(x.CIE_TOF.Year, x.CIE_TOF.Month, x.CIE_TOF.Day, x.CIE_TOH.Hours, x.CIE_TOH.Minutes, x.CIE_TOH.Seconds)));
if (calendarioAc != null)
{
return(calendarioAc.PERIODO);
}
return(0);
}
public static bool ValidarPeriodoEnCalendario445(TAT001Entities db, string sociedad_id, string tsol_id, int periodo_id, string tipo, string usuario_id = null)
{
//tipo
//PRE = PreCierre
//CI = Cierre
bool esPeriodoAbierto = false;
DateTime fechaActual = DateTime.Now;
short ejercicio = short.Parse(fechaActual.Year.ToString());
switch (tipo)
{
case "PRE":
esPeriodoAbierto = db.CALENDARIO_AC.Any(x =>
x.ACTIVO &&
x.SOCIEDAD_ID == sociedad_id &&
x.TSOL_ID == tsol_id &&
x.PERIODO == periodo_id &&
x.EJERCICIO == ejercicio &&
(fechaActual >= DbFunctions.CreateDateTime(x.PRE_FROMF.Year, x.PRE_FROMF.Month, x.PRE_FROMF.Day, x.PRE_FROMH.Hours, x.PRE_FROMH.Minutes, x.PRE_FROMH.Seconds) &&
fechaActual <= DbFunctions.CreateDateTime(x.PRE_TOF.Year, x.PRE_TOF.Month, x.PRE_TOF.Day, x.PRE_TOH.Hours, x.PRE_TOH.Minutes, x.PRE_TOH.Seconds)));
if (!esPeriodoAbierto && usuario_id != null)
{
esPeriodoAbierto = db.CALENDARIO_EX.Any(x =>
x.ACTIVO &&
x.SOCIEDAD_ID == sociedad_id &&
x.TSOL_ID == tsol_id &&
x.PERIODO == periodo_id &&
x.USUARIO_ID == usuario_id &&
x.EJERCICIO == ejercicio &&
(fechaActual >= DbFunctions.CreateDateTime(x.EX_FROMF.Year, x.EX_FROMF.Month, x.EX_FROMF.Day, x.EX_FROMH.Hours, x.EX_FROMH.Minutes, x.EX_FROMH.Seconds) &&
fechaActual <= DbFunctions.CreateDateTime(x.EX_TOF.Year, x.EX_TOF.Month, x.EX_TOF.Day, x.EX_TOH.Hours, x.EX_TOH.Minutes, x.EX_TOH.Seconds)));
}
break;
case "CI":
esPeriodoAbierto = db.CALENDARIO_AC.Any(x =>
x.ACTIVO &&
x.SOCIEDAD_ID == sociedad_id &&
x.TSOL_ID == tsol_id &&
x.PERIODO == periodo_id &&
x.EJERCICIO == ejercicio &&
(fechaActual >= DbFunctions.CreateDateTime(x.CIE_FROMF.Year, x.CIE_FROMF.Month, x.CIE_FROMF.Day, x.CIE_FROMH.Hours, x.CIE_FROMH.Minutes, x.CIE_FROMH.Seconds) &&
fechaActual <= DbFunctions.CreateDateTime(x.CIE_TOF.Year, x.CIE_TOF.Month, x.CIE_TOF.Day, x.CIE_TOH.Hours, x.CIE_TOH.Minutes, x.CIE_TOH.Seconds)));
break;
default:
break;
}
return(esPeriodoAbierto);
}
public void SQL_cehck()
{
SQL_entity ent = new SQL_entity();
Repository <TEMP_ACQ_D> rp = new Repository <TEMP_ACQ_D>(ent);
var a = from s in rp.SQLGetByMonth(st, fn) select s;
int b = a.Count();
var a_ = from s in ent.TEMP_ACQ_D
where DbFunctions.CreateDateTime(s.DATE.Value.Year, s.DATE.Value.Month, s.DATE.Value.Day, 00, 00, 00) == st
select s;
int b_ = a_.Count();
}
public IQueryable <T> SQLGetByYear(DateTime DateFrom_, DateTime DateTo_)
{
var a =
from s in this.dbSet
where DbFunctions.CreateDateTime(s.DATE.Value.Year, 01, 01, 00, 00, 00) >=
DbFunctions.CreateDateTime(DateFrom_.Year, 01, 01, 00, 00, 00)
&&
DbFunctions.CreateDateTime(s.DATE.Value.Year, 01, 01, 00, 00, 00) <=
DbFunctions.CreateDateTime(DateTo_.Year, 01, 01, 00, 00, 00)
select s;
this.QueryResult = a;
return(a);
}
public IList <CaseAppointment> DueCases(int days)
{
//Use DbFunctikons.AddDays to get rid of limitation in linq to entities that it does not support
//DataTime.AddDays, instead of loading all the store data then filter using linq to objects.
//Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
DateTime dueDate = DateTime.Now.AddDays(days);
var s = Query(x =>
DbFunctions.CreateDateTime(x.AppointmentGregDate.Year, x.AppointmentGregDate.Month, x.AppointmentGregDate.Day, 0, 0, 0) >= DateTime.Now
&&
DbFunctions.CreateDateTime(x.AppointmentGregDate.Year, x.AppointmentGregDate.Month, x.AppointmentGregDate.Day, 0, 0, 0) <= dueDate
);
return(s.ToList());
}
internal IEnumerable <ContentBytesChartDataItem> AggregateContentBytesChartData(
IQueryable <DbRequestLogEntry> query, TimeFrame timeFrame)
{
var groupedQuery = GetGrouping(query, timeFrame.Resolution);
return(groupedQuery
.Select(group => new ContentBytesChartDataItem()
{
In = group.Sum(entry => entry.ContentBytesIn),
Out = group.Sum(entry => entry.ContentBytesOut),
Key = DbFunctions.CreateDateTime(group.Key.Year, group.Key.Month ?? 1, group.Key.Day ?? 1, 0, 0, 0).Value,
})
.OrderBy(entry => entry.Key)
.ToList());
}
public void CreateDateTime()
{
#if !EFOLD
var q = this.Entities
.Select(x => DbFunctions.CreateDateTime(x.DateTime.Year, 5, 1, 0, 0, 0));
#else
var q = this.Entities
.Select(x => EntityFunctions.CreateDateTime(x.DateTime.Year, 5, 1, 0, 0, 0));
#endif
var q2 = q.AsEnumerable()
.Where(x => x.Value.Month == 5);
q2.Should().NotBeEmpty();
}
public static IEnumerable GetCustomerReports()
{
var maxOrderDate = DB.Orders.Max(o => o.OrderDate) ?? DateTime.Today;
var actualOrderYearDelta = DateTime.Today.Year - maxOrderDate.Year;
var query = from customer in DB.Customers
join order in DB.Orders on customer.CustomerID equals order.CustomerID
join order_detail in DB.Order_Details on order.OrderID equals order_detail.OrderID
join product in DB.Products on order_detail.ProductID equals product.ProductID
select new {
product.ProductName,
customer.CompanyName,
OrderDate = DbFunctions.CreateDateTime(order.OrderDate.Value.Year + actualOrderYearDelta, order.OrderDate.Value.Month, order.OrderDate.Value.Day, 0, 0, 0),
ProductAmount = (float)(order_detail.UnitPrice * order_detail.Quantity) * (1 - order_detail.Discount / 100) * 100
};
return(query.ToList());
}
public static IQueryable <FD_ACQ_DTO> list_d(Parameters parameters_)
{
return(from s in list_bool(parameters_)
where DbFunctions.CreateDateTime(s.DT_REG.Value.Year, s.DT_REG.Value.Month, s.DT_REG.Value.Day, 00, 00, 00) >=
DbFunctions.CreateDateTime(parameters_.dateFrom.Year, parameters_.dateFrom.Month, parameters_.dateFrom.Day, 00, 00, 00)
&&
DbFunctions.CreateDateTime(s.DT_REG.Value.Year, s.DT_REG.Value.Month, s.DT_REG.Value.Day, 00, 00, 00) <=
DbFunctions.CreateDateTime(parameters_.dateTo.Year, parameters_.dateTo.Month, parameters_.dateTo.Day, 00, 00, 00)
select new FD_ACQ_DTO
{
DT_REG = s.DT_REG,
TYPE_TRANSACTION = s.TYPE_TRANSACTION,
MERCHANT = s.MERCHANT,
PAY_SYS = s.PAY_SYS,
AMT = s.AMT
});
}
public EpisodeDTO GetLastEpisodeBySeasonAndFirstAired(Guid serieID)
{
using (var context = new TvShowOrganizerContext())
{
var date = DateTime.Now;
var episode = context
.Episodes
.Where(e => e.TvShowId.Equals(serieID))
.OrderByDescending(e => e.Season).ThenByDescending(e => e.Number)
.FirstOrDefault(e => DbFunctions.CreateDateTime(e.FirstAired.Value.Year, e.FirstAired.Value.Month, e.FirstAired.Value.Day, 0, 0, 0) < DbFunctions.CreateDateTime(date.Year, date.Month, date.Day, 0, 0, 0));
if (episode != null)
{
return(new EpisodeDTO(episode));
}
return(null);
}
}
static void Main(string[] args)
{
Console.WriteLine("start");
var ctx = new MyHotelDbContext();
var dates = ctx.Reservations.Select(x => DbFunctions.CreateDateTime(x.CheckinDate.Year, 1, 1, 0, 0, 0));
foreach (var date in dates)
{
Console.WriteLine("Normal:" + date);
}
var datesDynamic = ctx.Reservations.Select("DbFunctions.CreateDateTime(CheckinDate.Year, 1, 1, 0, 0, 0)");
foreach (var date in datesDynamic)
{
Console.WriteLine("Dynamic:" + date);
}
}
