private static void ExplicitLoading()
{
using (var context = new QueryingContext())
{
context.Database.Initialize(true);
context.Database.Log = Log;
foreach (var order in context.Orders)
{
var orderEntry = context.Entry(order);
if (!orderEntry.Collection(o => o.OrderLines).IsLoaded)
{
Console.WriteLine($"Loading order lines for order #{order.Id}...");
orderEntry.Collection(o => o.OrderLines).Load();
}
foreach (var orderLine in order.OrderLines)
{
var orderLineEntry = context.Entry(orderLine);
if (!orderLineEntry.Reference(ol => ol.Product).IsLoaded)
{
Console.WriteLine($"Loading product for order line #{orderLine.Id}...");
orderLineEntry.Reference(ol => ol.Product).Load();
Console.WriteLine($"Product {orderLine.Product.Name} loaded");
}
Console.WriteLine($"Order line #{orderLine.Id} of order {order.Id} has the product {orderLine.Product.Name}");
}
}
context.SaveChanges();
}
}
private static void DynamicSql()
{
using (var context = new QueryingContext())
{
context.Database.Initialize(true);
context.Database.Log = Log;
var sql = new SqlSentenceBuilder();
dynamic parameters = new ExpandoObject();
sql.AddSelect("SUM(Units) AS Units, Name");
sql.AddFrom("OrderLines INNER JOIN Products ON OrderLines.ProductId = Products.Id");
sql.AddGroupBy("Name");
const bool filter = true;
if (filter)
{
sql.AddWhere("OrderId = @OrderId", "myFilter");
parameters.OrderId = 1;
//sql.RemoveWhere("myFilter");
//((IDictionary<string, object>)parameters).Remove("OrderId");
}
sql.AddOrderBy("Name");
sql.EnablePaginationWithCte(1, 10);
Console.WriteLine(sql.Build());
var data = context.Database.Connection.Query <ComplexSqlType>(sql.Build(), (ExpandoObject)parameters);
foreach (var item in data)
{
Console.WriteLine(item);
}
}
}
private static void AsQueryable()
{
using (var context = new QueryingContext())
{
context.Database.Initialize(true);
context.Database.Log = Log;
var q = context.Products
.Select(p => p.Name.ToUpper())
.ToLower();
foreach (var item in q)
{
Console.WriteLine(item);
}
var q2 = context.Products
.Select(p => p.Name.ToUpper())
.AsEnumerable()
.Select(p => Regex.Replace(p, "[AEIOU]", "#"))
.AsQueryable()
.ToLower();
foreach (var item in q2)
{
Console.WriteLine(item);
}
}
}
private static void CreateOrder()
{
using (var context = new QueryingContext())
{
context.Orders.Add(new Order());
context.SaveChanges();
}
}
private static void EagerLoading()
{
using (var context = new QueryingContext())
{
context.Database.Initialize(true);
context.Database.Log = Log;
var orders = context.Orders
.Include(p => p.OrderLines)
.Include(p => p.OrderLines.Select(t => t.Product));
foreach (var order in orders)
{
Console.WriteLine($"#{order.Id} has {order.OrderLines.Count} lines");
Console.WriteLine($@"#{order.Id} has {order.OrderLines.Select(p => p.Product.Name).Distinct().Count()} different products");
}
}
}
private static void LazyLoading()
{
using (var context = new QueryingContext())
{
context.Database.Initialize(true);
context.Database.Log = Log;
var orders = context.Orders;
var firstOrder = orders.First();
var proxyType = firstOrder.GetType().Name;
var type = ObjectContext.GetObjectType(firstOrder.GetType()).Name;
Console.WriteLine($"{proxyType} / {type}");
foreach (var order in orders)
{
Console.WriteLine($"#{order.Id} has {order.OrderLines.Count} lines");
}
}
}
private static void DeferredExecution()
{
using (var context = new QueryingContext())
{
context.Database.Initialize(true);
context.Database.Log = Log;
var orders = context.Orders;
foreach (var order in orders)
{
Console.WriteLine($"#{order.Id}");
}
CreateOrder();
foreach (var order in orders)
{
Console.WriteLine($"#{order.Id}");
}
}
}
private static void ComplexQuery()
{
using (var context = new QueryingContext())
{
context.Database.Initialize(true);
context.Database.Log = Log;
var q = from o in context.Orders.GetOrdersGreaterThanYear2000()
.Where(IsOrderGreaterThanYear2000())
from l in o.OrderLines
where o.Id > 1
select new
{
o,
l,
p = l.Product
};
const bool filter = true;
if (filter)
{
q = from r in q
where r.l.Units > 0
select r;
}
var q2 = from r in q
group r by r.p.Name
into cont
select new
{
Product = cont.Key,
Units = cont.Sum(p => p.l.Units)
};
foreach (var item in q2)
{
Console.WriteLine($"{item.Product}, {item.Units}");
}
}
}
private static void Dapper()
{
using (var context = new QueryingContext())
{
context.Database.Initialize(true);
context.Database.Log = Log;
var data = context.Database.SqlQuery <OrderLineSummary>(@"
SELECT Id, Units
FROM OrderLines
WHERE OrderId = {0}", 1);
foreach (var item in data)
{
Console.WriteLine(item);
}
var data2 = context.Database.Connection.Query <OrderLineSummary, ProductSummary, OrderLineSummary>(
sql: @"SELECT OrderLines.Id, Units, Products.Id AS ProductId, Name
FROM OrderLines INNER JOIN Products ON OrderLines.ProductId = Products.Id
WHERE OrderId = @OrderId",
map: (ol, p) =>
{
ol.Product = new ProductSummary
{
Name = p.Name
};
return(ol);
},
param: new { OrderId = 1 },
splitOn: "ProductId");
foreach (var item in data2)
{
Console.WriteLine(item);
}
}
}
