Try .NET /elinq/
Powered by Try .NET

SQL Server WHERE

A) Finding rows by using a simple equality

var query = DbContext.Products.Query((Products products) =>
{
    var result = SELECT(products);
    FROM(products);
    WHERE(products.CategoryId == 1);

    return result;
})
                .OrderByDescending(p => p.ListPrice);

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

B) Finding rows that meet two conditions

var query = DbContext.Products.Query((Products products) =>
{
    var result = SELECT(products);
    FROM(products);
    WHERE(products.CategoryId == 1 && products.ModelYear == 2018);

    return result;
}).OrderByDescending(p => p.ListPrice);

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

C) Finding rows by using a comparison operator

var query = DbContext.Products.Query((Products products) =>
{
    var result = SELECT(products);
    FROM(products);
    WHERE(products.ListPrice > 300 && products.ModelYear == 2018);

    return result;
})
                .OrderByDescending(p => p.ListPrice);

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

D) Finding rows that meet any of two conditions

var query = DbContext.Products.Query((Products products) =>
{
    var result = SELECT(products);
    FROM(products);
    WHERE(products.ListPrice > 3000 || products.ModelYear == 2018);

    return result;
})
                .OrderByDescending(p => p.ListPrice);

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

E) Finding rows with the value between two values

Let's start using parameters:

var low = 1899.00M;
var high = 1999.99M;

var query = DbContext.Products.Query((Products products) =>
{
    var result = SELECT(products);
    FROM(products);
    WHERE(BETWEEN(products.ListPrice, low, high));

    return result;
})
    .OrderByDescending(p => p.ListPrice);

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

F) Finding rows that have a value in a list of values

ELINQ maps IList.Contains() method to SQL IN operator:

var values = new[] { 299.99M, 369.99M, 489.99M };

var query = DbContext.Products.Query((Products products) =>
{
    var result = SELECT(products);
    FROM(products);
    WHERE(values.Contains(products.ListPrice));

    return result;
})
    .OrderByDescending(p => p.ListPrice);

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

G) Finding rows whose values contain a string

And String.Contains() method to SQL LIKE operator:

var letters = "Cruiser";

var query = DbContext.Products.Query((Products products) =>
{
    var result = SELECT(products);
    FROM(products);
    WHERE(products.ProductName.Contains(letters));

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

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

< BACK | HOME