Try .NET /elinq/
Powered by Try .NET

SQL Server SELECT

ELINQ extends DbSet with a Query method. As any LINQ method, it accepts a Lambda. The Lambda parameters are the entities we want to participate in the query and return value is the result of SELECT invocation:

A) SQL Server SELECT – retrieve some columns of a table

Retrieving some columns is not a common case in EF and requires declaration of the dedicated keyless entity (FullName).

var query = DbContext.Set<FullName>()
                .Query((Customers customer, FullName alias) =>
                {
                    var result = SELECT<FullName>(customer.FirstName.@as(alias.FirstName), customer.LastName.@as(alias.LastName));
                    FROM(customer);

                    return result;
                });

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

Another option is to use LINQ for the anonymous top level projection. See how easy is to mix ELINQ and LINQ:

var query = from customers in DbContext.Customers.Query((Customers customer) =>
{
    var result = SELECT(customer);
    FROM(customer);

    return result;
})
            select new { customers.FirstName, customers.LastName };

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

B) SQL Server SELECT – retrieve all columns from a table

var query = DbContext.Customers.Query((Customers customer) =>
{
    var result = SELECT(customer);
    FROM(customer);

    return result;
});

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

C) SQL Server SELECT – sort the result set

var state = "CA";

var query = DbContext.Customers.Query((Customers customer) =>
{
    var result = SELECT(customer);
    FROM(customer);
    WHERE(customer.State == state);

    return result;
})
    .OrderBy(c => c.FirstName);

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

D) SQL Server SELECT – group rows into groups example

var state = "CA";

var query = DbContext.Set<CityCount>()
    .Query((Customers customer, CityCount cityCount) =>
    {
        var count = COUNT();
        var result = SELECT<CityCount>(customer.City.@as(cityCount.City), count.@as(cityCount.Count));
        FROM(customer);
        WHERE(customer.State == state);
        GROUP(BY(customer.City));

        return result;
    })
    .OrderBy(cc => cc.City);

foreach (var cityCount in query.Take(3))
    Console.WriteLine((cityCount.City, cityCount.Count));

E) SQL Server SELECT – filter groups example

var state = "CA";
var minCount = 10;

var query = DbContext.Set<CityCount>()
    .Query((Customers customer, CityCount cityCount) =>
    {
        var count = COUNT();
        var result = SELECT<CityCount>(customer.City.@as(cityCount.City), count.@as(cityCount.Count));
        FROM(customer);
        WHERE(customer.State == state);
        GROUP(BY(customer.City));
        HAVING(count > minCount);

        return result;
    })
    .OrderBy(cc => cc.City);

foreach (var cityCount in query)
    Console.WriteLine((cityCount.City, cityCount.Count));

< BACK | HOME