[TOC]
SELECT
The SELECT statement is used to select data from a table and is one of the basic operations of SQL.
The result of a SELECT query is stored in a result table (referred to as a result set).
SQL SELECT Syntax
To query data from a specific column:
SELECT [Column Name] FROM [Table Name]
To query data from all columns:
SELECT * FROM [Table Name]
SQL
To query all data:
SELECT * FROM categories
To query one or more columns:
SELECT category_id, category_name FROM categories
Query Expression
var list = from categories in context.Categories
select new
{
categories.CategoryId,
categories.CategoryName
};
var lists = from categories in context.Categories
select categories;
Query Statement
var list = context.Categories.Select(categories =>
new
{
categories.CategoryId,
categories.CategoryName
});
var lists = context.Categories.Select(categories => categories);
SQL SELECT DISTINCT Statement
DISTINCT can remove completely identical items from the result set. Only when every piece of data in every column is identical can it be considered "completely identical".
You can add identical entries (excluding the primary key) in the categories table for testing.
SQL
SELECT DISTINCT category_id, category_name FROM categories
Query Expression
var dislist = list.Distinct();
var dislist2 = (from categories in context.Categories
select new
{
categories.CategoryId,
categories.CategoryName
}).Distinct();
Query Statement
var dislist = list.Distinct();
var dislist2 = context.Categories.Distinct().Select(categories =>
new
{
categories.CategoryId,
categories.CategoryName
});
WHERE and Operators
Used for conditional filtering.
| Operator | Description |
| :------- | :------------ |
| = | Equal to |
| <> | Not equal to |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
| BETWEEN | Within a range |
| LIKE | Search for a pattern |
Note: In some versions of SQL, the operator <> can be written as !=.
BETWEEN and Operators
BETWEEN ... AND or BETWEEN ... OR
In C#, you can use && or || to represent it.
SQL
SELECT [Column Name] FROM [Table Name] WHERE [Column] [Operator] [Value]
SELECT
category_id,
category_name
FROM
categories
WHERE
category_id BETWEEN 1 AND 5
Query Expression
var list3 = from categories in context.Categories
where categories.CategoryId > 1 && categories.CategoryId < 5
select categories;
Query Statement
var list3 = context.Categories.Where(x => x.CategoryId > 1 && x.CategoryId < 5);
LIKE and Wildcards
In SQL, you can use the following wildcards:
| Wildcard | Description |
| :-------------------- | :------------------------------- |
| % | Replaces one or more characters |
| _ | Replaces a single character |
| [charlist] | Any single character in the list |
| [^charlist] or [!charlist] | Any single character not in the list |
SQL
SELECT * FROM categories WHERE category_name like 'B%'
Query Expression
var list4 = from categories in context.Categories
where categories.CategoryName.StartsWith("B")
select categories;
You cannot use % directly.
Query Statement
var list4 = context.Categories.Where(x => x.CategoryName.StartsWith("B"));
Linq only has StartsWith, EndsWith, and Contains; there are no other wildcards.
However, EF provides EF.Functions.Like() for wildcard operations.
For example:
var list5 = from categories in context.Categories
where EF.Functions.Like(categories.CategoryName, "B_")
select categories;
var list5 = context.Categories.Where(x => EF.Functions.Like(x.CategoryName, "B_"));
For more wildcard operations, please refer to EF.Functions.Like().
ORDER BY Sorting
SQL
SELECT * FROM categories ORDER BY category_id
C#
var list6 = (from categories in context.Categories
select categories).OrderBy(c => c.CategoryId);
var list7 = from categories in context.Categories
orderby categories.CategoryId
select categories;
var list6 = context.Categories.OrderBy(x => x.CategoryId).ToList();
var list7 = context.Categories.ToList().OrderBy(x => x.CategoryId);
TOP
PostgreSQL does not support TOP, and you can use OFFSET and LIMIT instead.
SELECT select_list
FROM table_expression
[ ORDER BY ... ]
[ LIMIT { number | ALL } ] [ OFFSET number ]
Expression for Top(5)
SELECT * FROM test ORDER BY test LIMIT 5
or
SELECT * FROM test OFFSET 0 LIMIT 5
Typically used with ORDER BY
SELECT * FROM test ORDER BY test OFFSET 5 LIMIT 5
The effect of LIMIT ALL is the same as omitting the LIMIT clause, for example, the following SQL retrieves all data.
SELECT * FROM test LIMIT ALL -- equivalent to SELECT * FROM test
In C#, you would use Take and Skip accordingly.
var list = (from c in context.UserRoles
select c).Skip(0).Take(5);
var list = context.UserRoles.Skip(0).Take(5);
Exceptions:
In T-SQL, Top(1), in PostgreSQL can be represented as
SELECT * FROM test LIMIT 1
While in C# it can be represented as:
var list00 = (from c in context.UserRoles
select c).First();
var list01 = (from c in context.UserRoles
select c).FirstOrDefault();
var list02 = (from c in context.UserRoles
select c).Take(1);
var list10 = context.UserRoles.First();
var list11 = context.UserRoles.FirstOrDefault();
var list12 = context.UserRoles.Take(1);
You can use First() or FirstOrDefault() to return the first piece of data in the query result, without adding conditions in the method body, which is equivalent to Top(1).
TakeWhile and SkipWhile
TakeWhile
Runs the query until a certain condition is met; it stops getting results when the condition becomes true.
SkipWhile
Runs the query until a certain condition is met; it starts skipping once the condition becomes true.
IN
Used with WHERE, it indicates a match for one of the specified conditions.
SELECT * FROM test WHERE test IN ('1','2')
SELECT * FROM test WHERE test NOT IN ('1','2')
In C#, this is represented by Contains.
string[] item = new string[] { "a", "b", "c" };
var list = from c in context.UserRoles
where item.Contains(c.RoleId)
select c;
var list2 = context.UserRoles.Where(x => item.Contains(x.RoleId));
Alias (as)
In SQL, you can set an alias for the queried column, for example:
SELECT test as ttttt FROM test
In C#, there are no direct operations like this.
However, many operations ultimately generate SQL that will include AS.
EXISTS and NOT EXISTS
| SQL | C# |
| ---------- | -------- |
| IN | Contains |
| EXISTS | Any |
| NOT EXISTS | All |
EXISTS
Checks if a result set is returned from a subquery, and if so, it returns TRUE.
The subquery should have WHERE or other conditions.
SELECT * FROM test WHERE EXISTS ( { subquery } )
EXISTS does not care about what is returned by the subquery or how many results are returned; it only checks if there are results (i.e., row count > 0).
For example:
SELECT * FROM test WHERE EXISTS (SELECT 1 FROM test WHERE test = 'a')
C#
Use .Any()
var list = from s in context.UserRoles
where (from c in context.UserRoles
select c).Any()
select s;
var list2 = context.UserRoles.Where(x => context.UserRoles.Any());
You can also add conditions to Any():
var list = from s in context.UserRoles
where (from c in context.UserRoles
select c).Any(x => x.RoleId != "x")
select s;
var list2 = context.UserRoles.Where(x => context.UserRoles.Any(x => x.RoleId != "x"));
NOT EXISTS
If no results are returned, it will return TRUE.
The subquery should also have conditions defined in the WHERE clause.
Linq's extension method All checks if every element meets the condition. It returns a Bool.
var list = from s in context.UserRoles
where context.UserRoles.All(x => x.RoleId == "a")
select s;
var list2 = context.UserRoles.Where(x => context.UserRoles.All(x => x.RoleId == "x"));
Using IN is generally faster than EXISTS.
文章评论