public void Execute(RestfulServiceDatabaseEntities dbContext)
{
var result = from c in dbContext.Customer
join p in dbContext.Purchase on c.ID equals p.Customer_id
into customerPurchases
select new { c, customerPurchases };
}
public void Execute(RestfulServiceDatabaseEntities dbContext)
{
var result = from c in dbContext.Customer
from p in c.Purchase
where p.Price > 5
select new { c.Name, p.Price };
}
static void Main(string[] args)
{
var dbContext = new RestfulServiceDatabaseEntities();
var query = new LookUpp();
query.Execute(dbContext);
}
/// <summary>
/// Those two queries return same results
/// </summary>
/// <param name="dbContext"></param>
public void Execute(RestfulServiceDatabaseEntities dbContext)
{
var resultQuery = from p in dbContext.Purchase
orderby p.Id, p.Price
select p;
var resultFluent = dbContext.Purchase.OrderBy(x => x.Id).ThenBy(x => x.Price);
}
public void Execute(RestfulServiceDatabaseEntities dbContext)
{
var customers = dbContext.Customer;
var purchases = dbContext.Purchase;
var result = from c in customers join p in purchases on
c.ID equals p.Customer_id
into custPurchases
select new { c.Name, custPurchases };
}
public void Execute(RestfulServiceDatabaseEntities dbContext)
{
var result = from c in dbContext.Customer
from p in c.Purchase.DefaultIfEmpty()
select new
{
c.Name,
Price = p == null ? (decimal?)null : p.Price,
Id = p == null ? (int?)null : p.Id
};
}
public void Execute(RestfulServiceDatabaseEntities dbContext)
{
var result = from c in dbContext.Customer
join p in dbContext.Purchase on c.ID equals p.Customer_id into custPurchases
from cp in custPurchases.DefaultIfEmpty()
select new
{
CustName = c.Name,
Price = cp == null ? (decimal?)null : cp.Price
};
}
/// <summary>
/// Using lookup and selectmany to implement outer join
/// </summary>
public void OuterJoinWithLookup(ILookup <int?, Purchase> purchaseLookup, RestfulServiceDatabaseEntities dbContext)
{
var result = from c in dbContext.Customer
from p in purchaseLookup[c.ID].DefaultIfEmpty()
select new {
c.Name,
TheId = p == null ? (int?)null : p.Id,
ThePrice = p == null ? (decimal?)null : p.Price,
};
var ff = result.ToArray();
}
public void Execute(RestfulServiceDatabaseEntities dbContext)
{
IEnumerable <string> query = from c in dbContext.Customer
join p in dbContext.Purchase on new { K1 = c.ID, K2 = c.ID } equals new { K1 = (int)p.Customer_id, K2 = (int)p.Customer_id }
select c.Name + " kupił za kwotę " + p.Price + " w transakcji o id " + p.Id;
foreach (var s in query)
{
Console.WriteLine(s);
}
}
public static IEnumerable <Customer> GetCustomers()
{
var dbContext = new RestfulServiceDatabaseEntities();
return(from c in dbContext.Customer select c);
}
/// <summary>
/// Implementation of join using lookup plus selectmany
/// </summary>
public void SelectManyWithLookup(ILookup <int?, Purchase> purchaseLookup, RestfulServiceDatabaseEntities dbContext)
{
var result = from c in dbContext.Customer
from p in purchaseLookup[c.ID]
select new { c.Name, p.Id, p.Price };
}
public void Execute(RestfulServiceDatabaseEntities dbContext)
{
var purchLookup = dbContext.Purchase.ToLookup(p => p.Customer_id, p => p);
SelectManyWithLookup(purchLookup, dbContext);
}
