LINQ SQL-like Syntax Examples #CSharp

gistfile1.st

var query = from с шт context.Customers where c.CustomerId == 1 select c;

/*Example of INNER JOIN in LINQ SQL-like syntax*/
var transactions = from с in сcontext.Customers
    join o in Context.Orders on c.CustomerId equals o.CustomerId
    join op in Context.OrderProducts on o.OrderId equals op.OrderId
    join p in Context.Products on op.ProductId equals p.ProductId
    select new
    {
        CustomerName = c.CustomerName,
        ProductName = p.ProductName,
        ProductCount = op.Count
    };

/*Example of LEFT JOIN in LINQ SQL-like syntax*/
var transactions = from с in сcontext.Customers
    join o in Context.Orders on c.CustomerId equals o.CustomerId into go
    from oJoined in go.DefaultIfEmpty()

    join op in Context.OrderProducts on oJoined.OrderId equals op.OrderId into gop
    from opJoined in gop.DefaultIfEmpty()

    join p in Context.Products on opJoined.ProductId equals p.ProductId into gp
    from pJoined in gp.DefaultIfEmpty()

    select new
    {
        CustomerName = c.CustomerName,
        ProductName = p.ProductName,
        ProductCount = opJoined == null?0:op.Count
    };

/*Example of Grouping & Aggregation in LINQ SQL-like syntax*/
var transactions = from с in сcontext.Customers
            join o in Context.Orders on c.CustomerId equals o.CustomerId into go
            from oJoined in go.DefaultIfEmpty()

            join op in Context.OrderProducts on oJoined.OrderId equals op.OrderId into gop
            from opJoined in gop.DefaultIfEmpty()

            join p in Context.Products on opJoined.ProductId equals p.ProductId into gp
            from pJoined in gp.DefaultIfEmpty()

            group new {c, opJoined, pJoined} by new {c.CustomerId, c.CustomerName}
            into g
            select new
            {
                CustomerName = g.Key.CustomerName,
                CustomerSum = g.Sum(t => (t.opJoined == null ? 0 : t.opJoined.Count) * (t.pJoined == null ? 0 : t.pJoined.ProductPrice))
            };