Try .NET /elinq/
Powered by Try .NET

SQL Server Subquery

sales orders of the customers who locate in New York:

var city = "New York";

var query = DbContext.Orders
    .Query((Orders orders) =>
    {
        var r = SELECT(orders);
        FROM(orders);
        WHERE(SubQuery((Customers customers) =>
        {
            var id = SELECT(customers.CustomerId);
            FROM(customers);
            WHERE(customers.City == city);
            return id.AsCollection();
        }).Contains(orders.CustomerId.GetValueOrDefault()));

        return r;
    })
    .OrderByDescending(so => so.OrderDate)
    .Include(so => so.Customer);

foreach (var order in query.Take(3))
    Console.WriteLine((order.OrderId, order.OrderDate, order.Customer.CustomerId));

When subquery SELECTs a single column (or a tuple), we may return it AsCollection() or AsSingle(). The correct type propagates out. Thus the return type of SubQuery() method above is ICollection<int?>.

For clarity let's assign the result of the subquery to a local variable (of course the produced SQL is same in either case):

var city = "New York";

var query = DbContext.Orders
    .Query((Orders orders) =>
    {
        var customersInCity = SubQuery((Customers customers) =>
        {
            var id = SELECT(customers.CustomerId);
            FROM(customers);
            WHERE(customers.City == city);
            return id.AsCollection();
        });

        var r = SELECT(orders);
        FROM(orders);
        WHERE(customersInCity.Contains(orders.CustomerId.GetValueOrDefault()));

        return r;
    })
    .OrderByDescending(so => so.OrderDate)
    .Include(so => so.Customer);

foreach (var order in query.Take(3))
    Console.WriteLine((order.OrderId, order.OrderDate, order.Customer.CustomerId));

Nesting subquery

var query = DbContext.Products
                .Query((Products product) =>
                {
                    var brands = SubQuery((Brands brand) =>
                    {
                        var id = SELECT(brand.BrandId);
                        FROM(brand);
                        WHERE(brand.BrandName == "Strider" || brand.BrandName == "Trek");
                        return id.AsCollection();
                    });

                    var avgPrice = SubQuery((Products product) =>
                    {
                        var avg = SELECT(AVG(product.ListPrice));
                        FROM(product);
                        WHERE(brands.Contains(product.BrandId));
                        return avg.AsSingle();
                    });

                    var r = SELECT(product);
                    FROM(product);
                    WHERE(product.ListPrice > avgPrice);

                    return r;
                })
                .OrderBy(p => p.ListPrice);

foreach (var product in query.Take(3))
    Console.WriteLine((product.ProductName, product.ListPrice));

SQL Server subquery is used in place of an expression

var query = DbContext.Set<OrderMaxListPrice>()
                .Query((Orders orders, OrderMaxListPrice alias) =>
                {
                    var maxListPrice = SubQuery((OrderItems items) =>
                    {
                        var max = SELECT(MAX(items.ListPrice));
                        FROM(items);
                        WHERE(items.OrderId == orders.OrderId);
                        return max.AsSingle();
                    });

                    var r = SELECT<OrderMaxListPrice>(orders.OrderId.@as(alias.Order.OrderId), maxListPrice.@as(alias.MaxListPrice));
                    FROM(orders);
                    return r;
                })
                .OrderByDescending(o => o.Order.OrderDate)
                .Include(o => o.Order);

foreach (var order in query.Take(3))
    Console.WriteLine((order.Order.OrderId, order.Order.OrderDate, order.MaxListPrice));

SQL Server subquery is used with IN operator

In fact we already covered IN operator in the very first example above.

SQL Server subquery is used with ANY operator

var query = DbContext.Products
                .Query((Products product) =>
                {
                    var avgBrandPrices = SubQuery((Products product) =>
                    {
                        var avg = SELECT(AVG(product.ListPrice));
                        FROM(product);
                        GROUP(BY(product.BrandId));
                        return avg.AsCollection();
                    });

                    var r = SELECT(product);
                    FROM(product);
                    WHERE(product.ListPrice >= ANY(avgBrandPrices));

                    return r;
                });

foreach (var product in query.Take(3))
    Console.WriteLine((product.ProductName, product.ListPrice));

SQL Server subquery is used with ALL operator

Just replace ANY with ALL above and run!

customers who bought products in 2017:
var query = DbContext.Customers
                .Query((Customers customer) =>
                {
                    var orders2017 = SubQuery((Orders order) =>
                    {
                        var count = SELECT(order.CustomerId);
                        FROM(order);
                        WHERE(order.Customer == customer && YEAR(order.OrderDate) == 2017);
                        return count.AsCollection();
                    });

                    var r = SELECT(customer);
                    FROM(customer);
                    WHERE(EXISTS(orders2017));
                    return r;
                })
                .OrderBy(c => c.FirstName)
                .ThenBy(c => c.LastName);

foreach (var customer in query.Take(3))
    Console.WriteLine((customer.CustomerId, customer.FirstName, customer.LastName, customer.City));
customers who did not buy any products in 2017:

ELINQ maps SQL's EXISTS operator to Any():

var query = DbContext.Customers
                .Query((Customers customer) =>
                {
                    var orders2017 = SubQuery((Orders order) =>
                    {
                        var count = SELECT(order.CustomerId);
                        FROM(order);
                        WHERE(order.Customer == customer && YEAR(order.OrderDate) == 2017);
                        return count.AsCollection();
                    });

                    var r = SELECT(customer);
                    FROM(customer);
                    WHERE(!orders2017.Any());
                    return r;
                })
                .OrderBy(c => c.FirstName)
                .ThenBy(c => c.LastName);

foreach (var customer in query.Take(3))
    Console.WriteLine((customer.CustomerId, customer.FirstName, customer.LastName, customer.City));

SQL Server subquery in the FROM clause

var query = DbContext.Set<Scalar<int>>()
                .Query((Scalar<int> alias) =>
                {
                    var orderCountByStaff = SubQuery((Orders order) =>
                    {
                        var count = SELECT<Scalar<int>>(COUNT().@as(alias.Value));
                        FROM(order);
                        GROUP(BY(order.StaffId));
                        return count;
                    });

                    var r = SELECT<Scalar<int>>(AVG(orderCountByStaff.Value).@as(alias.Value));
                    FROM(orderCountByStaff);
                    return r;
                });

Console.WriteLine(query.Single().Value);

Since ELINQ supports variables, this very powerfull feature does not lead to complex nesting.

< BACK | HOME