private static void RunQuery2()
{
var sw = Stopwatch.StartNew();
var cms = IgniteFactory.GetCache <ContractMonth>().AsCacheQueryable(new QueryOptions {
EnableDistributedJoins = true
});
var ms = IgniteFactory.GetCache <Month>().AsCacheQueryable(new QueryOptions {
EnableDistributedJoins = true
});
var q = from cm in cms
from m in ms
//where cm.Value.MonthID == m.Value.ID
//where m.Value.ActualYear == 2016
where m.Value.ID == cm.Value.MonthID && m.Value.ActualYear == 2016
//where cm.Value.MonthID == 26
select cm.Value;
Console.WriteLine($"Executing SQL: {(q as ICacheQueryable).GetFieldsQuery().Sql}");
var cnt = q.Count();
Console.WriteLine($"{cnt} records in {sw.Elapsed}");
var arr = q.Take(10).ToArray();
}
private static void RunQueryPPRV()
{
var sw = Stopwatch.StartNew();
var ics = IgniteFactory.GetCache <Contract>().AsCacheQueryable();
var pps = IgniteFactory.GetCache <PaymentPlan>().AsCacheQueryable();
var icms = IgniteFactory.GetCache <ContractMonth>().AsCacheQueryable();
var ims = IgniteFactory.GetCache <Month>().AsCacheQueryable();
var q = from ic in ics
from icm in icms
from pp in pps
from im in ims
where icm.Value.ContractID == ic.Value.ID && pp.Value.ContractMonthID == icm.Value.ID && icm.Value.MonthID == im.Value.ID &&
(ic.Value.ContractStatusID != 7 || im.Value.ActualDate < ic.Value.AnnulDate) &&
pp.Value.IsReassigned == false && ic.Value.ContractStatusID != 5
select new { ID = pp.Value.ID, ContractID = ic.Value.ID, ContractStatusID = ic.Value.ContractStatusID, MonthID = icm.Value.MonthID, ContractorID = ic.Value.ContractorID, PaymentPlanID = pp.Value.ID, PaymentPlanValue = pp.Value.Value, PaymentPlanSkuID = pp.Value.SkuId, PaymentPlanSkuQuantity = pp.Value.SkuQuantity, ActualYear = im.Value.ActualYear };
Console.WriteLine($"Executing SQL: {(q as ICacheQueryable).GetFieldsQuery().Sql}");
//var cnt = q.Count();
var arr = q.ToArray();
var cnt = arr.Length;
Console.WriteLine($"{cnt} records in {sw.Elapsed}");
}
private static void RunQuery()
{
var sw = Stopwatch.StartNew();
//var cfs = GetCache<ConditionFact>().AsCacheQueryable(new QueryOptions { EnableDistributedJoins = true });
//var cs = GetCache<Condition>().AsCacheQueryable(new QueryOptions { EnableDistributedJoins = true });
//var q = from cf in cfs
// from c in cs
// where c.Value.ID == cf.Value.ConditionID
// select c.Value.Name;
var q = from c in IgniteFactory.GetCache <PaymentPlan>().AsCacheQueryable()
select c.Value;
Console.WriteLine($"Executing SQL: {(q as ICacheQueryable).GetFieldsQuery().Sql}");
var cnt = q.Count();
Console.WriteLine($"{cnt} records in {sw.Elapsed}");
var arr = q.Take(10).ToArray();
}
private static void RunQueryGetBudgetDataSections()
{
var sw = Stopwatch.StartNew();
var user = new User {
ID = 12021
};
var userId = 12021;
var regionsToRole = IgniteFactory.GetCache <RegionToRole>().AsCacheQueryable();
var users = IgniteFactory.GetCache <User>().AsCacheQueryable();
var currentUserRegionsToRole = users
.Where(w => w.Value.ID == userId && !w.Value.IsDeleted)
.Join(regionsToRole, u => u.Value.ID, r => r.Value.RoleID, (userr, role) => userr)
//.SelectMany(sm => sm.Value.RegionToRole);
.ToList()
.SelectMany(sm => sm.Value.RegionToRole);
var q1 = currentUserRegionsToRole.ToList();
var childRegions = currentUserRegionsToRole
.SelectMany(rtr => rtr.Region.RegionExpand1)
.Select(re => re.Region);
var q2 = childRegions.ToList();
var regionsAsm = childRegions
.Where(w => w.RegionToRole.Any(a => a.RoleID == 7));
var q3 = regionsAsm.ToList();
var allInvestTitle = IgniteFactory.GetCache <InvestTitle>().AsCacheQueryable();
var isFutureYear = false;
var distributorToRegionAsmBindings = !isFutureYear
? regionsAsm.SelectMany(s => s.DistributorToRegionAsmBinding)
: regionsAsm.SelectMany(s => s.DistributorToRegionAsmBinding.Where(b => b.IsActual));
var q4 = distributorToRegionAsmBindings.ToList();
var resultAllBudgets = distributorToRegionAsmBindings
.Select(d => new { Distributor = d.Distributor, RegionAsm = d.Region, d })
.SelectMany(dr => allInvestTitle, (dr, t) => new { dr.Distributor, dr.RegionAsm, InvestTitle = t, dr.d });
var q5 = resultAllBudgets.ToList();
var allBudgets = resultAllBudgets
.Select(b => new
{
AsmRegionId = b.RegionAsm.ID,
AsmRegionName = b.RegionAsm.Name,
DistributorId = b.Distributor.ID,
DistributorName = b.Distributor.Name,
RsmRegionId = b.RegionAsm.RegionExpand
.Where(w => w.Region1.RegionToRole.Any(a => a.RoleID == 5))
.Select(ss => ss.ParentRegionID)
.FirstOrDefault(),
RsmRegionName = b.RegionAsm.RegionExpand
.Where(w => w.Region1.RegionToRole.Any(a => a.RoleID == 5))
.Select(ss => ss.Region1.Name)
.FirstOrDefault(),
InvestTitleId = b.InvestTitle.Value.ID,
InvestTitleName = b.InvestTitle.Value.Name,
});
var q6 = allBudgets.ToList();
var q = allBudgets.GroupBy(gb => new { gb.RsmRegionId, gb.RsmRegionName })
.Select(s => new { RegionId = s.Key.RsmRegionId, RegionName = s.Key.RsmRegionName, Count = s.Count() })
.ToList();
Console.WriteLine($"Executing SQL: {(q as ICacheQueryable).GetFieldsQuery().Sql}");
//var cnt = q.Count();
var arr = q.ToArray();
var cnt = arr.Length;
Console.WriteLine($"{cnt} records in {sw.Elapsed}");
}