29
投票
3答案
150557 次观看

LINQ符合AND OR条件的地方

所以我设法使该查询正常工作 List<string> listStatus = new List<string>() ; listStatus.add("Text1"); List<string> listMerchants = new List<string>() ; listMerchants.add("Text2"); from item in db.vw_Dropship_OrderItems where listStatus.Contains(item.StatusCode) ...

0
投票
4答案
186 次观看

在SQL中搜索几个表

我正在尝试一次搜索多个表中的搜索词。我的查询是: SELECT item.ItemID FROM Inventory.Item item JOIN Inventory.Category catR // each item can be in several categories ON catR.ItemID = item.ItemID JOIN Category.Category cat ON cat.CategoryID = catR.CategoryID JOIN I...

1
投票
3答案
359 次观看

通过添加未使用的WHERE条件,查询运行时间更长

我遇到了一个有趣的障碍(至少对我来说很有趣)。以下是查询外观的一般思路。假设@AuthorType是存储过程的输入,并且我在每个地方放置注释都有各种特殊条件。 SELECT * FROM TBooks WHERE (--...SOME CONDITIONS) OR (@AuthorType = 1 AND --...DIFFERENT CONDITIONS) OR (@AuthorType = 2 AND --...STILL MORE CONDITIONS) 对我来说有趣的是,如果我使用@AuthorType = 0执行此SP,则它的运行速度比如果删除最后两组条件(为@Aut...

4
投票
3答案
13180 次观看

Linq include with where子句

嘿,所以我遇到了这样的情况,我要从数据库中拉回客户,并通过包含的方式将所有案例研究包括在内 return (from c in db.Clients.Include("CaseStudies") where c.Id == clientId select c).First(); 但是我现在想做的是在包含的案例研究中添加一个where子句,这样它只返回案例研究,其中delete = false 有点像 return (from c in db.Clients.Include("CaseStudies") where c.Id...

0
投票
3答案
402 次观看

AsQueriable()或Expression <t> .Compile()?</t>

Edit2: 在最终能够将两者相互描述之后,看来在我的情况下.AsQueryable()比Expression.Compile()快一点。 原始问题: 我已经实现了一些数据库表(如List&lt;T&gt;)的缓存,我需要使用与查询Expression&lt;Func&lt;T, bool&gt;&gt;本身时所用的相同的Table&lt;T&gt;进行查询。 最快/最好的方法是什么? List&lt;T&gt;.AsQueryable().FirstOrDefault(Expression&lt;Func&lt;T, bool&gt;&gt;) 或 List&lt;...

2
投票
6答案
1750 次观看

如何强制SQL Server 2005在哪里运行联接?

我有一个SQL查询,该查询将定价表连接到包含用户提供的答案的表中。我的查询用于根据输入的数量获取价格。以下是我的SQL语句: SELECT JobQuestion.Value, Price.Min, Price.Max, Price.Amount FROM Price INNER JOIN JobQuestion ON Price.QuestionFK=JobQuestion.QuestionFK AND JobQuestion.JobFK=1 WHERE Price.Min &lt;= JobQuestion.Value AND ...

0
投票
1答案
3511 次观看

Zend DB Table Where子句

我正在尝试使用数组设置Zend DB表的where参数。我正在尝试遵循文档中的示例: $select = $table-&gt;select()-&gt;where(array('bug_status = ?' =&gt; 'NEW')); 我有一个继承Zend_Db_Table的类,并试图像示例一样进行选择: $select = $this-&gt;select()-&gt;where(array('FirstName = ?' =&gt; 'Ryan')); 但是我遇到一个错误,提示“ SQLSTATE [42S22]:找不到列:1054'where子句'中的未知...

0
投票
1答案
844 次观看

How to create a dynamic query using EOD SQL?

This should be fairly simple, though I can't seem to find a single example. I want to create a query looking like this: SELECT column_name FROM table_name WHERE column_name IN (value1,value2,...) As an option I could append OR-clauses to the end of the query. The code I have written so far ke...

1
投票
2答案
56 次观看

MYSQL IN or multiple conditionals

I have a simple query which is returning records based on the field status not having certain values. Lets say for arguments sake that the field can have values 1,2,3...10 and I want to return all records that don't have values 3, 7 and 9. Which of the following would be best to use? Option 1. ...

16
投票
5答案
18073 次观看

T-SQL Where Clause Case Statement Optimization (optional parameters to StoredProc)

I've been battling this one for a while now. I have a stored proc that takes in 3 parameters that are used to filter. If a specific value is passed in, I want to filter on that. If -1 is passed in, give me all. I've tried it the following two ways: First way: SELECT field1, field2...etc FROM...

8
投票
3答案
4908 次观看

LINQ results when there are no matches?

What exactly does a LINQ function return when there are no matches? Take the Where method, for example: var numbers = Enumerable.Range(1, 10); var results = numbers.Where(n =&gt; n == 50); What would be in results at this point?

1
投票
3答案
1002 次观看

C#, Linq to Sql: Why can I not use an expression to filter SubEntities?

I have made myself an ExpressionBuilder class that helps me put together expressions that can be used as a predicate when doing Linq to Sql queries. It has worked great. However, I just discovered Expressions can only be used to filter on Tables, and not on EntitySets??Why on earth is this the ca...

1
投票
2答案
2629 次观看

My simple sql update query is not working right :(

I've got a simple sql query that is trying to update a single row. The code came from some Linq-to-sql code (i used Profiler to grab it), but please don't worry about the source (L2S) ... that's irrelivant to the question. Now, when i try and do an update with the where clause, I get 0 rows upda...

0
投票
3答案
187 次观看

What does it mean to say that "the where clause of select statements can perform&gt;,&gt;=,

According to the Wikipedia article on Google App Engine: The where clause of select statements can perform&gt;,&gt;=, What does this mean?

1
投票
3答案
10122 次观看

SQL Server 2008 - boolean variable change query

I have the following query: DECLARE @IsStocked bit SELECT * FROM Products p WHERE p.LastSeen &gt; GETDATE() - 30 This returns all Products that have been seen within the last 30 days. My question is, I would like the p.LastSeen &gt; GETDATE() - 30 clause to only apply when @IsStocked = tru...

29
投票
9答案
20873 次观看

SQL Filter criteria in join criteria or where clause which is more efficient

I have a relatively simple query joining two tables. The "Where" criteria can be expressed either in the join criteria or as a where clause. I'm wondering which is more efficient. Query is to find max sales for a salesman from the beginning of time until they were promoted. Case 1 select sal...

1
投票
3答案
1320 次观看

Count() using Where clause and Left Join

I have a query similar to the following: SELECT users.id FROM users LEFT JOIN sales ON installations.customer = users.id What I would like to say is something like "WHERE count(sales.id)&gt; 4" - meaning that if the user has more than 4 sales assoc with them. I am not sure if I am going ...

0
投票
1答案
290 次观看

Linq and multiple Where's

I'm trying to see if there is a way to build a Linq statement that would choose based off available attributes in an element what the result would be, although not all attributes are always available. For example, this would be a 'standard' element: &lt;box left="2" right="2" /&gt; However, t...

0
投票
1答案
933 次观看

Incorrect result for date comparison (to a timestamp column) in mysql

I've been having some 'strange' results while comparing dates. table1 has two rows with TIMESTAMPS values 2009-08-26 23:39:56 and 2009-08-27 00:01:42 When I make this query: select * from table1 c INNER JOIN table2 r ON r.table1_id = c.id WHERE DATE(c.authorization_date) = '2009-08-26' it retu...

1
投票
6答案
815 次观看

Specify more than one item in the where clause

Is there any way I could run the following 'logical code' to actually work? $sql=mysql_query("DELETE FROM users WHERE id='3,4,5,9'"); I basically want to give my user a tick box to tick for all displayed rows, they can then pick which ones to remove. I just want to remove more than one row wit...

1
投票
3答案
3627 次观看

Complex Expressions in a LINQ Where Clause

I was wondering if it is possible to include inner variables or delegates in linq statements? I currently am using Linq to XML with XPath extensions and am using a where clause on an element that I cannot guarantee will exist. Here is a sample of what I mean: var result = from record in xm...

0
投票
1答案
838 次观看

Using a var based on an enum in a Where clause in Entity Framework throws an exception

I have the following code which throws an exception (detail in code comments below). I am merely trying to use an instance of an enum as part of the Where clause. I understand the message, but I don't understand why EF cannot parse an Int32 enum. It works if I copy the enum to an Int32 and then ...

2
投票
2答案
2699 次观看

linq where clause not in select statement

Can someone help me to convert from SQL Query to LINQ VB.NET: select rls.* from Roles rls(nolock) where rls.id not in ( select r.ID from usersRole ur (nolock) inner join Roles r(nolock) on ur.RoleID = r.ID where user_id = 'NY1772') Thanks

16
投票
4答案
5367 次观看

Scala equivalent to Haskell's where-clauses?

Is it possible to use something similar to where-clauses in Scala? Maybe there is some trick I didn't think of? Edit: Thanks for all your answers, they are very much appreciated. To sum up: Local vars, vals and defs can be used to achieve almost the same thing. For lazy evaluation, one can use ...

2
投票
1答案
794 次观看

Multiple Defered WHERE clause expressions in LINQ to SQL

Maybe a simple question, I'm trying to get a result from a table where the Name column contains all of an array of search terms. I'm creating a query and looping through my search strings, each time assigning the query = query.Where(...);. It appears that only the last term is being used, I suppo...

1
投票
1答案
739 次观看

Case statement in where clause w/an OR

Apologies in advance since I feel like I'm probably forgetting/missing something obvious on this one. Here goes; I'm using a case statement in my WHERE clause, the below works fine: WHERE r.[SomeCol] = @SomeColVal AND SomeOtherCol = ( CASE WHEN (@Year = 0 AND @Period = 0) THEN @SomeVal W...

2
投票
4答案
222 次观看

Which SQL do you write?

When joining two tables, what are the difference between the two blocks below and which is the better approach? Pattern A: SELECT ... FROM A INNER JOIN B ON A.PK = B.FK WHERE 1=1 AND A.Name = "Foo" AND B.Title = "Bar" Pattern B: SELECT ... FROM A INNER JOIN B ...

87
投票
7答案
141132 次观看

SQL server ignore case in a where expression

How do I construct a SQL query (MS SQL Server) where the "where" clause is case-insensitive? SELECT * FROM myTable WHERE myField = 'sOmeVal' I want the results to come back ignoring the case

132
投票
2答案
200953 次观看

MySQL join with where clause

I have two tables I want to join. I want all of the categories in the categories table and also all of the categories subscribed to by a user in the category_subscriptions table. essentially this is my query so far: SELECT * FROM categories LEFT JOIN user_category_subscriptions ON user_c...

8
投票
5答案
7527 次观看

Evaluation of multiples 'IN' Expressions in 'WHERE' clauses in mysql

Updating by @Cesar's request. Hope I understood what you want, if not, please revert. Quassnoi. If I make an SQL query like this: SELECT * FROM TABLE_NAME WHERE b IN (2, 7) AND c IN (3, 9), can I assume that MySQL will match only pairs from elements with same number in each list? That is, (2, 3...