Shulou(Shulou.com)06/02 Report--

SQL basic query and LINQ integrated query is how, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

SELECT

The SELECT statement is used to select data from a table and is one of the most basic operations of SQL.

The results of a query through SELECT are stored in a result table (called a result set).

SQL SELECT syntax

Query only the data for a column:

SELECT [column name] FROM [table name]

Query data for all columns

SELECT * FROM [table name] SQL

Query all data

SELECT * FROM categories

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 items that are identical in the query result set, and can be considered "identical" only if every data in each column is the same.

Items with the same data can be added to the categories table for testing (except for the primary key).

SQLSELECT 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 operator

Used for conditional filtering.

Operator description = equal to not equal > greater than = greater than or equal to 1 AND category_id

< 5查询表达式 var list3 = from categories in context.Categories where categories.CategoryId >

1 & & categories.CategoryId

< 5 select categories;查询语句 var list3 = context.Categories.Where(x =>

X.CategoryId > 1 & & x.CategoryId

< 5);LIKE 和通配符 在 SQL 中，可使用以下通配符： 通配符描述%替代一个或多个字符_仅替代一个字符[charlist]字符列中的任何单一字符[^charlist]或者[!charlist]不在字符列中的任何单一字符SQLSELECT * FROM categories WHERE category_name like 'B%'查询表达式 var list4 = from categories in context.Categories where categories.CategoryName.StartsWith("B") select categories; 不能直接使用 %。 查询语句 var list4 = context.Categories.Where(x =>

X.CategoryName.StartsWith ("B"))

Linq has only Startwith, Endwith, and Contains, and no other wildcards.

EF.Functions.Like () is provided in EF 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, learn about EF.Functions.Like () for yourself.

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

There is no TOP in PostgreSQL, you can use OFFSET or LIMIT instead.

SELECT select_list FROM table_expression [ORDER BY...] Expression of [LIMIT {number | ALL}] [OFFSET number] Top (5) SELECT * FROM test ORDER BY test LIMIT 5

Or

SELECT * FROM test OFFSET 0 LIMIT 5

Commonly used with Order by

SELECT * FROM test ORDER BY test OFFSET 5 LIMIT 5

LIMIT ALL has the same effect as omitting the LIMIT clause, such as SQL below or getting all the data.

SELECT * FROM test LIMIT ALL-- equivalent SELECT * FROM test

In the C # code, Take and Skip are used accordingly.

Var list = (from c in context.UserRoles select c) .Skip (0) .Take (5); var list = context.UserRoles.Skip (0) .Task (5)

Exception:

Top (1) in T-SQL, in PostgreSQL, can represent

SELECT * FROM test LIMIT 1

And C# says:

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, with no condition in the method body, which is equivalent to Top (1).

TakeWhile and SkipWhileTakeWhile

Run the query and do not stop getting until a condition is established, that is, when the condition is established.

SkipWhile

Run the query and do not stop skipping until a condition is established; that is, it will not start until the condition is established.

In

Used with Where to indicate that one of the criteria is met.

SELECT * FROM test WHERE test IN ('1pm 2') SELECT * FROM test WHERE test NOT IN ('1PM 2')

It is represented by Contains in C #

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 aliases for the columns of the query, such as

SELECT test as ttttt FROM test

In C #, there should be no such operation directly.

However, AS appears in the final SQL generated by many forms of operation.

EXISTS and NOT EXISTSSQLC#INContainsEXISTSAnyNOT EXISTSAllEXISTS

Determine whether a result set is returned in the subquery, and if so, it is TRUE.

There should be conditions such as where in the subquery.

SELECT * FROM test EXISTS ({subquery})

EXISTS does not care about what is returned or how many result sets are returned in the subquery, it only determines whether the result is returned (the result set returned by the query, the number of rows > 0).

For example

SELECT * FROM test 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 ())

Conditions can also be added in 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 result set returns, or TRUE if no result set is returned.

There should be conditions such as where in the subquery.

Linq's extension method, All, indicates whether each element meets the criteria. Return to Bool.

Var list = from s in context.UserRoles where context.UserRoles.All (x = > x.RoleIdstones = "a") select s; var list2 = context.UserRoles.Where (x = > context.UserRoles.All (x = > x.RoleIdstones = "x"))

In is faster than EXISTS.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.