public static void SelectSync()
{
LogNewLine();
using (_operLogger.BeginScope("{BatchId}", "SelectSync"))
{
var batchSw = LogStarted();
var operSw = new Stopwatch();
using (_operLogger.BeginScope("{OperId}", ".Warmup"))
{
LogStarted(operSw);
//warmup
using (var ctx = new BloggingContext(_dbCtxOpts))
{
ctx.SetDapperMapping <User>();
var q = ctx.Users.Where(o => o.Id > 1 && o.Name1 == "User Insert2").OrderBy(x => x.Id).ThenByDescending(x => x.Name1).Take(2);
var result = ctx.Query(q);
}
LogElapsed(operSw, "ended", "ignore timing");
}
LogNewLine();
batchSw.Restart();
operSw.Restart();
using (var ctx = new BloggingContext(_dbCtxOpts))
{
LogElapsed(operSw, "created context");
using (_operLogger.BeginScope("{OperId}", ".Select1"))
{
LogStarted(operSw);
var q = ctx.Users.Where(o => o.Id > 1 && o.Name1 == "User Insert2").OrderBy(x => x.Id).ThenByDescending(x => x.Name1).Take(2);
var res = ctx.Query(q);
LogElapsed(operSw, "ended", $"count = {res.Count()}");
}
LogNewLine();
using (_operLogger.BeginScope("{OperId}", ".Select12 (with selector)"))
{
LogStarted(operSw);
var q = ctx.Users.Where(o => o.Id > 1).OrderBy(x => x.Id).ThenByDescending(x => x.Name1).Take(2).Select(x => new { x.Id, Name = x.Name1 });
var res = ctx.Query(q);
LogElapsed(operSw, "ended", $"count = {res.Count()}");
}
LogNewLine();
using (_operLogger.BeginScope("{OperId}", ".Select13"))
{
LogStarted(operSw);
var q = ctx.Users.Where(o => o.Id > 1 && o.Name1 == "User Insert2").OrderBy(x => x.Id).ThenByDescending(x => x.Name1).Take(2);
var res = ctx.Query(q);
LogElapsed(operSw, "ended", $"count = {res.Count()}");
}
LogNewLine();
using (_operLogger.BeginScope("{OperId}", ".Select14 (with selector)"))
{
LogStarted(operSw);
var q = ctx.Users.Where(o => o.Id > 1).OrderBy(x => x.Id).ThenByDescending(x => x.Name1).Take(2).Select(x => new { x.Id, Name = x.Name1 });
var res = ctx.Query(q);
LogElapsed(operSw, "ended", $"count = {res.Count()}");
}
operSw.Restart();
}
LogElapsed(operSw, "disposed context");
LogElapsed(batchSw, "ended");
}
}
public static void Select()
{
var q1 = EF.CompileQuery(
(BloggingContext ctx) =>
ctx.Users.Where(o => o.Id > 1 && o.Name1 == "User Insert2")
.OrderBy(x => x.Id).ThenByDescending(x => x.Name1).Take(2));
LogNewLine();
using (_operLogger.BeginScope("{BatchId}", "Select"))
{
var batchSW = LogStarted();
using (var ctx = new BloggingContext(_dbCtxOpts))
{
ctx.SetDapperMapping <User>();
var operSW = new Stopwatch();
using (_operLogger.BeginScope("{OperId}", ".Select1a (compiled)"))
{
LogStarted(operSW);
var res = q1(ctx);
LogElapsed(operSW, "ended", $"count = {res.Count()}");
}
LogNewLine();
using (_operLogger.BeginScope("{OperId}", ".Select1"))
{
LogStarted(operSW);
var q = ctx.Users.Where(o => o.Id > 1 && o.Name1 == "User Insert2")
.OrderBy(x => x.Id).ThenByDescending(x => x.Name1).Take(2);
var res = ctx.Query(q);
LogElapsed(operSW, "ended", $"count = {res.Count()}");
}
LogNewLine();
using (_operLogger.BeginScope("{OperId}", ".Select2 (with selector)"))
{
LogStarted(operSW);
var q = ctx.Users.Where(o => o.Id > 1).OrderBy(x => x.Id).ThenByDescending(x => x.Name1)
.Take(2).Select(x => new { x.Id, Name = x.Name1 });
var res = ctx.Query(q);
LogElapsed(operSW, "ended", $"count = {res.Count()}");
}
LogNewLine();
using (_operLogger.BeginScope("{OperId}", ".Select3"))
{
LogStarted(operSW);
var q = ctx.Blogs.Where(b => b.Name == "Blog2").Join(ctx.Users, o => o.UserId, i => i.Id,
(o, i) => new { Blog = o, User = i });
var res = ctx.Query(q, (Blog b, User u) =>
{
b.User = u;
return(new { Blog = b, User = u });
});
LogElapsed(operSW, "ended", $"count = {res.Count()}");
}
var stateSw = new Stopwatch();
using (_operLogger.BeginScope("{OperId}", ".Select4"))
{
LogNewLine();
LogStarted(operSW);
stateSw.Restart();
var q = from u in ctx.Users
join b in ctx.Blogs on u.Id equals b.UserId into bg
from b in bg.DefaultIfEmpty()
join p in ctx.BlogPosts on b.Id equals p.BlogId into pg
from p in pg.DefaultIfEmpty()
where u.Id == 2
select new
{
User = u,
Blog = b,
Post = p
};
LogElapsed(stateSw, "created query");
var res = ctx.Query(q, (User u, Blog b, BlogPost p) =>
{
b.User = u;
if (p != null)
{
p.Blog = b;
}
return(new
{
User = u,
Blog = b,
Post = p
});
});
stateSw.Restart();
var users = res.GroupBy(ug => ug.User.Id, (key, uItems) =>
{
var item = uItems.First();
item.User.Blogs = uItems.GroupBy(bg => bg.Blog?.Id, (bid, bItems) =>
{
var bitem = bItems.FirstOrDefault();
if (bitem == null)
{
return(null);
}
bitem.Blog.BlogPosts = bItems.Select(x => x.Post).
Where(x => x != null).ToArray();
return(bitem.Blog);
}).Where(x => x != null).ToArray();
return(item.User);
}).ToArray();
LogElapsed(stateSw, "processed result");
LogElapsed(operSW, "ended", $"count = {res.Count()}");
}
}
LogElapsed(batchSW, "ended");
}
}
