public static void InitTables()
{
Database.SetInitializer(new DropCreateDatabaseTables());
using (var context = new ApplicationDbContext())
{
context.Customers.AddRange(
new List<Customer>
{
new Customer { Id = 1, Name = @"ABC" },
new Customer { Id = 2, Name = @"DEF" }
});
context.TransactionFlows.AddRange(
new List<TransactionFlow>
{
new TransactionFlow { Id = 1, Date = new DateTime(2000, 1, 1), TransactionId = 1, Status = "Submit" },
new TransactionFlow { Id = 2, Date = new DateTime(2000, 1, 1), TransactionId = 2, Status = "Submit" },
new TransactionFlow { Id = 3, Date = new DateTime(2000, 2, 1), TransactionId = 1, Status = "Process" },
new TransactionFlow { Id = 4, Date = new DateTime(2000, 2, 1), TransactionId = 2, Status = "Process" },
new TransactionFlow { Id = 5, Date = new DateTime(2000, 3, 1), TransactionId = 2, Status = "Finish" }
});
context.TranscationStates.AddRange(
new List<TranscationState>
{
new TranscationState { Level = 1, State = "Submit" },
new TranscationState { Level = 3, State = "Process" },
new TranscationState { Level = 9, State = "Finish" }
});
context.SaveChanges();
}
}
static void Main()
{
//InitDb.InitTables();
var ctx = new ApplicationDbContext();
//ctx.TransactionFlows
// .Select(s =>
// new
// {
// s.TransactionId,
// s.Status,
// StateLevel = ctx.TranscationStates.FirstOrDefault(p => p.State.Equals(s.Status)).Level
// })
// .GroupBy(ks =>
// ks.TransactionId)
// .Select(sg =>
// new
// {
// sg,
// MaxState = sg.Max(s => s.StateLevel)
// })
// .SelectMany(
// sc => sc.sg,
// (k, g) => new { k, g })
// .Where(p =>
// p.g.StateLevel.Equals(p.k.MaxState))
// .Select(s =>
// new
// {
// Id = s.g.TransactionId,
// Customer = ctx.Customers.FirstOrDefault(p => p.Id.Equals(s.g.TransactionId)).Name,
// s.g.Status
// })
// .ToList()
// .ForEach(a =>
// Console.WriteLine(
// @"Id = {0}, Customer = {1}, Status = {2}",
// a.Id,
// a.Customer,
// a.Status));
//(from g in
// (from tf in ctx.TransactionFlows
// select new
// {
// tf.TransactionId,
// tf.Status,
// StateLevel = ctx.TranscationStates.FirstOrDefault(p => p.State.Equals(tf.Status)).Level
// })
//group g by g.TransactionId into grp
//let maxState = grp.Max(s => s.StateLevel)
//from p in grp
//where p.StateLevel.Equals(maxState)
//select new
//{
// Id = p.TransactionId,
// Customer = ctx.Customers.FirstOrDefault(c => c.Id.Equals(p.TransactionId)).Name,
// p.Status
//})
//.ToList()
//.ForEach(a =>
// Console.WriteLine(
// @"Id = {0}, Customer = {1}, Status = {2}",
// a.Id,
// a.Customer,
// a.Status));
//Debug.WriteLine(
// (from g in
// (from tf in ctx.TransactionFlows
// select new
// {
// tf.TransactionId,
// tf.Status,
// StateLevel = ctx.TranscationStates.FirstOrDefault(p => p.State.Equals(tf.Status)).Level
// })
// group g by g.TransactionId
// into grp
// let maxState = grp.Max(s => s.StateLevel)
// from p in grp
// where p.StateLevel.Equals(maxState)
// select new
// {
// Id = p.TransactionId,
// Customer = ctx.Customers.FirstOrDefault(c => c.Id.Equals(p.TransactionId)).Name,
// p.Status
// })
// .ToString());
//Debug.WriteLine(
// ctx.TransactionFlows
// .Select(s =>
// new
// {
// s.TransactionId,
// s.Status,
// StateLevel = ctx.TranscationStates.FirstOrDefault(p => p.State.Equals(s.Status)).Level
// })
// .GroupBy(ks =>
// ks.TransactionId)
// .Select(sg =>
// new
// {
// sg,
// MaxState = sg.Max(s => s.StateLevel)
// })
// .SelectMany(
// sc => sc.sg,
// (k, g) => new { k, g })
// .Where(p =>
// p.g.StateLevel.Equals(p.k.MaxState))
// .Select(s =>
// new
// {
// Id = s.g.TransactionId,
// Customer = ctx.Customers.FirstOrDefault(p => p.Id.Equals(s.g.TransactionId)).Name,
// s.g.Status
// })
// .ToString());
Debug.WriteLine(
ctx.TransactionFlows
.Select(s =>
new
{
s.TransactionId,
StateLevel = ctx.TranscationStates.FirstOrDefault(p => p.State.Equals(s.Status)).Level
})
.GroupBy(
ks => ks.TransactionId,
(k, g) =>
new
{
Id = k,
Max = g.Max(s => s.StateLevel)
})
.Select(s =>
new
{
s.Id,
Customer = ctx.Customers.FirstOrDefault(p => p.Id.Equals(s.Id)).Name,
Status = ctx.TranscationStates.FirstOrDefault(p => p.Level.Equals(s.Max)).State
})
.ToString());
}
