如何将sql查询转换为linq [英] how to convert sql query into linq

问题描述

我想在linq查询中转换下面的查询。我尝试但我得到例外：

i want to convert below query in linq query. i try but i get the exception:

string query = "select * from OrderItems where itemname is not null";
           if (!string.IsNullOrEmpty(item))
           {
               query += " and itemname=" + item;
           }
           if (!string.IsNullOrEmpty(description))
           {
               query += " and description=" + description;
           }
           if (!string.IsNullOrEmpty(price))
           {
               query += " and price=" + price;
           }

我尝试的代码如下：

my tried code is below:

var InventoryData = GetItems().ToList();
           if (!string.IsNullOrEmpty(item))
           {
               InventoryData += InventoryData.ToList().Where(d => d.InventoryId == item).ToList();
           }

请帮助：显示错误+符号。



请提前帮助谢谢。

please help: showing error + sign.

please help thanks in advance.

推荐答案

你的关闭，但你应该只使用=，而不是+ =这些条款会累积你想要的方式：

your close, but you should just use "=", not "+=" The clauses will accumulate the way you intend:

//string query = "select * from OrderItems where itemname is not null";
var query = GetItems(); // keep it queriable for now.  As soon as you perform "ToList()" the database is queried.  You should wait until you refine the criteria

if (!string.IsNullOrEmpty(item))
//    query += " and itemname=" + item;
      query = query.Where(d => d.itemname == item);
if (!string.IsNullOrEmpty(description))
//    query += " and description=" + description;
      query = query.Where(d => d.description == description);
if (!string.IsNullOrEmpty(price))
//    query += " and price=" + price;
      query = query.Where(d => d.price == price);
  
var InventoryData = query.ToList(); //The query to the db will include all the where clauses you add 

首先，您的SQL代码容易受到 SQL注入 [ ^ ]。 从不使用字符串连接来构建SQL查询。 始终使用参数化查询。



其次，要将过滤器应用于LINQ查询，您只需将调用链接到其中方法：

First of all, your SQL code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Secondly, to apply filters to a LINQ query, you just chain calls to the Where method:

var query = GetItems();
if (!string.IsNullOrEmpty(item))
{
    query = query.Where(d => d.ItemName == item);
}
if (!string.IsNullOrEmpty(description))
{
    query = query.Where(d => d.Description == description);
}
if (!string.IsNullOrEmpty(price))
{
    // TODO: Use the correct data type to store the price.
    query = query.Where(d => d.Price == price);
}

var inventoryData = query.ToList();

注意：您的 GetItems 方法应返回 IQueryable< T> 实例，以便过滤器将传递给SQL查询。在构建整个查询之前，不要调用 ToList ;否则，所有数据都将在过滤之前加载到内存中。

---

关于SQL注入你想知道的一切（但不敢问）特洛伊亨特 [ ^ ]

如何在没有技术术语的情况下解释SQL注入？ |信息安全堆栈交换 [ ^ ]

查询参数化备忘单| OWASP [ ^ ]

SQL注入攻击机制Pluralsight [ ^ ]

NB: Your GetItems method should return an IQueryable<T> instance, so that the filters are passed to the SQL query. Don't call ToList until you have built the entire query; otherwise, all of the data will be loaded into memory before the filtering takes place.

---

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
SQL injection attack mechanics | Pluralsight [^]

这篇关于如何将sql查询转换为linq的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！